Read/Write Excel Files Using Apache POI In Selenium WebDriver

Selenium is a widely used software testing framework for web applications. Selenium has the highest community base and you will solutions to every problem in community forums and blogs. Although selenium is a very useful and complete library it misses some of the most used tools.

Reading and writing Excel files have lots of use cases but this feature does not come with selenium out of the box. So in order to Read and Write Excel files we use an external library which is Apache POI Library.

Apache POI (Poor Obfuscation Implementation) is a popular Java library for working with Microsoft Office documents, including Excel. You can use Apache POI alongside Selenium to read and write Excel data in your automation scripts. Here’s a step-by-step guide on how to do it:

1. Download and Set Up Apache POI:

First, you need to download the Apache POI library and add it to your Java project. You can download the latest version from the Apache POI website

2. Create a New Maven Project (Optional):

If you’re using Maven, you can add the Apache POI dependency to your project by including it in your pom.xml file:

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version> <!-- Use the latest version available -->
    </dependency>
</dependencies>

3. Import Necessary Packages

Now create a new Java class file and import the necessary Packages

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

4. Initialize WebDriver (Selenium):

Initialize your Selenium WebDriver as you normally would for web automation. Ensure you have a reference to the Excel file you want to read/write data to.

5. Reading Excel Data:

To read Excel data using Apache POI, you can use the following code as a reference:

We will use FileInputStream to open the Excel file and Initialize it which represent the entire Excel workbook. e use XSSFWorkbook because it's designed for .xlsx files, which are the most common format for Excel files.

We then select the sheet we want to work with using workbook.getSheetAt(0). In this example, we choose the first sheet (index 0). You can select a different sheet by changing the index or using the sheet's name with workbook.getSheet("SheetName").

We iterate through the rows and cells in the selected sheet using nested loops. For each cell, we print its value. You can perform different operations on the cell values based on your requirements.

After reading the data, it’s essential to close the Workbook and FileInputStream to release system resources properly.

FileInputStream excelFile = new FileInputStream(new File("path/to/your/excel/file.xlsx"));
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet sheet = workbook.getSheetAt(0); // Get the first sheet (0-indexed)

for (Row row : sheet) {
    for (Cell cell : row) {
        System.out.print(cell.toString() + "\t");
    }
    System.out.println(); // Move to the next row
}

workbook.close();
excelFile.close();

6. Writing Excel Data:

FileOutputStream excelFile = new FileOutputStream(new File("path/to/your/excel/file.xlsx"));
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1"); // Create a new sheet

// Create some data and write it to cells
Row row = sheet.createRow(0); // Create the first row (0-indexed)
Cell cell = row.createCell(0); // Create a cell in the first row and first column (0-indexed)
cell.setCellValue("Hello, Excel!");

workbook.write(excelFile);
workbook.close();
excelFile.close();

Make sure to replace "path/to/your/excel/file.xlsx" with the actual path to your Excel file.