Read Data from Excel Using Selenium

Read Data from Excel Using Selenium [2026]

Excel files are widely used to manage test data because they are easy to edit, review, and share across teams. In Selenium automation, Excel-based data handling enables data-driven testing, where the same test logic is executed with multiple input combinations without changing the test code.

Using Excel as a data source helps separate test logic from test data, making automation suites easier to maintain and scale. It is commonly used for scenarios such as form validation, multi-user workflows, configuration testing, and regression coverage across large data sets. When implemented correctly, Excel-driven Selenium tests improve reusability while keeping test behavior predictable across environments.

What Is Apache POI?

Apache POI is a Java-based library used to read, write, and modify Microsoft Office files, including Excel, Word, and PowerPoint documents. In Selenium automation, Apache POI is most commonly used to work with Excel files as part of data-driven testing.

Apache POI provides APIs to access Excel workbooks, sheets, rows, and cells programmatically. It supports both legacy .xls files and modern .xlsx formats, allowing Selenium tests to consume external test data without hardcoding values in the test logic. By using Apache POI, test data can be updated independently of automation code, improving maintainability and scalability of Selenium test suites.

Why Use Apache POI for Selenium Automation

Apache POI is widely used in Selenium automation because it provides a structured and reliable way to manage external test data. It allows Selenium tests to scale without hardcoding values, making automation easier to maintain as test coverage grows.

• Supports data-driven testing: Apache POI enables Selenium tests to read input data such as usernames, form values, and configuration parameters directly from Excel files, allowing the same test logic to run with multiple data sets.
• Separates test logic from test data: Keeping data in Excel files avoids frequent code changes when test inputs change. This improves maintainability and reduces the risk of introducing logic errors.
• Handles both XLS and XLSX formats: Apache POI supports legacy and modern Excel formats, making it compatible with a wide range of existing test data sources.
• Provides fine-grained control over Excel structure: The library allows precise access to sheets, rows, cells, and data types, which is useful for complex test scenarios and result tracking.
• Integrates easily with Selenium test frameworks: Apache POI works seamlessly with common Selenium setups, enabling clean integration with test runners and CI pipelines.

Using Apache POI helps teams build flexible, scalable, and reusable Selenium test suites driven by external Excel data rather than hardcoded values.

Prerequisites for Using Apache POI with Selenium

Before using Apache POI with Selenium, certain setup and environment requirements must be in place to ensure smooth integration and reliable data handling.

• Java development environment: Apache POI is a Java-based library, so a supported Java Development Kit (JDK) must be installed and properly configured. The project should be set up as a Java application.
• Selenium WebDriver configured: Selenium WebDriver should already be installed and working correctly in the project. Basic browser automation setup must be verified before introducing Excel-based data handling.
• Test framework integration: A test framework such as TestNG or JUnit should be configured to manage test execution, assertions, and reporting. Apache POI works alongside these frameworks without additional configuration.
• Build tool setup: A dependency management tool like Maven or Gradle should be available to manage Apache POI libraries and related dependencies efficiently.
• Basic understanding of Excel structure: Familiarity with workbooks, sheets, rows, cells, and common Excel data types helps avoid parsing errors and improves data handling logic.
• File access permissions: The project must have read and write permissions for the directory where Excel files are stored to allow seamless data reading and updates.

Meeting these prerequisites ensures Apache POI can be integrated cleanly into Selenium automation without environment-related issues.

Installing and Setting Up Apache POI

Apache POI must be added to the Selenium project before Excel files can be read or written programmatically. The setup process is straightforward when using a dependency management tool and ensures all required libraries are available at runtime.

• Add Apache POI dependencies to the project

The recommended approach is to use a build tool such as Maven or Gradle so dependencies are resolved automatically. Apache POI is split into multiple modules, and Excel handling requires both the core and OOXML components.

For Maven-based projects, include the required dependencies in the project configuration file. This ensures support for modern .xlsx files as well as shared utilities used by the library.

• Verify dependency resolution

After adding the dependencies, refresh or rebuild the project so the libraries are downloaded and added to the classpath. Any dependency resolution errors should be fixed before proceeding, as Apache POI relies on supporting libraries for XML processing and file handling.

• Organize Excel files within the project structure

Excel files used for test data should be stored in a predictable location, such as a dedicated test resources directory. Keeping test data separate from source code improves maintainability and avoids path-related issues during CI execution.

• Create a utility layer for Excel operations

Instead of reading or writing Excel files directly inside test classes, it is best to create reusable utility methods. This centralizes file handling logic and makes tests easier to read and maintain.

• Validate setup with a simple read operation

