Write Data To The Excel Sheet In Selenium Using POI

Earlier, we have discussed different techniques to accommodate parameterization in our Selenium automation test. Today we are going to discuss commands (code) to write data to the excel sheet using the POI jar file. Before we proceed further to achieve today’s agenda, I would recommend you following hot tutorials on parameterization:

Advantages of writing data to the excel sheet

Ease in handover test data to the client or stakeholders (Portable)
Have a checklist of pass and failure status against each scenario mentioned in the excel sheet
Ease of maintenance

Write data excel sheet selenium

Differences between POI and JXL

POI	JXL
Format Supported: It supports xlsx and xls both.	Format Supported: It supports only xls format.
Version: It supports all the versions of Excel.	Version: It does not support Excel 2007 and later.
It supports conditional formatting.	It does not support conditional formatting.
Apache POI releases its update regularly.	It was last updated in 2009.
It supports rich text.	It does not support rich text.
Documentation is elaborated.	Documentation is limited.

The hierarchical flow of the implementation of POI to write data to the Excel sheet

You got all the advantages of maintaining an excel sheet for the parameterization. Now let me give you a gist of the steps of the instantiation and calling of the methods and interfaces.

FileInputStream
Workbook -> XSSFWorkbook or HSSFWorkbook
Sheet
Row
Cell
FileOutputStream

How to implement POI jar file to write data to the excel sheet?

Now we are going to implement all those Interfaces and classes to write data to the sheet. Below sample code create a cell and write cell heading at 0^th row and 2^nd column.

public static void writeDataToExcel() throws IOException {
			
		String path = "data/TestDataSheet.xlsx";
		
		FileInputStream fis = new FileInputStream(path);
		
		Workbook workbook = new XSSFWorkbook(fis);
		
		Sheet sheet = workbook.getSheetAt(0);
		
		Row row = sheet.getRow(0);
		
		Cell cell = row.createCell(2);
		
		cell.setCellValue("Written Status");
		
		FileOutputStream fos = new FileOutputStream(path);
		
		workbook.write(fos);
		
		fos.close();

	}

In the above code, we are taking file by defining its path through FileInputStream and further, we created the instance of Workbook and XSSFWorkbook because our sheet is having .xlsx extension (HSSFWorkbook for .xls extension). We use createCell() method first to assign the cell and then we write data by using setCellValue() method. Here is the screenshot from excel sheet after writing data.

write data excel sheet

Above code is used to write data to the single column. Let’s look at another piece of code which writes data for the entire row to the assigned cell.

package com.inviul.selenium.project;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteDataFullRow {

	public static void main(String[] args) throws IOException {
		
		String path = "data/TestDataSheet.xlsx";
		
		FileInputStream fis = new FileInputStream(path);
		
		Workbook workbook = new XSSFWorkbook(fis);
		
		Sheet sheet = workbook.getSheetAt(0);
		
		int lastRow = sheet.getLastRowNum();
		
		for(int i=1; i<=lastRow; i++){
			
			Row row = sheet.getRow(i);
			
			Cell cell = row.createCell(2);
			
			cell.setCellValue("Writing Done");
			
		}
		
		FileOutputStream fos = new FileOutputStream(path);
		
		workbook.write(fos);
		
		fos.close();


	}

}

Write data to excel sheet