Data driven testing in Selenium WebDriver – Use of Apache POI to read from or write to excel files

Data driven testing refers to storing the test data in some external file rather than hard coding it in the test scripts. Storing data in an external file makes it easy to manage scripts easily and update data whenever needed without affecting the scripts. If you are familiar with using QTP/UFT, it can read data from internal data sheets or external excel files. Similarly most of the automation tools have mechanisms for reading data from external files and use it in test cases. One major difference between QTP/UFT and Selenium that we need to understand is QTP/UFT is a ready to use automation tool. But Selenium is more like a framework and we need to depend on other third part tools in order to accomplish our testing needs. For reading data from excel files, we need to use Apache POI.

What is Apache POI?

Apache POI is basically an open source Java library developed by Apache which provides an API for reading and writing files in Microsoft office formats including MS Word, Powerpoint or Excel using Java programs. It has classes and methods to decode the user input data or a file into MS document files. So for data driven testing using Selenium WebDriver, we use it for reading data stored in excel sheets. It can also be used if you want to write any data to an excel file in your scripts. The POI library lets you work with both xls and xlsx documents. The POI library provides different implementations for reading and writing these file formats- The HSSF implementation is used for xls files and the XSSF implementation for xlsx files. WE will be using the classes and methods in the POI library for reading/writing our excel files.
How to download the Apache POI ?
Go to the Apache POI download page http://poi.apache.org/download.html and select ‘The latest stable release’ link to download the latest version.

Next page, you get all the available downloads for Apache POI. You select the zip file under Binary Distribution.

Click the below link to start download.

Once the download is done, extract the files to a folder so that you can import the POI jars to your project.
The folder structure you get will be like:

Apart from the Jar files you see in the above folder, you have some more Jar files within the lib folder and ooxml-lib folder. You have to import all these Jar files to the class path of your project in Eclipse.
Lib folder:

Ooxml folder:

How to add the Jar files to Project library?

As I said you need to add all the above Jar files to your project file library in order to do data driven testing using Selenium. Let’s now see how to add the files.
Right click on your project name.
Select Build Path-->Configure Build Path.

Click on ‘Add External Jars’ as shown in the below screen.

Select all the Jar files in the main folder, and also the jar files under lib and ooxml-lib folders.

That’s all. You are done adding your poi files to your project. Now you can get started with writing your data driven tests.

How to Read/Write data using Apache POI?

In this section, we will be seeing how to write the code in selenium for reading data from an excel sheet and how you can write data to excel sheet. Before we start writing the code, let’s take a quick glance of few interfaces and classes under the Apache POI API that are critical to work with excel sheets. We have two implementations in POI API. The XSSF implementation is for MS office 2007 or later version .xlsx files) and HSSF implementation for MS office 97-2003 (.xls files)
Workbook: This can be considered as the super interface of all classes that create and maintain excel workbooks. The XSSFWorkbook (for xlsx) and HSSFWorkbook (for xls) classes implements this interface.
XSSFWorkbook and HSSFWorkbook classes : These classes implements the workbook interface and has methods to read or write Microsoft excel files.
Sheet: Sheet is the super interface of all classes that create and maintain low level spreadsheets or worksheets, which is represented as a grid of cells.
XSSFSheet and HSSFSheet: These classes implements the Sheet interface and can create excel spreadsheets and can format the sheet style and format.
Row: Interface used for representation of a row in a spreadsheet.
XSSFRow and HSSFRow: These classes implements the Row interface and can create rows in spreadsheet.
Cell: An interface representing all classes that represent cells in the row of a spreadsheet.
XSSFCell and HSSFCell: Implements the Cell interface and is a high level representation of cells in a spreadsheet.
The figure below gives you a summary of the interfaces and classes that we discussed.

Read data from Excel file