As a final step, perform a basic operation such as opening a workbook and reading a single cell value. This confirms that Apache POI is correctly configured and that file paths and permissions are set up properly.

Once Apache POI is installed and verified, it can be safely integrated into Selenium tests to support scalable, Excel-driven automation workflows.

Understanding Excel File Structure (XLS vs XLSX)

Before reading data from Excel in Selenium, it is important to understand the structural differences between XLS and XLSX file formats. These differences affect how Apache POI processes files and how test data should be managed.

• XLS format (Excel 97–2003)

XLS files use a binary file structure and are supported in Apache POI through the HSSF (Horrible Spreadsheet Format) API. This format has limitations on row and column counts and is less efficient for large datasets. It is mainly encountered in legacy projects where older Excel files are still in use.

• XLSX format (Excel 2007 and later)

XLSX files are based on the Open XML standard and use a compressed XML structure. Apache POI handles this format through the XSSF API. XLSX supports larger datasets, better performance, and modern Excel features, making it the preferred format for new Selenium automation projects.

• Workbook, sheet, row, and cell hierarchy

Regardless of format, Excel files follow the same logical structure. A workbook contains one or more sheets, each sheet consists of rows, and each row contains cells. Apache POI exposes this hierarchy through dedicated classes, allowing Selenium tests to navigate and extract data precisely.

• Data type handling differences

Excel cells can store different data types such as strings, numbers, booleans, and dates. XLSX handles data types more consistently than XLS, reducing parsing issues when reading values for Selenium tests.

• Compatibility and maintenance considerations

While Apache POI supports both formats, XLSX is easier to maintain, more scalable, and better suited for CI environments. Migrating legacy XLS files to XLSX simplifies long-term maintenance of data-driven Selenium tests.

Choosing the correct Excel format upfront helps avoid performance bottlenecks and data handling issues in Selenium automation.

Reading Data from Excel Files in Selenium

Reading data from Excel files is a core part of implementing data-driven Selenium tests. Instead of hardcoding input values, Selenium can pull test data from external Excel sheets and execute the same test logic with different data sets. This approach improves reusability, reduces code changes, and makes test coverage easier to expand.

Apache POI provides the APIs needed to navigate workbooks, sheets, rows, and cells, allowing Selenium tests to read structured data reliably and use it during execution.

Reading Data from a Single Cell

Reading a single cell is useful for pulling one-off values such as a base URL, a username/password, or an environment flag. Apache POI reads Excel data by opening the workbook, selecting a sheet, then addressing a row and cell by index (both are zero-based).

import java.io.FileInputStream;

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



public class ExcelReadSingleCell {

  public static void main(String[] args) throws Exception {

    try (FileInputStream fis = new FileInputStream("testdata.xlsx");

         Workbook workbook = WorkbookFactory.create(fis)) {



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

      Row row = sheet.getRow(1);          // 2nd row

      Cell cell = row.getCell(0);         // 1st column



      // DataFormatter handles numbers/dates without manual casting

      DataFormatter formatter = new DataFormatter();

      String value = formatter.formatCellValue(cell);



      System.out.println("Cell value: " + value);

    }

  }

}

Reading Multiple Rows and Columns

When Excel holds a table of test cases (each row as a test record), the usual pattern is to loop over rows and columns and store results into a structure (for example, Object[][] for TestNG, or a List<Map<String, String>> for readable key-based access).

import java.io.FileInputStream;

import java.util.*;

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



public class ExcelReadTable {

  public static List<Map<String, String>> readSheetAsMaps(String path, String sheetName) throws Exception {

    try (FileInputStream fis = new FileInputStream(path);

         Workbook workbook = WorkbookFactory.create(fis)) {



      Sheet sheet = workbook.getSheet(sheetName);

      DataFormatter formatter = new DataFormatter();



      // Header row defines column names

      Row header = sheet.getRow(0);

      int lastCol = header.getLastCellNum();



      List<Map<String, String>> rows = new ArrayList<>();

      int lastRow = sheet.getLastRowNum();



      for (int r = 1; r <= lastRow; r++) {

        Row row = sheet.getRow(r);

        if (row == null) continue;



        Map<String, String> record = new LinkedHashMap<>();

        for (int c = 0; c < lastCol; c++) {

          String key = formatter.formatCellValue(header.getCell(c)).trim();

          String val = formatter.formatCellValue(row.getCell(c)).trim();

          record.put(key, val);

        }

        rows.add(record);

      }

      return rows;

    }

  }

}

This style scales better than hardcoding column indexes in tests because tests can access values by column name (for example, record.get(“username”)).

Handling Different Data Types in Excel

Excel cells can store strings, numbers, booleans, formulas, blanks, and dates. Type issues are a frequent source of flaky “data-driven” tests (for example, 1234 becoming 1234.0, or dates turning into numeric serials). A predictable strategy avoids surprises.

• Use DataFormatter for “what the user sees”: It converts a cell into a string using Excel’s display rules, which is ideal for form inputs and assertions.
• Handle formulas explicitly: Either read the formula string or evaluate it to its computed value using FormulaEvaluator.
• Detect date cells correctly: Dates are stored as numbers in Excel; use DateUtil.isCellDateFormatted(cell) to confirm.

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

import org.apache.poi.ss.usermodel.DateUtil;



public class ExcelTypeHandling {

  public static String getCellAsString(Cell cell, Workbook workbook) {

    if (cell == null) return "";



    DataFormatter formatter = new DataFormatter();

    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();



    CellType type = cell.getCellType();

    if (type == CellType.FORMULA) {

      // Evaluate the computed value instead of returning the formula text

      return formatter.formatCellValue(cell, evaluator).trim();

    }



    if (type == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {

      // DataFormatter will format dates as displayed in Excel

      return formatter.formatCellValue(cell).trim();

    }



    return formatter.formatCellValue(cell).trim();

  }

}

This approach keeps Selenium inputs consistent (especially for OTP-like numbers, currency, or date strings).

Iterating Through Sheets, Rows, and Cells

For large workbooks or multi-module test suites, it’s common to scan multiple sheets, apply filters, and only load relevant rows (for example, Run=Y, Env=staging, Feature=checkout). Iteration should be robust against empty rows/cells and irregular formatting.

• Iterate sheets when test data is organized by feature: For example, separate sheets for Login, Checkout, Profile.
• Skip blank rows safely: Excel sheets often contain gaps; check for null rows and “all blank” lines.
• Avoid hardcoding boundaries: Use getLastRowNum() and getLastCellNum() to compute ranges.
• Add lightweight filtering: Load only rows marked runnable to keep test execution predictable.

import java.io.FileInputStream;

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



public class ExcelIterateAll {

  public static void main(String[] args) throws Exception {

    try (FileInputStream fis = new FileInputStream("testdata.xlsx");

         Workbook workbook = WorkbookFactory.create(fis)) {



      DataFormatter formatter = new DataFormatter();



      for (int s = 0; s < workbook.getNumberOfSheets(); s++) {

        Sheet sheet = workbook.getSheetAt(s);

        System.out.println("Sheet: " + sheet.getSheetName());



        for (int r = 0; r <= sheet.getLastRowNum(); r++) {

          Row row = sheet.getRow(r);

          if (row == null) continue;



          // Example: print row values safely

          short lastCell = row.getLastCellNum();

          for (int c = 0; c < lastCell; c++) {

            String value = formatter.formatCellValue(row.getCell(c)).trim();

            System.out.print(value + (c == lastCell - 1 ? "" : " | "));

          }

          System.out.println();

        }

      }

    }

  }

}

If the goal is to feed Selenium tests, the next step is usually to convert these loops into a reusable utility that returns structured records (maps/arrays) and filters rows by a “Run” flag to avoid executing every dataset blindly.

Writing Data into Excel Files Using Selenium

Writing back to Excel is commonly used to store test outputs such as pass/fail status, timestamps, generated IDs, or comments. Selenium does not write to Excel directly; Selenium triggers application behavior, and Apache POI writes the resulting data into the workbook. The safest approach is to load the workbook, update only the required cells, and persist changes to disk.

Writing Data to Existing Cells

This approach updates a cell that already exists (for example, a “Status” column). It is useful when the Excel sheet is pre-structured with fixed headers and test rows.

import java.io.*;

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



public class ExcelWriteExistingCell {

  public static void main(String[] args) throws Exception {

    String path = "testdata.xlsx";



    try (FileInputStream fis = new FileInputStream(path);

         Workbook workbook = WorkbookFactory.create(fis)) {



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

      Row row = sheet.getRow(1);                // 2nd row

      Cell cell = row.getCell(3);              // 4th column (Status)



      if (cell == null) cell = row.createCell(3);

      cell.setCellValue("PASS");



      try (FileOutputStream fos = new FileOutputStream(path)) {

        workbook.write(fos);

      }

    }

  }

}

Key point: Always write using a FileOutputStream after updates, otherwise changes remain only in memory.

Creating New Rows and Cells

This is used when logging new records (for example, adding a result entry per run). It works well for execution logs, audit records, and reports where each run appends a new row.

import java.io.*;

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



public class ExcelAppendRow {

  public static void main(String[] args) throws Exception {

    String path = "results.xlsx";



    try (FileInputStream fis = new FileInputStream(path);

         Workbook workbook = WorkbookFactory.create(fis)) {



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

      if (sheet == null) sheet = workbook.createSheet("Runs");



      int nextRowIndex = sheet.getLastRowNum() + 1;

      Row newRow = sheet.createRow(nextRowIndex);



      newRow.createCell(0).setCellValue("TC_Login_01");

      newRow.createCell(1).setCellValue("PASS");

      newRow.createCell(2).setCellValue(System.currentTimeMillis());



      try (FileOutputStream fos = new FileOutputStream(path)) {

        workbook.write(fos);

      }

    }

  }

}

If the sheet is empty and getLastRowNum() returns 0, this pattern still works but may overwrite an existing header row unless headers are handled explicitly.

Updating Test Results in Excel

Updating results becomes cleaner when the sheet has a header row and a unique identifier per test case (for example, TestCaseId). A practical pattern is: locate the row by TestCaseId, find the “Status” and “Message” columns by header name, then update them.

import java.io.*;

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

public class ExcelUpdateByTestId {

  public static void updateResult(String path, String sheetName, String testCaseId,

                                  String status, String message) throws Exception {

    try (FileInputStream fis = new FileInputStream(path);

         Workbook workbook = WorkbookFactory.create(fis)) {



      Sheet sheet = workbook.getSheet(sheetName);

      DataFormatter formatter = new DataFormatter();



      Row header = sheet.getRow(0);

      int statusCol = -1, messageCol = -1, idCol = -1;



      for (int c = 0; c < header.getLastCellNum(); c++) {

        String colName = formatter.formatCellValue(header.getCell(c)).trim();

        if (colName.equalsIgnoreCase("TestCaseId")) idCol = c;

        if (colName.equalsIgnoreCase("Status")) statusCol = c;

        if (colName.equalsIgnoreCase("Message")) messageCol = c;

      }



      if (idCol == -1 || statusCol == -1) {

        throw new IllegalStateException("Required columns not found in header.");

      }



      for (int r = 1; r <= sheet.getLastRowNum(); r++) {

        Row row = sheet.getRow(r);

        if (row == null) continue;



        String id = formatter.formatCellValue(row.getCell(idCol)).trim();

        if (!id.equals(testCaseId)) continue;



        Cell statusCell = row.getCell(statusCol);

        if (statusCell == null) statusCell = row.createCell(statusCol);

        statusCell.setCellValue(status);



        if (messageCol != -1) {

          Cell msgCell = row.getCell(messageCol);

          if (msgCell == null) msgCell = row.createCell(messageCol);

          msgCell.setCellValue(message);

        }

        break;

      }



      try (FileOutputStream fos = new FileOutputStream(path)) {

        workbook.write(fos);

      }

    }

  }

}

Practical notes for stability:

• Avoid writing to the same Excel file concurrently during parallel runs. Use per-thread result files or consolidate results after execution.
• Keep result columns consistent (Status, Message, Timestamp) so updates remain predictable.
• If Excel is used only for reporting, consider writing results into a separate results workbook rather than mutating the input test data file.

Common Exceptions and Error Handling in Apache POI

Apache POI failures usually come from file handling, sheet/cell access, or type conversions. Handling these cleanly keeps data-driven Selenium runs predictable and avoids “random” failures caused by bad test data or file issues.

• FileNotFoundException / Invalid path: This happens when the Excel file path is wrong, the file was moved, or the CI workspace does not include the file. Use predictable resource paths and fail fast with a clear message that includes the resolved path.
• EncryptedDocumentException / Protected workbook: POI cannot read password-protected or encrypted Excel files unless explicitly handled. If teams share protected sheets, automation should use an unprotected copy of the workbook for CI.
• IOException (file locked or stream issues): This occurs when Excel is open locally, multiple tests try to write to the same file, or streams are not closed properly. Always use try-with-resources and avoid writing to shared files during parallel execution.
• NullPointerException from missing sheet/row/cell: getSheet(), getRow(), and getCell() can return null. Defensive checks prevent runtime crashes and allow the test to throw meaningful errors (for example, “Sheet ‘Login’ not found”).
• IllegalStateException (wrong cell type): Calling getStringCellValue() on a numeric cell or similar type mismatches causes this. Use DataFormatter for consistent string extraction or switch on CellType.
• Formula-related issues: Formula cells may return formula text rather than the evaluated value unless a FormulaEvaluator is used. For data-driven testing, always evaluate formulas if business users maintain Excel sheets with computed values.

A practical error-handling pattern is to validate file existence, validate required sheets and headers, and use DataFormatter to avoid type crashes.

Best Practices for Excel-Driven Selenium Tests

Excel-driven automation works well when the data layer is treated as test infrastructure, not ad-hoc file parsing inside tests.

• Separate test data from test logic: Keep Excel parsing in a utility class or data provider, not inside Selenium test methods. Tests should receive clean inputs (strings/maps/objects) and focus on browser actions and validations.
• Use headers and read by column names: Relying on column indexes breaks when columns move. A header-based approach makes Excel sheets flexible and safer to maintain.
• Add a “Run” flag and environment columns: Include fields such as Run=Y/N, Env, or Browser to control which rows execute. This prevents accidental execution of incomplete datasets and helps manage CI vs local runs.
• Keep data types predictable: Store inputs like phone numbers, ZIP codes, and IDs as text to avoid Excel formatting altering values. Use DataFormatter so Selenium receives the displayed value consistently.
• Do not write results into the input workbook during parallel runs: Writing to the same Excel file from multiple threads causes file locks and corrupted outputs. If result capture is required, write to a separate results workbook per run or per thread and merge later.
• Validate test data before test execution: Check that required columns exist, mandatory values are present, and rows are not malformed before starting the browser. Failing early on data issues saves time and reduces CI noise.
• Version control Excel test data carefully: Excel changes can silently break tests. Store test data alongside code with clear change tracking and enforce review for updates to critical sheets.

Performance Considerations for Large Excel Files

Large workbooks can slow test startup, increase memory usage, and become a hidden bottleneck in data-driven Selenium suites. Performance improves when file IO is minimized and data loading is controlled.

• Avoid reading the file repeatedly for every test: Loading the workbook for each test method increases IO cost significantly. Read once per suite or per class, cache the parsed data in memory, and feed it to tests through data providers.
• Load only the required sheet and columns: If the workbook contains multiple sheets and large tables, read only the relevant sheet and only the columns needed for the current test set. Unnecessary iteration increases runtime.
• Prefer XLSX for scalability: XLSX generally handles modern datasets better and avoids limitations of legacy XLS. It also aligns better with long-term maintenance.
• Use streaming for very large XLSX files: For extremely large Excel files, POI’s streaming approaches reduce memory overhead by processing rows incrementally rather than loading the full sheet into memory. This is useful for bulk-data validations or large parameterized suites.
• Keep Excel files small and purpose-driven: A single “mega workbook” becomes slow and hard to maintain. Splitting data by feature or module improves read speed and reduces accidental coupling across tests.
• Measure and optimize the real bottleneck: If suite startup time is high, profile where time is spent—parsing, iteration, conversions, or repeated disk reads—then optimize the hot path (usually repeated workbook loading).

Applying these practices ensures Excel-driven Selenium tests remain maintainable and do not become slower or less reliable as the dataset grows.

When to Avoid Excel-Based Test Data

Excel works well for structured, human-maintained test inputs, but it is not always the right choice. In some cases, using Excel can add unnecessary complexity or reduce test reliability.

• Highly dynamic or generated data: If test data is created at runtime (timestamps, UUIDs, tokens, OTPs), Excel adds little value. Generating data programmatically keeps tests simpler and avoids constant file updates.
• Complex relational data models: Scenarios involving deep object relationships, nested structures, or large JSON payloads are better handled using code-based fixtures or external data files designed for structured data.
• Parallel-heavy test execution: Writing to or reading from shared Excel files during parallel runs can cause file locks and race conditions. For highly parallel suites, in-memory data providers or per-test data sources are more stable.
• Frequently changing test inputs: When test data changes often, maintaining Excel files can become error-prone and hard to track. Code-based data or configuration-driven approaches provide better version control visibility.
• Performance-critical test suites: Large Excel files increase startup time and memory usage. For fast-running smoke or CI-critical suites, lightweight data sources reduce overhead.

Conclusion

Excel-based data handling enables Selenium tests to scale by separating test logic from test inputs and supporting structured, repeatable data-driven execution. When implemented with the right patterns—clear file structure, robust parsing, defensive error handling, and controlled execution—Excel can be a practical and maintainable data source for automation.

However, Excel is not a universal solution. It works best for stable, human-managed datasets and becomes less effective for highly dynamic data, heavy parallelism, or performance-critical suites. Choosing when to use Excel, and when to rely on programmatic data generation or alternative sources, is key to keeping Selenium tests reliable and efficient.