5 Ways to Load Excel Sheets in TestNG DataProvider

The DataProvider feature in TestNG, an advanced framework for testing, is a powerful way to parameterize test methods, enabling automated tests to be executed with varied data sets. This functionality is particularly useful when automating test cases for applications that deal with spreadsheets, such as Excel. Here are five effective methods to load Excel sheets into your TestNG DataProvider:

Using Apache POI

Apache POI is a comprehensive API for manipulating Microsoft Office formats, including Excel. Here’s how you can use it to load Excel sheets:

• Setup Apache POI Dependency: Add the necessary dependencies to your project's build file. For Maven, your pom.xml might include:

    org.apache.poi
    poi-ooxml
    5.2.0

• Read Excel File: Write code to open, read, and navigate through the Excel file:

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

public Object[][] loadFromExcel(String filePath) {
    Workbook workbook = WorkbookFactory.create(new File(filePath));
    Sheet sheet = workbook.getSheetAt(0);
    int rowCount = sheet.getPhysicalNumberOfRows();
    int colCount = sheet.getRow(0).getPhysicalNumberOfCells();
    Object[][] data = new Object[rowCount - 1][colCount];

    for (int i = 1; i < rowCount; i++) {
        Row row = sheet.getRow(i);
        for (int j = 0; j < colCount; j++) {
            Cell cell = row.getCell(j);
            data[i-1][j] = cell.getStringCellValue();
        }
    }
    return data;
}

📄 Note: Ensure the Excel file is well-structured with headers in the first row and data below.

JExcelAPI

JExcelAPI is another library for reading and writing Excel files. Here's how to use it:

• Include JExcelAPI Dependency: Add this dependency to your project:

    net.sourceforge.jexcelapi
    jxl
    2.6.12

• Read Excel File:

import jxl.*;

public Object[][] loadExcelData(String filePath) {
    Workbook workbook = Workbook.getWorkbook(new File(filePath));
    Sheet sheet = workbook.getSheet(0);
    int rows = sheet.getRows();
    int columns = sheet.getColumns();
    Object[][] data = new Object[rows-1][columns];

    for (int i = 1; i < rows; i++) {
        for (int j = 0; j < columns; j++) {
            Cell cell = sheet.getCell(j, i);
            data[i-1][j] = cell.getContents();
        }
    }
    workbook.close();
    return data;
}

Using Excel XML

Excel files saved in XML format can be read directly by TestNG’s DataProvider. Here’s how:

• Save Excel as XML: Export your Excel data to an XML file.
• Parse XML Data: Use Java’s built-in XML parsers like JAXB or DOM Parser.

JDBC-ODBC Bridge

This approach connects Excel files through ODBC, treating the Excel as a database:

• Enable Microsoft Excel Driver: Make sure your system has the driver installed.
• JDBC Code: Connect to Excel file via JDBC:

import java.sql.*;

public Object[][] loadViaJdbc(String filePath) {
    Connection conn = null;
    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String connURL = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx)};DBQ="+filePath+";";
        conn = DriverManager.getConnection(connURL);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM [Sheet1$]");
        
        // Build data array
        return buildDataArray(rs); // Helper method to convert ResultSet to Object array
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try { conn.close(); } catch (Exception e) { /* Ignored */ }
    }
    return new Object[][]{}; // Empty in case of exception
}

💡 Note: The JDBC-ODBC Bridge is deprecated in Java, so use cautiously or look for alternatives like JDBC drivers for Excel.

TestNG with Custom Excel Reader

Create a custom reader class for loading data from Excel:

• Custom Reader Class: Implement your logic for reading Excel files.

public class ExcelDataReader {
    public Object[][] loadData(String filePath) {
        // Your custom logic to read Excel file
        return new Object[][]{{"Sample", "Data"}};
    }
}

Overall, integrating Excel data with TestNG’s DataProvider allows for dynamic test data loading, which is essential for effective automation testing. Here’s how you can summarize the key methods:

Wrapping Up: Each of these methods provides different advantages:

• Apache POI is versatile, supporting both .xls and .xlsx formats.
• JExcelAPI provides an alternative but with less support for .xlsx.
• XML parsing is straightforward but requires file conversion.
• The JDBC-ODBC Bridge method requires system setup but can be powerful when used properly.
• A custom Excel reader gives you the flexibility to tailor the reading process to your needs.

By utilizing any of these techniques, your automation framework can be enhanced with the capability to seamlessly incorporate Excel data, allowing for efficient, scalable, and maintainable test scripts.