Now, let’s see the complete code to read an excel file using the POI API.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ExcelFileReader {

    public void readExcelFile(String folderPath, String fileName, String sheetName) throws IOException {
        // Create a File object to open the XLSX file
        File file = new File(folderPath + "\\" + fileName);

        // Create a FileInputStream object to read the Excel file
        FileInputStream fileInputStream = new FileInputStream(file);

        Workbook workbook = null;

        // Find the file extension by extracting the substring after the last dot in the file name
        String fileExtension = fileName.substring(fileName.lastIndexOf("."));

        // Check if the file is in the XLSX format
        if (fileExtension.equals(".xlsx")) {
            // If it is an XLSX file, create an object of XSSFWorkbook class
            workbook = new XSSFWorkbook(fileInputStream);
        }
        // Check if the file is in the XLS format
        else if (fileExtension.equals(".xls")) {
            // If it is an XLS file, create an object of HSSFWorkbook class
            workbook = new HSSFWorkbook(fileInputStream);
        }

        // Read the specified sheet inside the workbook by its name
        Sheet sheet = workbook.getSheet(sheetName);

        // Find the number of rows in the Excel file
        int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

        // Create a loop over all the rows of the Excel file to read its data
        for (int i = 0; i < rowCount + 1; i++) {
            Row row = sheet.getRow(i);

            // Create a loop to print cell values in a row
            for (int j = 0; j < row.getLastCellNum(); j++) {
                // Print Excel data in the console
                System.out.print(row.getCell(j).getStringCellValue() + " || ");
            }
            System.out.println();
        }

        // Close the input stream
        fileInputStream.close();
    }

    // Main function is calling readExcelFile function to read data from the Excel file
    public static void main(String... args) throws IOException {
        // Create an object of ExcelFileReader class
        ExcelFileReader excelFileReader = new ExcelFileReader();

        // Prepare the path of the Excel file
        String folderPath = System.getProperty("user.dir");

        // Call the readExcelFile method of the class to read data
        excelFileReader.readExcelFile(folderPath, "ExportedExcel.xlsx", "ExcelDemo");
    }
}

Write Data to Excel file

There may be instances where you want to write or store some results to any excel sheet. The Apache POI API can be used to write data to excel files as well.

Next let's see the complete code to write data to an excel sheet using Apache POI.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ExcelWriter {

    public void writeToExcelFile(String folderPath, String fileName, String sheetName, String[] data) throws IOException {
        // Create a File object to open the xlsx file
        File file = new File(folderPath + "\\" + fileName);

        // Create a FileInputStream object to read the excel file
        FileInputStream fileInputStream = new FileInputStream(file);

        Workbook workbook = null;

        // Find the file extension by extracting the substring after the last dot in the file name
        String fileExtension = fileName.substring(fileName.lastIndexOf("."));

        // Check if the file is in the xlsx format
        if (fileExtension.equals(".xlsx")) {
            // If it is an xlsx file, create an object of XSSFWorkbook class
            workbook = new XSSFWorkbook(fileInputStream);
        }
        // Check if the file is in the xls format
        else if (fileExtension.equals(".xls")) {
            // If it is an xls file, create an object of HSSFWorkbook class
            workbook = new HSSFWorkbook(fileInputStream);
        }

        // Get the specified sheet from the workbook
        Sheet sheet = workbook.getSheet(sheetName);

        // Get the current count of rows in the excel file
        int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

        // Get the header row from the sheet
        Row headerRow = sheet.getRow(0);

        // Create a new row and append it at the end of the sheet
        Row newRow = sheet.createRow(rowCount + 1);

        // Iterate over the cells of the newly created row
        for (int j = 0; j < headerRow.getLastCellNum(); j++) {
            // Fill data in the row
            Cell cell = newRow.createCell(j);
            cell.setCellValue(data[j]);
        }

        // Close the input stream
        fileInputStream.close();

        // Create a FileOutputStream object to write data into the excel file
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        // Write data into the excel file
        workbook.write(fileOutputStream);

        // Close the output stream
        fileOutputStream.close();
    }

    public static void main(String... args) throws IOException {
        // Create an array with the data in the same order in which you expect it to be filled in the excel file
        String[] valuesToWrite = {"2023", "AWS", "4343"};

        // Create an object of the current class
        ExcelWriter excelWriter = new ExcelWriter();

        // Write the data into the file using folder path, file name, sheet name, and the data to be filled
        excelWriter.writeToExcelFile(System.getProperty("user.dir"),
                                     "ExportedExcel.xlsx", "ExcelDemo", valuesToWrite);
    }
}

That's all about reading/writing data using the Apache POI and data driven testing using Selenium webDriver from excel files. Happy Testing!