Data-Driven Testing with Excel and Apache POI API in Java

Data-driven testing is a powerful way to make your automated tests more flexible by using external data sources like Excel to drive test cases. In this guide, we’ll learn how to use the Apache POI API to read data from Excel and integrate it with Java test cases, including how to combine it with TestNG’s DataProvider for robust automation testing. Let’s dive in step by step!

Introduction to Data-Driven Testing with Excel

In automated testing, data-driven testing allows you to run the same test with different sets of data. Excel is a common choice for storing test data, especially in legacy frameworks used by many companies. The goal of this tutorial is to teach you how to connect Excel to your Java test cases using the Apache POI API, an open-source library that enables Java to interact with Microsoft Excel files. By the end, you’ll be able to write clean, data-driven test cases that pull data from Excel and even integrate with TestNG for advanced test execution.

What is Apache POI API?

The Apache POI API is a Java library designed to read and write Microsoft Office file formats, including Excel. It’s widely used to connect Excel data to Java test cases. If you’re asked in an interview how to drive data from Excel into your tests, the answer is simple: “Using the Apache POI API.” This library provides methods to access Excel workbooks, sheets, rows, and cells, making it easy to extract data for testing.

Setting Up the Environment

Before we start coding, let’s set up the environment to work with Excel and Apache POI.

Step 1: Create a Maven Project

We’ll use a Maven project to manage dependencies easily. Maven automatically downloads the necessary libraries (JARs) for Apache POI, saving you from manually adding them to your project.

1. In Eclipse:

   • Go to File > New > Maven Project.

   • Select Create a simple project (skip archetype selection) and click Next.

   • Enter a Group ID and an Artifact ID.

   • Click Finish to create the project.

This creates a Maven project with a basic structure, including a pom.xml file where we’ll add dependencies.

2. Add Apache POI Dependencies:

   • Open the pom.xml file.

   • Add the following dependencies for Apache POI and POI-OOXML (check Maven Repository for the latest version):

       <dependencies>
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi</artifactId>
               <version>3.17</version>
           </dependency>
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi-ooxml</artifactId>
               <version>3.17</version>
           </dependency>
       </dependencies>
     

   • Save the file, and Maven will automatically download the required JARs.

3. Create an Excel File:

   • Open Microsoft Excel and create a sample file (e.g., demodata.xlsx).

   • Add test data, such as:

       | Testcases       | Data1 | Data2 | Data3 |
       |-----------------|-------|-------|-------|
       | Login           | abc   | xyz   | 123   |
       | Purchase        | vdvd  | fsfe  | ewre  |
       | Add Profile     | vsf   | etf   | vdge  |
       | Delete Profile  | pqr   | mno   | 456   |
     

   • Save the file in a known location (e.g., Documents/demodata.xlsx).

Now your environment is ready to start coding!

Reading Data from Excel Using Apache POI

Let’s write a Java program to read data from the Excel file. We’ll break this down into a clear strategy, treating it like a game to make it fun and understandable.

Step 1: Access the Excel Workbook

To read data from Excel, we first need to access the workbook. Here’s how:

1. Create a FileInputStream:

   • Use the FileInputStream class to point to the Excel file’s location.

   • Example:

       FileInputStream fis = new FileInputStream("Documents/demodata.xlsx");
     

2. Initialize the Workbook:

   • Use the XSSFWorkbook class from Apache POI to create a workbook object.

   • Pass the FileInputStream to it:

       XSSFWorkbook workbook = new XSSFWorkbook(fis);
     

This gives you access to the entire Excel file.

Step 2: Access the Desired Sheet

Excel files can have multiple sheets, but we want to work with a specific one (e.g., Sheet1).

1. Get the Number of Sheets:

   • Use workbook.getNumberOfSheets() to find out how many sheets are in the workbook.

   • Example:

       int sheets = workbook.getNumberOfSheets();
     

2. Loop Through Sheets to Find the Desired One:

   • Iterate through the sheets to find the one you need (e.g., testdata).

   • Example:

       XSSFSheet sheet = null;
       for (int i = 0; i < sheets; i++) {
           if (workbook.getSheetName(i).equalsIgnoreCase("testdata")) {
               sheet = workbook.getSheetAt(i);
               break;
           }
       }
     

Now, the sheet variable holds the desired sheet.

Step 3: Identify the Testcases Column

To make the code flexible, we need to dynamically find the column containing the test case names (e.g., Testcases).

1. Access the First Row:

   • The first row typically contains headers. Use an iterator to access it:

       Iterator<Row> rows = sheet.iterator();
       Row firstRow = rows.next(); // Gets the first row
     

2. Scan the First Row for the Testcases Column:

   • Iterate through the cells in the first row to find the Testcases column and store its index.

   • Example:

       int column = -1;
       int k = 0;
       Iterator<Cell> cells = firstRow.cellIterator();
       while (cells.hasNext()) {
           Cell value = cells.next();
           if (value.getStringCellValue().equalsIgnoreCase("Testcases")) {
               column = k;
               break;
           }
           k++;
       }
       System.out.println("Testcases column index: " + column);
     

This code scans the first row and identifies that Testcases is at index 0.

Step 4: Find the Desired Test Case Row

Now that we know the Testcases column index, let’s find the row for a specific test case (e.g., Purchase).

1. Iterate Through Rows:

   • Loop through all rows in the sheet, checking the cell in the Testcases column.

   • Example:

       Row targetRow = null;
       Iterator<Row> rows = sheet.iterator();
       while (rows.hasNext()) {
           Row r = rows.next();
           if (r.getCell(column).getStringCellValue().equalsIgnoreCase("Purchase")) {
               targetRow = r;
               break;
           }
       }
     

2. Extract Data from the Target Row:

   • Once the Purchase row is found, iterate through its cells to extract all data.

   • Example:

       ArrayList<String> data = new ArrayList<>();
       Iterator<Cell> cells = targetRow.cellIterator();
       while (cells.hasNext()) {
           Cell cell = cells.next();
           data.add(cell.getStringCellValue());
       }
       System.out.println(data); // Prints: [Purchase, vdvd, fsfe, ewre]
     

This stores the data in an ArrayList for use in your test case.

Step 5: Handle Different Data Types

Excel cells can contain strings, numbers, or other formats. To handle this, check the cell type before extracting its value.

1. Check Cell Type:

   • Use getCellTypeEnum() to determine if a cell is a string or numeric.

   • Example:

       ArrayList<String> data = new ArrayList<>();
       Iterator<Cell> cells = targetRow.cellIterator();
       while (cells.hasNext()) {
           Cell cell = cells.next();
           if (cell.getCellTypeEnum() == CellType.STRING) {
               data.add(cell.getStringCellValue());
           } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
               data.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
           }
       }
     

2. Convert Numeric to String:

   • Use NumberToTextConverter to convert numeric values to strings, ensuring compatibility with the ArrayList<String>.

Step 6: Create a Reusable Method

To make the code reusable, wrap it in a method that takes a test case name and returns its data.

public ArrayList<String> getData(String testcaseName) throws IOException {
    FileInputStream fis = new FileInputStream("Documents/demodata.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = null;
    int sheets = workbook.getNumberOfSheets();
    for (int i = 0; i < sheets; i++) {
        if (workbook.getSheetName(i).equalsIgnoreCase("testdata")) {
            sheet = workbook.getSheetAt(i);
            break;
        }
    }

    int column = -1;
    Iterator<Row> rows = sheet.iterator();
    Row firstRow = rows.next();
    int k = 0;
    Iterator<Cell> cells = firstRow.cellIterator();
    while (cells.hasNext()) {
        Cell value = cells.next();
        if (value.getStringCellValue().equalsIgnoreCase("Testcases")) {
            column = k;
            break;
        }
        k++;
    }

    ArrayList<String> data = new ArrayList<>();
    rows = sheet.iterator();
    while (rows.hasNext()) {
        Row r = rows.next();
        if (r.getCell(column).getStringCellValue().equalsIgnoreCase(testcaseName)) {
            Iterator<Cell> cv = r.cellIterator();
            while (cv.hasNext()) {
                Cell cell = cv.next();
                if (cell.getCellTypeEnum() == CellType.STRING) {
                    data.add(cell.getStringCellValue());
                } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    data.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
                }
            }
            break;
        }
    }
    return data;
}

You can call this method in your test case:

public static void main(String[] args) throws IOException {
    DataDriven d = new DataDriven();
    ArrayList<String> data = d.getData("Add Profile");
    System.out.println(data); // Prints: [Add Profile, vsf, etf, vdge]
}

This method makes your test case clean and reusable, allowing you to fetch data for any test case by passing its name.

Integrating Excel with TestNG DataProvider

Now, let’s take it a step further by integrating Excel with TestNG DataProvider to run tests multiple times with different data sets, generating detailed reports.

Why Integrate Excel with TestNG DataProvider?

• TestNG DataProvider allows you to run a test multiple times with different data sets, treating each run as a separate test in the report.

• Hardcoding data in Java files is not ideal; fetching data from Excel makes tests more maintainable.

• Combining Excel with DataProvider lets you dynamically load data while maintaining TestNG’s reporting capabilities.

Step 1: Understand TestNG DataProvider

A DataProvider in TestNG supplies data to a test method. It returns a multidimensional array (Object[][]), where each inner array represents a set of data for one test execution.

Example:

@DataProvider(name = "driveTest")
public Object[][] getData() {
    Object[][] data = {
        {"hello", "text", "1"},
        {"bye", "message", "143"},
        {"solo", "call", "453"}
    };
    return data;
}

