File Handling | Writing data into an Excel(.XLSX or .XLS) File.

Writing data into an Excel(.XLSX or .XLS) File using JAVA.

Overview of the Program

  1. Locate path and filename where the new excel file will be created using File Class.
  2. Load the newly created file into FileOutputStream Class, so that new content can be written in it.
  3. Create workbook using XSSFWorkbook class.
  4. Create sheet in the workbook using XSSFSheet class.
  5. Now, create new Row in the sheet using createRow() method of the sheet object.
  6. Now, create new Cell in the Row using createCell() method of the Row object.
  7. Now, input text/value in the newly created cell using setCellValue() Method of the cell object.
  8. Once you have created a new Row, you can add multiple cells in the same row using Steps # 5-7.
  9. Once you have entered all the values in the FileOutputStream object. Now, you have to write all the data in original excel file. This can be done using write() of the XSSFWorkbook Class.
  10. At the end you have to close the workbook using close() of the XSSFWorkbook Class object.

Output

Create Excel Example-1
Create Excel Example-1
Create Excel Example-2
Create Excel Example-2

Additional Notes

This program would support only the excel files having extension “.xlsx”. If you want to run the same code/program for the files having extension “.xls” replace all XSSF classes to HSSF as mentioned below:

testResult.xlsx testResult.xls

XSSFWorkbook

HSSFWorkbook

XSSFSheet

HSSFSheet

XSSFRow

HSSFRow

Java Code (Example 1)

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class createAndWriteExcel 
{
	public static void main(String[] args) throws IOException 
	{
		String filePath="output_Excel//testResult.xlsx";
		String sheetName="testData";
		createExcel(filePath,sheetName);
	}
	
	public static void createExcel(String filePath,String sheetName) throws IOException
	{
		// Step #1 : Specify file name and location
		File file=new File(filePath);
		
		// Step #2 : load the excel file in FileOutputStream object
		FileOutputStream fos=new FileOutputStream(file); 
		
		// Step #3 : Create new Workbook
		XSSFWorkbook workbook =new XSSFWorkbook(); 
		
		// Step #4 : Create new sheet
		XSSFSheet sheet=workbook.createSheet(sheetName);
			
			// Adding content to the Excel sheet
			addingContent(sheet);
			
			// Write and update Excel File
			writeAndUpdateExcel(workbook, fos);
	}

	public static void addingContent(XSSFSheet sheet)
	{
		// Step #5 : Create new Row in the sheet
		XSSFRow headingRow=sheet.createRow(0);
		// Step #6 and Step #7 : Create new Cell and add content to each cell
		headingRow.createCell(0).setCellValue("S.No");
		headingRow.createCell(1).setCellValue("Website Name");
		headingRow.createCell(2).setCellValue("URL");
		
		// Step #8 : Creating/Adding multiple rows and cells
		XSSFRow row_1_content=sheet.createRow(1);
		row_1_content.createCell(0).setCellValue("1");
		row_1_content.createCell(1).setCellValue("Google");
		row_1_content.createCell(2).setCellValue("www.google.com");
		
		XSSFRow row_2_content=sheet.createRow(2);
		row_2_content.createCell(0).setCellValue("2");
		row_2_content.createCell(1).setCellValue("Facebook");
		row_2_content.createCell(2).setCellValue("www.facebook.com");
		
		XSSFRow row_3_content=sheet.createRow(3);
		row_3_content.createCell(0).setCellValue("3");
		row_3_content.createCell(1).setCellValue("Twitter");
		row_3_content.createCell(2).setCellValue("https://twitter.com/");
		
		XSSFRow row_4_content=sheet.createRow(4);
		row_4_content.createCell(0).setCellValue("4");
		row_4_content.createCell(1).setCellValue("Guru99");
		row_4_content.createCell(2).setCellValue("https://www.guru99.com/");
	}
	
	public static void writeAndUpdateExcel(XSSFWorkbook workbook, FileOutputStream fos) throws IOException
	{
		// Step #9 : Writing all the data from workbook object to FileOutputStream object
		workbook.write(fos);
		
		// Step #10 : Closing the workbook object.
		workbook.close();
		System.out.println("File created and saved at specified location....");
	}
}

Java Code (Example 2)

The alternate way to write the content in the excel file using loop and function to reduce code and complexity of the Program.

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class createAndWriteExcel_Example2 
{
	public static void main(String[] args) throws IOException 
	{
		String filePath="output_Excel//testResult2.xlsx";
		String sheetName="testData";
		createExcel(filePath,sheetName);
	}
	
	public static void createExcel(String filePath,String sheetName) throws IOException
	{
		// Step #1 : Specify file name and location
		File file=new File(filePath);
		
		// Step #2 : load the excel file in FileOutputStream object
		FileOutputStream fos=new FileOutputStream(file); 
		
		// Step #3 : Create new Workbook
		XSSFWorkbook workbook =new XSSFWorkbook(); 
		
		// Step #4 : Create new sheet
		XSSFSheet sheet=workbook.createSheet(sheetName);
			
			// Adding content to the Excel sheet
			String[] heading={"S.No","User Name","Password","Comments"};
			String[] row1={"1","uid_ABC","pwd_abc"};
			String[] row2={"2","uid_XYZ","pwd_xyz","Google User ID and Password"};
			String[] row3={"3","uid_123","pwd_123"};
			String[] row4={"4","uid_JKL","pwd_jkl","Facebook User ID and Password"};
			String[] row5={"5","uid_PQR","pwd_pqr"};
			addingContent(sheet,0,heading);
			addingContent(sheet,1,row1);
			addingContent(sheet,2,row2);
			addingContent(sheet,3,row3);
			addingContent(sheet,4,row4);
			addingContent(sheet,5,row5);
			
			// Write and update Excel File
			writeAndUpdateExcel(workbook, fos);
	}

	public static void addingContent(XSSFSheet sheet,int rowIndex,String[] cellValues)
	{
		// Step #5 : Create new row in the sheet
		XSSFRow currentRow=sheet.createRow(rowIndex);
		int cellIndex=0;
		
		// Step #6-8 : Adding new cells and values in each row
		for (String currentCellValue : cellValues) 
		{
			currentRow.createCell(cellIndex).setCellValue(currentCellValue);
			cellIndex++;
		}
	}
	
	public static void writeAndUpdateExcel(XSSFWorkbook workbook, FileOutputStream fos) throws IOException
	{
		// Step #9 : Writing all the data from workbook object to FileOutputStream object
		workbook.write(fos);
		
		// Step #10 : Closing the workbook object.
		workbook.close();
		System.out.println("File created and saved at specified location....");
	}
}

1094total visits,1visits today

3 thoughts on “File Handling | Writing data into an Excel(.XLSX or .XLS) File.”

  1. Hi,

    I am totally new to Selenium IDE.

    Can you provide me detailed steps on how to write to excel file from Selenium IDE like where to write Java code,
    creating files etc.?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *