Read Data from Excel in Selenium Using Apache POI: Tutorial

Why Use Excel Data in Selenium Tests

Running the same test case with multiple input sets is common in automation. Embedding data in scripts makes maintenance difficult and increases the risk of errors. Using Excel to store test data separates data from test logic, reduces maintenance effort, and allows dynamic execution of multiple scenarios.

Below are key reasons why using Excel data enhances Selenium tests:

• Centralized Data Management: Testers can maintain all input values in one place, simplifying updates across multiple test cases. For example, changing a customer ID in an Excel sheet automatically reflects in all scripts that read this file.
• Support for Multiple Test Scenarios: Excel allows testers to store different sets of data for positive, negative, and boundary test cases. This enables comprehensive test coverage without duplicating scripts.
• Dynamic Test Execution: Selenium scripts can read Excel data at runtime, allowing tests to run with different inputs dynamically. This is particularly useful for data-driven testing where the same workflow must be validated under multiple conditions.
• Improved Maintainability: When test data changes, updates are limited to the Excel file. This reduces maintenance effort and ensures scripts remain clean and reusable.
• Scalability for Large Datasets: Excel can handle large volumes of test data efficiently. Automation scripts can iterate through thousands of rows to execute extensive test scenarios without manual intervention.

Reading Excel Data in Selenium: Step-by-Step

Reading data from Excel is essential for data-driven testing, where the same workflow must be validated with multiple input sets. Selenium itself does not provide native support for Excel, so Apache POI, a Java library for reading and writing Microsoft Office files, is commonly used. 

Using Apache POI allows testers to extract data from both .xls (HSSF) and .xlsx (XSSF) files efficiently. Below is an explanation followed by practical steps that testers can follow:

1. Add Apache POI Dependencies

Apache POI jars need to be added to the project. For Maven users, include the following dependencies in pom.xml:

 <dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>5.2.3</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>5.2.3</version>

</dependency>

2. Load the Excel File

Create a FileInputStream to read the Excel file. This stream feeds into an Apache POI Workbook object. For example:

FileInputStream file = new FileInputStream("TestData.xlsx");

Workbook workbook = WorkbookFactory.create(file);

Sheet sheet = workbook.getSheet("Sheet1");

3. Iterate Through Rows and Cells

Use a nested loop to access each row and its cells. Testers can read data as strings, numbers, or booleans depending on the cell type.

for (Row row : sheet) {

    for (Cell cell : row) {

        switch (cell.getCellType()) {

            case STRING:

                System.out.println(cell.getStringCellValue());

                break;

            case NUMERIC:

                System.out.println(cell.getNumericCellValue());

                break;

            case BOOLEAN:

                System.out.println(cell.getBooleanCellValue());

                break;

        }

    }

}

4. Close Resources

Always close the Workbook and FileInputStream to prevent memory leaks:

workbook.close();

file.close();

Writing Excel Data in Selenium: Step-by-Step

Writing Excel data allows testers to maintain dynamic logs, track test execution, and feed subsequent test steps with updated values. Apache POI provides straightforward methods to create or update Excel files directly from Selenium scripts. 

Below is a structured approach to writing Excel data in Selenium:

1. Load or Create the Excel File

To write data, you can either open an existing Excel file or create a new one. For an existing file:

FileInputStream file = new FileInputStream("TestData.xlsx");

Workbook workbook = WorkbookFactory.create(file);

Sheet sheet = workbook.getSheet("Sheet1");

To create a new file:

Workbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet("Results");

2. Access or Create Rows and Cells

Identify the row and cell where data will be written. Use createRow and createCell for new entries.

Row row = sheet.createRow(0); // First row

Cell cell = row.createCell(0); // First column

cell.setCellValue("Test Passed"); // Writing a string value

3. Write Different Data Types

Apache POI supports strings, numbers, booleans, and formulas. For example:

row.createCell(1).setCellValue(123); // Numeric value

row.createCell(2).setCellValue(true); // Boolean value

row.createCell(3).setCellFormula("SUM(A1:B1)"); // Formula

4. Save and Close the File

Writing data requires a FileOutputStream. Always close resources to prevent file corruption:

FileOutputStream outFile = new FileOutputStream("TestData.xlsx");

workbook.write(outFile);

outFile.close();

workbook.close();

Configuring Apache POI in Selenium Projects

Before Selenium can interact with Excel files, Apache POI must be properly configured in the project. A correct setup ensures that reading and writing operations work reliably across different environments and Excel formats. This section explains how to configure Apache POI for both Maven and non-Maven Java projects and highlights common setup considerations.

1. Add Apache POI Dependencies in Maven Projects:

