Reading Excel file in Selenium WebDriver | Apache POI Library

 

There are several ways to read an Excel file in Selenium WebDriver. Here is an example of how to read an Excel file using the Apache POI library:

First, you need to download the Apache POI library and add it to your project classpath. 

You can download it from the Apache POI website (https://poi.apache.org/)

1.Create a new Java class and import the necessary POI classes:

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;

2.In your test method, use the FileInputStream class to read the Excel file:

FileInputStream file = new FileInputStream("path/to/excel/file.xlsx");
Workbook workbook = new XSSFWorkbook(file);

3.Get the sheet from the workbook:

Sheet sheet = workbook.getSheetAt(0);

4.Iterate through the rows and cells of the sheet:

for (Row row : sheet) {
 
    for (Cell cell : row) {
 
        System.out.print(cell.getStringCellValue() + " ");
 
    }
 
    System.out.println();
 
}
 

In this example, the FileInputStream class is used to read the Excel file, XSSFWorkbook class is used to create a new workbook object and the getSheetAt() method is used to get the sheet from the workbook. Then the for-each loop is used to iterate through the rows and cells of the sheet and the getStringCellValue() method is used to get the value of each cell as a string.

It's important to note that the above example is for reading an Excel file in the xlsx format. If you are working with an Excel file in the xls format, you can use the HSSFWorkbook class instead of the XSSFWorkbook class to create a new workbook object.

Additionally, you can also use the DataFormatter class to format the cell value before using it in your test script.

 

DataFormatter formatter = new DataFormatter();
for (Row row : sheet) {
 
  for (Cell cell : row) {
 
        String value = formatter.formatCellValue(cell);
 
        System.out.print(value + " ");
 
    }
 
    System.out.println();
 
}

In this example, the DataFormatter class is used to create a new formatter object and the formatCellValue(cell) method is used to format the cell value.

It's important to note that reading data from an excel sheet can be slow and resource-intensive. You should minimize the number of times you read data from the sheet and you can use the data in your test script.

Also, you should always close the file after reading it, to free up resources and avoid potential data corruption issues.

file.close();
 
workbook.close();

Finally, it's important to note that reading an excel sheet with many rows and columns can be slow, so it's always better to use only the needed cells and rows.

Example Code for how to read an Excel file in Selenium WebDriver using the Apache POI library:

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 ExcelReader {
 
 
 
    public static void main(String[] args) throws IOException {
 
        // File path
 
        String filePath = "path/to/excel/file.xlsx";
 
 
 
        // Open the file
 
        FileInputStream file = new FileInputStream(filePath);
 
 
 
        // Create a workbook object
 
        Workbook workbook = new XSSFWorkbook(file);
 
 
 
        // Get the sheet at index 0
 
        Sheet sheet = workbook.getSheetAt(0);
 
 
 
        // Iterate through the rows
 
        for (Row row : sheet) {
 
            // Iterate through the cells
 
            for (Cell cell : row) {
 
                // Get the cell value
 
                String value = cell.getStringCellValue();
 
                System.out.print(value + " ");
 
            }
 
            System.out.println();
 
        }
 
 
 
        // Close the file
 
        file.close();
 
        workbook.close();
 
    }
 
}

In this example, the FileInputStream class is used to read the Excel file, XSSFWorkbook class is used to create a new workbook object and the getSheetAt() method is used to get the sheet from the workbook. Then the for-each loop is used to iterate through the rows and cells of the sheet and the getStringCellValue() method is used to get the value of each cell as a string.

It's important to note that the above example is for reading an Excel file in the xlsx format. If you are working with an Excel file in the xls format, you can use the HSSFWorkbook class instead of the XSSFWorkbook class to create a new workbook object.

Also, you should always close the file after reading it, to free up resources and avoid potential data corruption issues.

You should also make sure that the path to the excel file is correct and the file exists in the specified location.