How to Read Data from Excel in Selenium Using Apache POI
Rohit Rajpal
Learn how to read and write Excel data in Selenium using Apache POI with setup steps, examples, and best practices for reliable data-driven testing.
Managing test data is a critical part of Selenium automation. Hardcoding inputs works only for small scripts but quickly becomes unmanageable as test coverage grows. Using Excel to store data separates test logic from inputs, making scripts reusable, maintainable, and reliable.
Selenium itself does not provide built-in support for Excel, so testers rely on libraries like Apache POI to handle Excel files efficiently. Apache POI is a Java library that allows reading, writing, and updating Microsoft Excel files. It supports both .xls and .xlsx formats, and provides full control over cells and sheets.
This article explores how to set up Apache POI in a Selenium project and how to read and write Excel data effectively.
What is Apache POI?
Apache POI is a Java library designed to work with Microsoft Office documents, including Excel, Word, and PowerPoint files. For Selenium testing, its Excel modules are most relevant because they enable scripts to interact directly with spreadsheets. By using POI, testers can read input data, write test results, and update existing files without manually opening Excel.
The library provides separate APIs for different Excel formats. The HSSF API handles older .xls files, while the XSSF API supports newer .xlsx files. There is also SXSSF, optimized for writing large .xlsx files with lower memory usage, which is useful in large-scale test automation projects.
Apache POI is preferred over alternatives like JExcel or CSV because it offers more control over Excel-specific features, such as formatting, formulas, and multiple sheets. This makes it a reliable choice for professional automation frameworks where robust Excel handling is required.
Setting Up Apache POI in a Selenium Project
Before reading or writing Excel files in Selenium, Apache POI must be added to the project. Proper setup ensures that all required dependencies are available and prevents runtime errors. The process is straightforward for Maven and Gradle projects, and manual setup is also possible for projects without a build tool.
For Maven projects, the required dependencies can be added to the pom.xml file. The main modules are:
- poi: Core library for general Office document support
- poi-ooxml: Required for working with .xlsx files
- poi-ooxml-schemas: Supports Excel-specific features like formulas and styles
- commons-collections4: Needed by POI for collection utilities
For Gradle projects, the same dependencies can be included in the build.gradle file under dependencies.
If Maven or Gradle is not used, testers can download the POI JAR files from the official Apache POI website and add them manually to the project’s classpath. Ensuring all transitive dependencies are included is essential; otherwise, runtime errors may occur while reading or writing Excel files.
Once the dependencies are in place, the project is ready to use Apache POI for reading and writing Excel data during Selenium tests.
How to Read Data from Excel in Selenium
Reading data from Excel allows Selenium tests to be fully data-driven. By keeping inputs in Excel, the same test scripts can run against multiple datasets without modifying the code. This improves maintainability and supports extensive test coverage. Before accessing Excel data, it is important to understand the basic structure of an Excel file in Apache POI:
- Workbook: Represents the entire Excel file. It can be an HSSFWorkbook for .xls files or an XSSFWorkbook for .xlsx files.
- Sheet: Represents a single worksheet in the workbook. Tests often read data from specific sheets identified by name or index.
- Row: Represents a horizontal row in the sheet. Rows are typically used to group related test data.
- Cell: Represents a single data point within a row. The cell type can be string, numeric, boolean, or formula.
Here is a step-by-step approach to reading data using Apache POI:
- Load the Excel file: Use FileInputStream to open the file from the system.
- Create the Workbook instance: Instantiate HSSFWorkbook or XSSFWorkbook depending on the file format.
- Access the Sheet: Get the required sheet either by name or index.
- Iterate through Rows and Cells: Loop through each row and cell to fetch data.
- Handle Cell Types: Convert the cell value to the required type using methods like getStringCellValue() or getNumericCellValue().
For example, reading a username and password from a login sheet:
FileInputStream file = new FileInputStream("TestData.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheet("LoginData");
for (int i = 1; i <= sheet.getLastRowNum(); i++) { // skip header row
XSSFRow row = sheet.getRow(i);
String username = row.getCell(0).getStringCellValue();
String password = row.getCell(1).getStringCellValue();
// Use username and password in Selenium test
}
workbook.close();
file.close();
This approach allows dynamic fetching of test data, enabling the same Selenium test to execute with different sets of input values. Proper handling of rows, cells, and data types ensures accuracy and reduces runtime errors.
How to Write Data into Excel in Selenium
Writing data into Excel is essential for logging test results, maintaining reports, or updating test inputs during automation. Apache POI allows Selenium scripts to create new Excel files or modify existing ones, giving testers full control over data tracking. Proper writing also ensures that results are structured, readable, and reusable for future test runs.
The basic process involves:
- Creating or opening a Workbook: Use XSSFWorkbook for .xlsx files or HSSFWorkbook for .xls files.
- Accessing or creating a Sheet: Fetch an existing sheet or create a new one using createSheet().
- Creating Rows and Cells: Define rows and cells where data will be written. Rows and cells can be appended dynamically.
- Setting Cell Values: Assign string, numeric, or boolean values depending on the data type.
- Saving the Workbook: Write changes to the file using FileOutputStream and close all resources properly.
For example, writing a test result into an Excel file:
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("TestResults");
XSSFRow header = sheet.createRow(0);
header.createCell(0).setCellValue("TestCase");
header.createCell(1).setCellValue("Status");
XSSFRow row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("Login Test");
row1.createCell(1).setCellValue("Pass");
FileOutputStream fileOut = new FileOutputStream("TestResults.xlsx");
workbook.write(fileOut);
fileOut.close();
workbook.close();
This method allows testers to log results dynamically for multiple test cases. For large datasets, using loops and proper exception handling ensures that all data is written efficiently without overwriting or corrupting existing files. Writing to Excel also enables integration with reporting tools or dashboards for better test visibility.
Best Practices for Excel Data Handling in Selenium
Handling Excel data efficiently is crucial for creating reliable, maintainable, and scalable Selenium tests. Poor practices can lead to brittle tests, incorrect results, or slow execution.
Below are key practices to consider when working with Excel in Selenium:
- Keep Data and Logic Separate: Store all test inputs, expected results, and configurable values in Excel. Avoid hardcoding data in scripts to maintain flexibility.
- Use Meaningful Sheet and Column Names: Name sheets and columns clearly, for example, LoginData with Username and Password columns, to make the data self-explanatory.
- Handle Different Data Types Carefully: Explicitly check cell types before reading values to prevent runtime errors from mixed or unexpected formats.
- Close Resources Properly: Always close FileInputStream, FileOutputStream, and Workbook objects to avoid memory leaks and file locks.
- Validate Excel Structure: Before reading data, verify that the required sheets, rows, and columns exist. This prevents exceptions during test execution.
- Use Utility Methods: Create reusable methods for reading and writing Excel data. This reduces code duplication and improves maintainability.
- Consider Large Datasets: For Excel files with thousands of rows, use SXSSFWorkbook for streaming to reduce memory consumption during writing operations.
How Does BrowserStack Support Excel-Based Selenium Testing
Excel-driven Selenium tests often need to be executed across multiple browsers, operating systems, and devices. Running these tests locally can be slow, inconsistent, and hard to scale.
BrowserStack is a cloud platform that enhances Selenium testing by enabling tests to run on real devices and browsers. This ensures that your automation reflects real-world user experiences across different environments.
Here’s how BrowserStack benefits Selenium testing:
- Selenium Grid on Cloud: BrowserStack provides a cloud-based Selenium Grid, allowing tests to run in parallel across multiple browsers, devices, and operating systems without maintaining local infrastructure.
- Cross-Browser Testing: Verify your application across multiple browsers and operating systems to ensure consistent behavior regardless of the user’s platform.
- Parallel Testing: Run multiple tests simultaneously on different devices and browsers to reduce execution time for large test suites.
- Easy Debugging: Access video recordings, screenshots, and logs for each test run to identify and fix issues quickly.
- CI/CD Integration: BrowserStack integrates with popular CI/CD tools such as Jenkins, CircleCI, and GitHub Actions, enabling automated test execution as part of the development pipeline.
Conclusion
Using Excel for test data in Selenium makes automation scripts flexible, maintainable, and scalable. Externalizing inputs allows the same scripts to run across multiple datasets without changes. Apache POI provides a reliable way to read, write, and update Excel files, enabling dynamic, data-driven testing while keeping test logic separate from test data.
Integrating Selenium tests with BrowserStack extends these benefits to real-world environments. BrowserStack Automate provides access to thousands of real devices and browsers, a cloud-based Selenium Grid for parallel execution, and detailed debugging tools including logs, screenshots, and videos.
Written by
Rohit Rajpal
Rohit Rajpal is a B2B Content Strategist at BrowserStack, helping SaaS brands build AI-first strategies that drive authority and revenue. He writes about content, strategy, and the future of search in the zero-click era.
Contents
Subscribe for latest updates
Share this article
Related posts
Detecting and Handling Broken Links in Selenium
Learn how to detect and handle broken links and images using Selenium WebDriver for improved user experience and SEO performance.
Azma Banu
September 17, 2025
Mastering XPath in Selenium: A Comprehensive Guide
Learn XPath syntax, how to find XPath in Chrome, and use it in Selenium with examples. Explore testing on a real device cloud for better accuracy
Azma Banu
September 17, 2025
Selenium Tutorial: Automate Web Testing
Learn Selenium for automated web testing with real devices, parallel execution, cloud grid, and cross-platform testing for efficient, scalable test automation.
Azma Banu
September 17, 2025