How to Read & Write Data from Excel File in Selenium Webdriver

File IO is an important part of any software process. We always create a file, open it, update files, or sometimes delete the files on our computers. The same applies in Selenium Automation, especially when we need to Read & Write Data from Excel files. To accomplish this, we require a process to manipulate files with Selenium. For us, Java provides different classes for File Manipulation with Selenium. In this article, we will learn how to read and write on an Excel file with the help of the Java IO package and Apache POI library.

Exporting Excel

How to handle excel file using POI

Apache provides a famous library POI to read or write an Excel. This library is used to read and write both XLS and XLSX file format of Excel. An HSSF implementation is provided by the POI library to read XLS files.

What is POI?

POI is a JAR file that helps us to create a coding environment in the IDE to read the data from the excel sheet and writing to the excel sheet.

How to Set up POI JAR File in my project?

To set up POI Jar File in the project you just need to create the dependency in the pom.xml file. Below is the dependency definition:

Maven:

You can simply download the latest version POI jars from http://poi.apache.org/download.html & download the latest zip file under Binary Distribution

Unzip the downloaded file and add all below jars to the classpath of your project.

ooxml-lib folder

lib folder

Interfaces inside the POI

1. Workbook– This interface is implemented to instantiate different excel file representation (xls or xlsx).
2. Sheet– This interface is implemented to read the sheet inside the workbook of the different types of excel sheet (xls or xlsx).
3. Row– This interface is used to identify the row inside the sheet of the different types of excel sheet (xls or xlsx).
4. Cell– This interface is implemented to identify the corresponding cell of the given row of the different types of excel sheet (xls or xlsx).

Classes inside the POI

1. XSSFWorkbook- This class representation will implement Workbook interfaces for the XLSX file.
2. HSSFWorkbook- This class representation will implement the Workbook interface for the XLS file.
3. XSSFSheet- This class representing a Sheet interface for the XLSX file.
4. HSSFSheet- This class representing a Sheet interface for the XLS file.
5. XSSFRow- This class representing a Row interface for the XLSX file.
6. HSSFRow- This class representing a Row interface for the XLS file.
7. XSSFCell- This class representing a Cell interface for the XLSX file.
8. Sheet: XSSFSheet and HSSFSheet classes implement this interface.
9. Row: XSSFRow and HSSFRow classes implement this interface.
10. Cell: XSSFCell and HSSFCell classes implement this interface.

Implementation of the POI Jar File to read test data from the Excel Sheet?

Consider below Excel file

Read data from Excel file

Step 1: Create a Package excelExport

Step 2: Create a Class ReadExcelSheetData

package excelExport;

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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelSheetData {
public static void main(String[] args) throws IOException {
String path = "C:\\Users\\Desktop\\ExportExcel.xlsx";
FileInputStream fis = new FileInputStream(path);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
System.out.println("Last row- "+lastRow);
for(int i=0; i<=lastRow; i++){
Row row = sheet.getRow(i);
int lastCell = row.getLastCellNum();
for(int j=0; j<lastCell; j++){
Cell cell = row.getCell(j);
String value = cell.getStringCellValue();
System.out.println(value);
}
System.out.println();
}
}
}

Here is the console output:

Write data on Excel file

Advantages of writing data to the Excel Sheet

• It is the ease in handover test data to the client
• Have a checklist of pass and failure status against each scenario mentioned in the excel sheet
• Ease of maintenance

The Hierarchical flow of POI to write data to the Excel Sheet.

Below are the steps to 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

Below code will 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 = "C:\\Users\\Desktop\\ExportExcel.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("Age");
FileOutputStream fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();
}

In the above code, we are taking a file by defining its path through FileInputStream and we created the instance of Workbook and XSSFWorkbook. We use createCell() method first to assign the cell and then we write data by using setCellValue() method.  

Write data to Excel file

Step 1: Create a Package excelExportStep 2: Create a Class WriteExcelData

package excelExport;

import java.io.FileInputStream;
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 WriteExcelData {
public static void main(String[] args) throws IOException {
String path = "C:\\Users\\SidduRohan\\Desktop\\ExportExcel.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("32");
}
FileOutputStream fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();
}
}

After running the above program, the output looks like

Conclusion:

• To read excel file by Java IO operation we need to use Apache POI Jar.
• XLSX and XLS files are two kinds of a workbook in Excel file
• POI has different Interfaces like Workbook, Sheet, Row, Cell.

These interfaces are implemented by corresponding XLS (HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell) and XLSX (XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell) file manipulation classes.