Skip to toolbar

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....");
	}
}

2918total visits,3visits today

5 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 *

Site Statistics

  • Users online: 0 
  • Visitors today : 14
  • Page views today : 14
  • Total visitors : 48,210
  • Total page view: 66,356

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.