34-Data Driven Tests | Apache POI

1. Introduction to Data-Driven Testing

Data-Driven Testing (DDT) is a testing methodology where test scripts read test data from external sources (like Excel, CSV, XML, or databases) and execute test cases with multiple data sets. This approach enhances test coverage, reduces code duplication, and makes maintenance easier.

Advantages of Data-Driven Testing:

• Reusability: The same test logic can be used with different data sets.
• Scalability: Easily add or modify test data without altering test scripts.
• Maintenance: Centralized test data management simplifies updates.

Why Apache POI?

Apache POI is a robust Java library that enables reading and writing Microsoft Office documents, including Excel files. It provides a comprehensive API for interacting with Excel sheets, making it ideal for managing test data in DDT.

2. Setting Up Apache POI in Your Maven Project

To utilize Apache POI for Excel operations, you need to add the necessary dependencies to your Maven project.

2.1. Adding Apache POI Dependencies

1. Open pom.xml:
   • Locate and open the pom.xml file in your project’s root directory.
2. Add Apache POI Dependencies:
   • Insert the following dependencies within the <dependencies> tag:

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

   • Notes:
     • Version: Ensure you’re using a stable and compatible version of Apache POI. As of now, 5.2.3 is a stable release. You can verify the latest versions here.
     • Scope: These dependencies are required during both compile and runtime phases.
3. Update Maven Project:
   • After adding the dependencies, right-click on the project in Eclipse.
   • Navigate to Maven > Update Project.....
   • Ensure your project is selected and click OK.
   • Maven will download and integrate the specified Apache POI dependencies into your project.

2.2. Verify Dependency Integration

Ensure that the dependencies are correctly added by checking the Maven Dependencies section in your project explorer. You should see poi-5.2.3.jar and poi-ooxml-5.2.3.jar listed.

3. Creating Excel Test Data

Before implementing data-driven tests, you need to create an Excel file (.xlsx) that contains the test data.

3.1. Create TestData.xlsx

1. Navigate to Resources Folder:
   • Go to src/test/resources.
2. Create TestData.xlsx:
   • Right-click on resources > New > File.
   • Name the file TestData.xlsx and click Finish.
3. Define Test Data Structure:
   • Open TestData.xlsx using Microsoft Excel or any compatible spreadsheet editor.
   • Create sheets corresponding to different test scenarios. For example, a sheet named AccountRegistration.
   • Define column headers for your test data. For the AccountRegistration test case, you might have:

   FirstName	LastName	Email	Telephone	Password
   John	Doe	johndoe@mail.com	1234567890	Pass@1234
   Jane	Smith	janesmith@mail.com	0987654321	Pass@5678

   • Populate the sheet with multiple rows of test data to cover various scenarios.
4. Save the Excel File:
   • After entering the test data, save and close the Excel file.
5. Best Practices:
   • Consistent Naming: Use separate sheets for different test scenarios.
   • Data Validation: Ensure data types are consistent (e.g., emails are correctly formatted).
   • Avoid Duplicates: Ensure unique entries where necessary to prevent conflicts.

4. Developing a Utility Class for Excel Operations

To interact with Excel files, we’ll create a utility class that handles reading data from TestData.xlsx using Apache POI.

4.1. Create ExcelUtil.java

1. Navigate to Utilities Package:
   • Go to src/test/java/com/opencart/utilities.
2. Create ExcelUtil Class:
   • Right-click on utilities > New > Class.
   • Name the class ExcelUtil and click Finish.
