Reading and Writing Excel Files in Selenium Java Automation

In the world of test automation, managing test data efficiently is just as crucial as writing effective test scripts. Excel files are commonly used to store and manage test data due to their simplicity and accessibility. When using Selenium with Java, integrating Excel-based data handling allows testers to perform data-driven testing, where input and expected results are read from spreadsheets.

In this blog, we’ll explore how to read and write Excel files using the Apache POI library in a Selenium Java automation framework. Whether you're building a scalable test suite or simply trying to avoid hardcoded test data, this guide will help you enhance your automation skills.

Why Use Excel in Selenium Automation?

• Test Data Management: Store inputs, expected outputs, and configurations.
• Scalability: Easy to modify test cases without touching code.
• Non-Technical Collaboration: Testers or stakeholders can provide data in Excel without needing programming knowledge.

Tools Required

To work with Excel files in Selenium Java:

• Apache POI Library – A powerful Java API for Microsoft documents.
• Selenium WebDriver – For browser automation.
• Maven or Gradle – To manage dependencies.

Step 1: Add Apache POI Dependency

If you’re using Maven, add the following to your pom.xml:

xml

<dependency>

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

    <artifactId>poi-ooxml</artifactId>

    <version>5.2.3</version>

</dependency>

Step 2: Read Data from Excel

Here's a method to read data from an Excel sheet:

java

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

import java.io.FileInputStream;

public class ExcelReader {

    public static String readCell(String filePath, String sheetName, int row, int cell) throws Exception {

        FileInputStream file = new FileInputStream(filePath);

        Workbook workbook = WorkbookFactory.create(file);

        Sheet sheet = workbook.getSheet(sheetName);

        Row r = sheet.getRow(row);

        Cell c = r.getCell(cell);

        return c.getStringCellValue();

    }

}

Usage in Selenium Test:

java

String username = ExcelReader.readCell("data.xlsx", "LoginData", 1, 0);

driver.findElement(By.id("username")).sendKeys(username);

Step 3: Write Data to Excel

To log results or modify data:

java

import java.io.FileOutputStream;

public class ExcelWriter {

    public static void writeCell(String filePath, String sheetName, int row, int cell, String value) throws Exception {

        FileInputStream file = new FileInputStream(filePath);

        Workbook workbook = WorkbookFactory.create(file);

        Sheet sheet = workbook.getSheet(sheetName);

        Row r = sheet.getRow(row);

        if (r == null) r = sheet.createRow(row);

        Cell c = r.createCell(cell);

        c.setCellValue(value);

        file.close();

        FileOutputStream outFile = new FileOutputStream(filePath);

        workbook.write(outFile);

        outFile.close();

    }

}

Usage Example:

java

ExcelWriter.writeCell("data.xlsx", "TestResults", 1, 2, "Pass");

Step 4: Implement Data-Driven Testing

Using loops and Excel data, you can easily run the same test with multiple inputs:

java

for (int i = 1; i <= rowCount; i++) {

    String username = ExcelReader.readCell("data.xlsx", "LoginData", i, 0);

    String password = ExcelReader.readCell("data.xlsx", "LoginData", i, 1);

    // Perform login with Selenium

}

Conclusion

Reading and writing Excel files in Selenium with Java is a powerful way to make your tests data-driven, scalable, and easy to maintain. By using the Apache POI library, you can seamlessly integrate Excel with your automation framework, improve test coverage, and reduce manual test data entry. Start small—create a login test driven by Excel—and scale your framework from there. Your automation suite will be more flexible and maintainable as a result.

 Learn Selenium with Java  Course  in Hyderabad

Read More: Page Object Model Design Pattern in Selenium Java

Visit Quality Thought Training Institute Hyderabad
Get Direction