For Maven users, include the core POI libraries and the OOXML library for .xlsx support in pom.xml:

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>5.2.3</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>5.2.3</version>

</dependency>

These dependencies handle both .xls and .xlsx files, providing full reading and writing capabilities.

2. Include JARs in Non-Maven Projects 

For projects not using Maven, download the required JAR files from the Apache POI website. Include the following in the project’s build path:

• poi-5.2.3.jar
• poi-ooxml-5.2.3.jar
• poi-ooxml-full-5.2.3.jar
• commons-collections4-4.4.jar and other supporting libraries

Proper inclusion ensures all features, including formulas and styles, work without runtime errors.

3. Verify Java Version Compatibility

Apache POI requires Java 8 or higher. Ensure the project’s JDK matches the POI version requirements to avoid compatibility issues.

4. Organize Excel Files in Project Structure 

Store test data files in a dedicated folder, such as src/test/resources, to maintain clarity and support version control. Access the files using relative paths to ensure portability across environments.

5. Handle Exceptions and Resource Management

When reading or writing Excel files, wrap operations in try-catch blocks and always close FileInputStream, FileOutputStream, and Workbook objects to prevent memory leaks and file corruption.

Handling Different Data Types in Excel Files

Excel files can contain multiple data types, including strings, numbers, booleans, dates, and formulas. Selenium scripts must handle each type correctly to avoid errors during test execution. Apache POI provides methods to detect and process these data types efficiently. 

Below is an overview of the main data types and how to handle them in Selenium using Apache POI:

• String Data: Text values are the most common in Excel sheets. Use cell.getStringCellValue() to retrieve strings. For example, reading usernames or email addresses from the sheet requires this method.
• Numeric Data: Numbers can represent IDs, quantities, or amounts. Use cell.getNumericCellValue(). Be aware that numeric cells may also contain dates in Excel; in such cases, convert them appropriately using DateUtil.isCellDateFormatted(cell).
• Boolean Data: True/false values are stored as booleans. Use cell.getBooleanCellValue() to read them accurately. For instance, a test case might check whether a feature is enabled or disabled.
• Date Data: Dates are stored as numeric values in Excel. Apache POI provides DateUtil.getJavaDate(cell.getNumericCellValue()) to convert them into Java Date objects. This is essential when validating date fields in forms or reports.
• Formula Data: Cells with formulas require evaluating the formula result. Use FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); and then evaluator.evaluate(cell) to get the calculated value. This ensures that tests can read dynamic or calculated values accurately.
• Blank or Null Cells: Always check if a cell is null or blank using cell == null or cell.getCellType() == CellType.BLANK before accessing its value. This prevents NullPointerException during iteration.

Common Challenges When Handling Excel Files in Selenium

Working with Excel files in Selenium can introduce several challenges that may affect test reliability and maintainability. Being aware of these issues and implementing best practices helps prevent errors and ensures the smooth execution of data-driven tests.

Below are the most common challenges testers encounter and how to address them:

• Incorrect File Paths: Using absolute paths can cause tests to fail across different environments. Always use relative paths and store Excel files in a dedicated resources folder, such as src/test/resources, to ensure portability.
• Unsupported File Formats: Apache POI distinguishes between .xls (HSSF) and .xlsx (XSSF). Attempting to read an incompatible format can result in runtime exceptions. Ensure the correct API is used for the file type.
• Empty or Missing Cells: Accessing null or blank cells without checks can throw NullPointerException. Always verify cell existence using cell != null and check for CellType.BLANK before reading values.
• Mixed Data Types in a Column: Columns containing both numbers and strings can cause type mismatches. Use cell.getCellType() to detect the type dynamically and handle each case appropriately.
• Large Datasets and Performance Issues: Reading thousands of rows can consume significant memory. Use streaming APIs like SXSSFWorkbook for writing large datasets and iterate efficiently for reading.
• Formula Evaluation Errors: Cells with formulas may not return expected values if the formula is not evaluated. Always use FormulaEvaluator to calculate the result instead of reading the raw formula.
• File Locking Issues: Keeping a file open in Excel while a Selenium test tries to read or write to it can cause IOException. Close all streams properly and avoid editing the file manually during test execution.

Conclusion

Using Excel files in Selenium enables efficient management of large datasets and dynamic, data-driven test execution. Apache POI allows reading, writing, and handling different data types, ensuring scripts process inputs accurately and remain maintainable across multiple scenarios. 

To validate these data-driven tests in real-world conditions, run Selenium scripts on BrowserStack and ensure consistent execution across browsers, versions, and devices. Its real device cloud and Selenium Grid integration allow teams to identify environment-specific issues early and maintain reliable, cross-browser automation suites.