@Test(dataProvider = "driveTest")
public void testCaseData(String greeting, String communication, String id) {
    System.out.println(greeting + " " + communication + " " + id);
}

Running this test produces:

hello text 1
bye message 143
solo call 453
Tests run: 3, Failures: 0

Each array runs the test once, and TestNG reports three separate test runs.

Step 2: Replace Hardcoded Data with Excel Data

Instead of hardcoding data, let’s read it from an Excel file (e.g., ExcelDriven.xlsx):

| Greeting | Communication | ID  |
|----------|---------------|-----|
| hello    | text          | 1   |
| bye      | message       | 143 |
| solo     | call          | 453 |

1. Add Dependencies:

   • Ensure the Apache POI dependencies are in your pom.xml (already added earlier).

   • Add TestNG dependency for Maven portability:

       <dependency>
           <groupId>org.testng</groupId>
           <artifactId>testng</artifactId>
           <version>7.4.0</version>
           <scope>test</scope>
       </dependency>
     

2. Read Excel Data into a Multidimensional Array:

   • Modify the DataProvider to read data from Excel and store it in a multidimensional array.

   • Example:

       @DataProvider(name = "driveTest")
       public Object[][] getData() throws IOException {
           FileInputStream fis = new FileInputStream("Documents/ExcelDriven.xlsx");
           XSSFWorkbook wb = new XSSFWorkbook(fis);
           XSSFSheet sheet = wb.getSheetAt(0);
           int rowCount = sheet.getPhysicalNumberOfRows();
           Row row = sheet.getRow(0);
           int columnCount = row.getLastCellNum();
           Object[][] data = new Object[rowCount - 1][columnCount];
           DataFormatter formatter = new DataFormatter();
     
           for (int i = 0; i < rowCount - 1; i++) {
               row = sheet.getRow(i + 1);
               for (int j = 0; j < columnCount; j++) {
                   Cell cell = row.getCell(j);
                   data[i][j] = formatter.formatCellValue(cell);
               }
           }
           return data;
       }
     
       @Test(dataProvider = "driveTest")
       public void testCaseData(String greeting, String communication, String id) {
           System.out.println(greeting + " " + communication + " " + id);
       }
     

3. Explanation of the Code:

   • FileInputStream and Workbook: Access the Excel file and create a workbook object.

   • Sheet Access: Get the first sheet (getSheetAt(0)).

   • Row and Column Count: Use getPhysicalNumberOfRows() for rows and getLastCellNum() for columns.

   • Multidimensional Array: Create an array with dimensions [rowCount - 1][columnCount] to skip the header row.

   • DataFormatter: Use DataFormatter to convert all cell values to strings, handling different data types.

   • Nested Loops: Iterate through rows and columns to populate the array.

   • Return Data: Return the multidimensional array to the DataProvider.

Running this test produces the same output as the hardcoded version, but the data is now dynamically loaded from Excel:

hello text 1
bye message 143
solo call 453
Tests run: 3, Failures: 0

Benefits of This Approach

• No Hardcoding: Data is stored in Excel, making it easy to update without changing code.

• Detailed Reporting: TestNG DataProvider treats each data set as a separate test, providing granular reports.

• Flexibility: The code handles different data types (strings, numbers) and can be adapted to various Excel formats.

• Reusability: The getData method and DataProvider can be reused across multiple test cases.

Tips for Beginners

1. Practice Step-by-Step:

   • Set up the Maven project and dependencies.

   • Create a simple Excel file and try reading one row.

   • Gradually build the full logic, testing each part (e.g., accessing sheets, rows, cells).

2. Understand Iterators:

   • Apache POI uses iterators to traverse sheets (sheet.iterator()), rows (row.cellIterator()), and cells. Think of it as moving step-by-step through a grid.

3. Debugging:

   • Run small portions of the code (e.g., print the column index or row data) to ensure each step works.

   • Use System.out.println to verify outputs at each stage.

4. Take Notes:

   • Write down the logic (e.g., workbook → sheet → rows → cells) on paper to visualize the flow.

   • Practice with different Excel structures to understand how to adapt the code.

5. Experiment:

   • Try changing the Excel file (e.g., move the Testcases column or add numeric data) and see how the code adapts.

   • Test with different test case names to ensure the getData method is generic.

Conclusion

By following this guide, you’ve learned how to use the Apache POI API to read data from Excel and integrate it with Java test cases. You’ve also seen how to combine this with TestNG DataProvider to run tests multiple times with different data sets, producing detailed reports. This approach is widely used in automation testing for Selenium, Appium, or REST-assured frameworks, making it a valuable skill for any automation engineer.
For the complete code, refer to the examples provided in this blog. Happy testing!