Excel in Excel handling in Selenium

Excel is a widely used tool in test automation for reading test data and writing outputs during execution. Apache POI is one of the most used java libraries, that offers multiple classes and methods to read/write data from old and new Excel files formats.

Wanted to share a detailed step by step approach to read/write data from/to excel using Apache POI library. Before we start, lets understand the various terms used in Excel

Workbook: A workbook is an Excel file. It can be used for creating and maintaining the spreadsheet. A workbook may contain many sheets.

Sheet: A sheet represents a page in an Excel file that contains the number of rows and columns.

Row: A row is a collection of cells, which is used to refer to a row in the spreadsheet.

Cell: A cell is a combination of a row and a column. Data entered by a user is stored in a cell. Data can be of various type such as string, numeric, date, Boolean or even formula.

Lets see the step by step process for handling excel in selenium framework

Step 1: Need to install Apache POI dependency

. Apache POI is an open source java library that helps to read/write data from/to excel in selenium. Add Apache POI from maven repositories to pom.xml

 	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.3</version>
		</dependency>

Step 2: Create an object for File class

File class is a static class that is used to open, create, copy file from the path mentioned. Path can be absolute or relative

	File file= new File(path);

Step 3: Create an object for FileInputStream/FileOutputStream classes

FileInputStream class is used to read input bytes from a file.

FileOutputStream class is to write output bytes to a file

	FileInputStream fin = new FileInputStream(file);
	FileOutputStream fout = new FileOutputStream(file);

Step 4: Use Apache POI interfaces and classes to create excel workbook

The interface used to work with Excel workbooks is "Workbook". The 2 widely used classes that implement this interface are:

• HSSFWorkbook - This class is used to read/write to .xls Excel format. It is compatible with 97-2003 MS office versions

• XSSFWorkbook - This class is used to read/write to .xlsx or .xls Excel format. It is compatible with 2007 or later MS office versions

XSSFWorkbook wb = new XSSFWorkbook(FileInputStream fin);
HSSFWorkbook xlwb = new HSSFWorkbook(FileInputStream fin);

Step 5: Use "Sheet" interface to interact with excel sheets

The 2 interfaces to create or read from sheet in excel workbook are

• HSSFSheet - This class is used to create a new sheet in the HSSFWorkbook (Workbooks in .xls file format)

• XSSFSheet - This class is used to create a new sheet in the XSSFWorkbook (Workbooks in .xlsx file format)

XSSFSheet ws = wb.getSheet(sheet);
HSSFSheet xlws = wb.getSheet(sheet);

Step 6: "Row" interface helps us to interact with rows in a specific sheet. Below two classes implement this interface

• HSSFRow - This represents a row in the HSSFSheet (Sheets created in old excel file format .xls).

• XSSFRow - This represents a row in the XSSFSheet (Sheets created in new excel file format .xlsx).

HSSFRow row1=sheet.getRow(1);
XSSFRow row2=sheet.getRow(2);

Step 7: Use the method in Sheet interface to get the specific row

The below mentioned methods are mostly used while working with excel

• getRow (int rowIndex) - This method is used to get to a specific row number in the sheet

• getLastRowNum() - This method is used to get the last row in the sheet

• getFirstRowNum() - This method is used to get the first row in the sheet

Row xlRow=ws.getRow(int rowIndex);
int lastRow = ws.getLastRowNum();
int firstRow = ws.getFirstRowNum();

Step 8: Use the iterator() method to read all the columns in a specific row

The iterator() method can be used to get an Iterator for any collection

Iterator<Cell> i = xlRow.iterator();

Step 9: Use the get/set Cell value method to read/write data from a cell. The below methods are used to get various types of data from the cell

• getStringCellValue() - To read text data type from a cell

• getNumericCellValue() - To read numeric data type from a cell

• getBooleanCellValue() - To read text data type from a cell

• getDateCellValue() - To read date data type from a cell

• setCellValue() - To write data to a cell

cell.getStringCellValue();
cell.setCellValue("Numpy Ninja");

Sample method to read data from excel

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;

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

public static List<String> readFromExcel(String path,String sheet, int row) throws IOException{
		File file= new File(path);
		FileInputStream fio = new FileInputStream(file);
		List<String> inputs = new ArrayList<String>();
		XSSFWorkbook wb = new XSSFWorkbook(fio);
		XSSFSheet ws = wb.getSheet(sheet);
		Row xlRow=ws.getRow(row); 
		Iterator<Cell> i = xlRow.iterator();
		while(i.hasNext()) {
			Cell cell = i.next();
			inputs.add(cell.getStringCellValue());
		}
		wb.close();
		return inputs;	
	}

Sample method to write data from excel

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

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

public static writeToExcel(String path,String sheet, int row) throws IOException{
		File file= new File(path);
		FileInputStream fio = new FileInputStream(file);
		XSSFWorkbook wb = new XSSFWorkbook(fio);
		XSSFSheet ws = wb.getSheet(sheet);
		Row xlRow=ws.createRow(ws.getLastRow()+1); 
		xlRow.createCell(0).setCellValue("TestCase_01");
         xlRow.createCell(1).setCellValue("Pass");
		FileOutputStream fout = new 			FileOutputStream("path");
        wb.write(fout);
		wb.close();
	}