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();
}