3. Implement ExcelUtil Class:

   package com.opencart.utilities;
   
   import org.apache.poi.ss.usermodel.Cell;
   import org.apache.poi.ss.usermodel.Row;
   import org.apache.poi.ss.usermodel.Sheet;
   import org.apache.poi.ss.usermodel.Workbook;
   import org.apache.poi.xssf.usermodel.XSSFWorkbook;
   
   import java.io.FileInputStream;
   import java.io.IOException;
   import java.util.Iterator;
   import java.util.List;
   
   /**
    * Utility class for handling Excel operations using Apache POI.
    */
   public class ExcelUtil {
       private String filePath;
       private Workbook workbook;
   
       /**
        * Constructor to initialize ExcelUtil with the path to the Excel file.
        *
        * @param filePath Path to the Excel file.
        */
       public ExcelUtil(String filePath) {
           this.filePath = filePath;
           try {
               FileInputStream fis = new FileInputStream(this.filePath);
               this.workbook = new XSSFWorkbook(fis);
               fis.close();
           } catch (IOException e) {
               e.printStackTrace();
               throw new RuntimeException("Failed to load Excel file: " + filePath);
           }
       }
   
       /**
        * Retrieves data from a specific sheet in the Excel file.
        *
        * @param sheetName Name of the sheet.
        * @return A two-dimensional Object array containing the data.
        */
       public Object[][] getData(String sheetName) {
           Sheet sheet = workbook.getSheet(sheetName);
           if (sheet == null) {
               throw new RuntimeException("Sheet: " + sheetName + " does not exist in " + filePath);
           }
   
           int rowCount = sheet.getPhysicalNumberOfRows();
           Row headerRow = sheet.getRow(0);
           int colCount = headerRow.getLastCellNum();
   
           Object[][] data = new Object[rowCount - 1][colCount];
   
           for (int i = 1; i < rowCount; i++) { // Start from 1 to skip header
               Row row = sheet.getRow(i);
               for (int j = 0; j < colCount; j++) {
                   Cell cell = row.getCell(j);
                   data[i - 1][j] = getCellValue(cell);
               }
           }
   
           return data;
       }
   
       /**
        * Retrieves the value of a cell in a readable format.
        *
        * @param cell The cell to retrieve the value from.
        * @return The cell value as a String.
        */
       private String getCellValue(Cell cell) {
           if (cell == null) {
               return "";
           }
   
           switch (cell.getCellType()) {
               case STRING:
                   return cell.getStringCellValue();
               case BOOLEAN:
                   return String.valueOf(cell.getBooleanCellValue());
               case NUMERIC:
                   if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                       return cell.getDateCellValue().toString();
                   } else {
                       return String.valueOf((long) cell.getNumericCellValue());
                   }
               case FORMULA:
                   return cell.getCellFormula();
               case BLANK:
                   return "";
               default:
                   return "";
           }
       }
   
       /**
        * Closes the workbook to free resources.
        */
       public void closeWorkbook() {
           try {
               if (workbook != null) {
                   workbook.close();
               }
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   }
                   

   • Explanation:
     • Constructor: Initializes the ExcelUtil with the path to the Excel file. It loads the workbook using FileInputStream and XSSFWorkbook.
     • getData Method: Accepts the sheet name and retrieves all data from that sheet. It skips the header row (assumed to be the first row) and returns a two-dimensional Object[][] array suitable for TestNG’s @DataProvider.
     • getCellValue Method: Converts cell values to String for uniformity. It handles different cell types (STRING, BOOLEAN, NUMERIC, FORMULA, BLANK).
     • closeWorkbook Method: Closes the workbook to free system resources.
4. Utility Class for Reading Excel Data:To facilitate easy data retrieval, we’ll implement a singleton pattern for ExcelUtil. However, for simplicity, the above implementation suffices for most use cases.

5. Implementing Data Providers in TestNG

TestNG’s @DataProvider annotation allows you to pass multiple sets of data to your test methods. We’ll leverage this to feed data from our Excel file into our test cases.

5.1. Create DataProviderUtil.java

1. Navigate to Utilities Package:
   • Go to src/test/java/com/opencart/utilities.
2. Create DataProviderUtil Class:
   • Right-click on utilities > New > Class.
   • Name the class DataProviderUtil and click Finish.
3. Implement DataProviderUtil Class:

   package com.opencart.utilities;
   
   import org.testng.annotations.DataProvider;
   
   /**
    * Utility class for providing data to TestNG test cases.
    */
   public class DataProviderUtil {
   
       /**
        * DataProvider for Account Registration Test Case.
        *
        * @return Two-dimensional Object array containing test data.
        */
       @DataProvider(name = "accountRegistrationData")
       public static Object[][] getAccountRegistrationData() {
           String excelPath = "src/test/resources/TestData.xlsx";
           ExcelUtil excel = new ExcelUtil(excelPath);
           Object[][] data = excel.getData("AccountRegistration");
           excel.closeWorkbook();
           return data;
       }
   
       // Add more DataProviders for other test cases as needed
   }
                   

   • Explanation:
     • @DataProvider Annotation: Defines a data provider named accountRegistrationData.
     • Method Logic:
       • Initializes ExcelUtil with the path to TestData.xlsx.
       • Retrieves data from the AccountRegistration sheet.
       • Closes the workbook to free resources.
       • Returns the data as a two-dimensional Object[][] array.
   • Extensibility: You can add more data providers for different test cases by following the same pattern.

6. Modifying Test Cases to Use Data from Excel

Now, we’ll modify the existing test case (AccountRegistrationTest) to utilize the data provided by DataProviderUtil.

6.1. Update AccountRegistrationTest.java

1. Open AccountRegistrationTest.java:
   • Navigate to src/test/java/com/opencart/tests/AccountRegistrationTest.java.
2. Import DataProviderUtil:
   • Add the following import at the top of the file:

   import com.opencart.utilities.DataProviderUtil;
                   

3. Modify the Test Method to Use Data Provider:
   1. Annotate the Test Method:
      • Use the @Test annotation with the dataProvider and dataProviderClass attributes.
   2. Update the Method Signature:
      • Add parameters to the test method corresponding to the columns in the Excel sheet.
   3. Implement the Test Logic:
      • Use the parameters directly in your test steps.
      • Enhance logging to include the specific data set being used.

   package com.opencart.tests;
   
   import com.opencart.pages.AccountRegistrationPage;
   import com.opencart.pages.HomePage;
   import com.opencart.utilities.DataProviderUtil;
   import org.testng.Assert;
   import org.testng.annotations.Test;
   
   /**
    * Test Class for Account Registration.
    */
   public class AccountRegistrationTest extends BaseTest {
   
       @Test(dataProvider = "accountRegistrationData", dataProviderClass = DataProviderUtil.class)
       public void tc001_accountRegistrationTest(String firstName, String lastName, String email, String telephone, String password) {
           try {
               logger.info("Starting Account Registration Test Case: tc001 with data - " +
                           "FirstName: " + firstName + ", LastName: " + lastName + ", Email: " + email +
                           ", Telephone: " + telephone + ", Password: " + password);
   
               // Create objects for Page Object Classes
               HomePage homePage = new HomePage(driver);
               AccountRegistrationPage registrationPage = new AccountRegistrationPage(driver);
   
               // Perform actions on Home Page
               logger.info("Clicking on 'My Account' link.");
               homePage.clickMyAccount();
   
               logger.info("Clicking on 'Register' link.");
               homePage.clickRegister();
   
               // Perform actions on Registration Page
               logger.info("Registering a new user.");
               registrationPage.registerNewUser(firstName, lastName, email, telephone, password);
   
               // Validation
               String actualMessage = registrationPage.getConfirmationMessage();
               String expectedMessage = "Your Account Has Been Created!";
               logger.info("Validating the confirmation message.");
   
               Assert.assertEquals(actualMessage, expectedMessage, "Account registration failed!");
   
               logger.info("Account Registration Test Case: tc001 Passed Successfully.");
   
           } catch (Exception e) {
               logger.error("Account Registration Test Case: tc001 Failed due to exception - " + e.getMessage());
               Assert.fail("Account Registration Test Case: tc001 Failed due to exception.");
           }
       }
   }
                   

   • Explanation:
     • @Test Annotation: Specifies the data provider (accountRegistrationData) and the class containing it (DataProviderUtil).
     • Method Parameters: The test method now accepts parameters corresponding to the columns in the Excel sheet.
     • Dynamic Data Usage: Test data (firstName, lastName, email, telephone, password) are passed directly from the Excel file.
     • Logging Enhancements: Logs the specific data set being used for each test iteration, aiding in debugging and traceability.
   • Note: Ensure that the parameters in the test method match the columns in the Excel sheet in both order and data type.

7. Executing Data-Driven Tests

1. Run testng.xml for Data-Driven Tests:
   • Ensure testng.xml is Configured Correctly:
     • Your testng.xml should already be set up for cross-browser and parallel testing. It will now also handle data-driven tests.
   • Execute the Test Suite:
     • Right-click on testng.xml > Run As > TestNG Suite.
2. Monitor Test Execution:
   • TestNG will initiate parallel executions across the specified browsers.
   • Each test case will run multiple times based on the number of data sets in TestData.xlsx.
3. Verify Log Outputs:
   • • Navigate to the logs folder at the project root.
     • Open automation.log to review detailed logs for each test iteration.
     • Sample Log Entries for Data-Driven Test:

   2025-01-04 10:30:15 [TestNG-method=tc001_accountRegistrationTest, TestNG-class=AccountRegistrationTest, TestNG-instance=AccountRegistrationTest@1a2b3c] INFO  com.opencart.tests.AccountRegistrationTest - Starting Account Registration Test Case: tc001 with data - FirstName: JOHN, LastName: DOE, Email: johndoe@mail.com, Telephone: 1234567890, Password: Pass@1234
   2025-01-04 10:30:16 [TestNG-method=tc001_accountRegistrationTest, TestNG-class=AccountRegistrationTest, TestNG-instance=AccountRegistrationTest@1a2b3c] INFO  com.opencart.tests.AccountRegistrationTest - Clicking on 'My Account' link.
   2025-01-04 10:30:17 [TestNG-method=tc001_accountRegistrationTest, TestNG-class=AccountRegistrationTest, TestNG-instance=AccountRegistrationTest@1a2b3c] INFO  com.opencart.tests.AccountRegistrationTest - Clicking on 'Register' link.
   2025-01-04 10:30:18 [TestNG-method=tc001_accountRegistrationTest, TestNG-class=AccountRegistrationTest, TestNG-instance=AccountRegistrationTest@1a2b3c] INFO  com.opencart.tests.AccountRegistrationTest - Registering a new user.
   2025-01-04 10:30:19 [TestNG-method=tc001_accountRegistrationTest, TestNG-class=AccountRegistrationTest, TestNG-instance=AccountRegistrationTest@1a2b3c] INFO  com.opencart.tests.AccountRegistrationTest - Validating the confirmation message.
   2025-01-04 10:30:20 [TestNG-method=tc001_accountRegistrationTest, TestNG-class=AccountRegistrationTest, TestNG-instance=AccountRegistrationTest@1a2b3c] INFO  com.opencart.tests.AccountRegistrationTest - Account Registration Test Case: tc001 Passed Successfully.
           

4. Analyze Test Results:
   • TestNG Reports:
     • After execution, TestNG generates reports in the test-output folder.
     • Open index.html to view the detailed test execution report, including passed and failed test cases.
   • Log Verification:
     • Review automation.log to ensure that all test iterations have corresponding log entries.
     • Verify that logs accurately reflect the test steps and outcomes.
   • Handling Failures:
     • In case of test failures, consult the logs to identify the cause.
     • For example, if a test fails due to an assertion error, the log will capture the error message and the data set used.

8. Best Practices

Implementing Data-Driven Testing with Apache POI can significantly enhance your automation framework’s efficiency. Here are some best practices to follow:

1. Maintain Clear Excel Structure:
   • Use separate sheets for different test scenarios.
   • Clearly define column headers and ensure data consistency.
2. Avoid Hardcoding Paths:
   • Use relative paths for Excel files to ensure portability across different environments.
   • Example: "src/test/resources/TestData.xlsx" instead of absolute paths.
3. Handle Exceptions Gracefully:
   • Ensure that your utility classes handle exceptions like missing files or incorrect sheet names.
   • Provide meaningful error messages to facilitate debugging.
4. Optimize Data Retrieval:
   • Load the Excel file once per test run to improve performance.
   • Reuse the ExcelUtil instance if possible.
5. Secure Sensitive Data:
   • Avoid storing sensitive information (like passwords) in plain text within Excel files.
   • Consider using encrypted properties or secure storage mechanisms.
6. Validate Test Data:
   • Ensure that the data in Excel files is accurate and formatted correctly to prevent test failures due to invalid data.
7. Integrate with Logging:
   • Log the data sets being used for each test iteration to maintain transparency and ease troubleshooting.
8. Scalability:
   • Design your utility classes to handle large datasets efficiently.
   • Implement pagination or batch processing if dealing with extensive data.
9. Consistent Naming Conventions:
   • Use descriptive names for sheets, columns, and data providers to enhance readability and maintainability.
10. Version Control:
    • Track changes to your Excel test data and utility classes using version control systems like Git to manage updates and collaborate effectively.

9. Conclusion

Integrating Data-Driven Testing using Apache POI elevates your automation framework by enabling efficient and scalable test executions with multiple data sets. This approach not only enhances test coverage but also simplifies test maintenance and scalability.

Key Takeaways:

• Data-Driven Testing: Enhances test flexibility by allowing the same test case to run with various data sets.
• Apache POI Integration: Facilitates seamless interaction with Excel files for managing test data.
• TestNG Data Providers: Efficiently supply test data to your test cases, enabling parallel and cross-browser executions.

Next Steps:

1. Implement Additional Test Cases:
   • Add more test cases (e.g., Login, Search Product) following the established framework structure.
   • Create corresponding sheets in TestData.xlsx for each test case.
2. Enhance Excel Utility:
   • Add functionalities like writing back results to Excel, handling multiple data types, or supporting other file formats.
3. Integrate with Reporting Tools:
   • Combine data-driven test results with reporting tools like ExtentReports for comprehensive and visually appealing reports.
4. Continuous Integration (CI):
   • Incorporate your data-driven tests into CI pipelines using tools like Jenkins to automate test executions upon code changes.
5. Advanced Data Management:
   • Explore other data sources like databases, CSV files, or JSON for test data management, depending on your project’s requirements.

Happy Testing! 🚀