Certainly! Based on the extensive transcript you’ve provided, we’ll continue building a comprehensive guide on Data-Driven Testing (DDT) Using Excel and Properties Files with Selenium WebDriver in Java. This section will delve into the practical implementation of DDT, including creating utility files, handling Excel operations, working with properties files, and executing test cases using Selenium WebDriver.

Table of Contents

1. Introduction to Data-Driven Testing

Data-Driven Testing (DDT) is a testing methodology where test scripts are executed multiple times with different sets of input data. This approach enhances test coverage and efficiency by separating test logic from test data. In Selenium WebDriver with Java, Excel files are commonly used to store and manage test data.

1.1. What is Data-Driven Testing?

Data-Driven Testing involves the following steps:

  1. Preparing Test Data: Organize test data in external sources like Excel files.
  2. Reading Test Data: Use automation scripts to read data from these sources.
  3. Executing Tests: Pass the read data to the application under test.
  4. Validating Results: Verify the application’s behavior based on the input data.

Benefits of DDT:

  • Reusability: Test scripts can be reused with different data sets.
  • Scalability: Easily expand test coverage by adding more data without modifying scripts.
  • Maintainability: Simplifies maintenance by separating data from test logic.

2. Understanding Excel File Structure

Before diving into automation, it’s essential to understand the structure of Excel files. Excel organizes data hierarchically as follows:


Excel File (.xls or .xlsx)
│
└── Workbook
    │
    ├── Sheet (Worksheet)
    │   │
    │   ├── Row
    │   │   │
    │   │   └── Cell
    │   │
    │   └── ...
    │
    └── ...

Key Components:

  • Excel File: The entire document containing one or more workbooks.
  • Workbook: A collection of one or more sheets.
  • Sheet (Worksheet): A single tab within a workbook, containing rows and cells.
  • Row: A horizontal grouping of cells.
  • Cell: The intersection of a row and a column, holding individual data points.

Hierarchy Flow:

Excel File → Workbook → Sheet → Row → Cell

3. Setting Up Apache POI with Maven

Apache POI is a powerful Java library for interacting with Microsoft Office documents, including Excel files. Since Selenium WebDriver does not natively support Excel operations, integrating Apache POI is essential for Data-Driven Testing.

3.1. Integrating Apache POI with Maven

If your project uses Maven for dependency management, follow these steps to add Apache POI:

  1. Locate the Maven Repository:Visit the Maven Repository to find the necessary Apache POI dependencies.
  2. Add Dependencies to pom.xml:Include the following dependencies in your project’s pom.xml file: 
    <!-- Apache POI Core -->

    org.apache.poi
    poi
    5.2.5 <!-- Use the latest stable version -->


<!-- Apache POI for OOXML (Required for .xlsx files) -->

    org.apache.poi
    poi-ooxml
    5.2.5 <!-- Use the latest stable version -->


<!-- Apache Log4j for Logging (Optional but recommended) -->

    org.apache.logging.log4j
    log4j-core
    2.17.1 <!-- Use the latest stable version -->

    Note:

    • Ensure that the versions match the latest stable releases to avoid compatibility issues.
    • Adding Log4j is optional but helps in managing logging effectively.
  3. Refresh Maven Project:After adding the dependencies, refresh your Maven project to download and include the new libraries.

Understanding Dependencies:

  • poi: Core library for working with Excel files.
  • poi-ooxml: Extends POI to handle OOXML formats like .xlsx.
  • log4j-core: Facilitates logging, helping to manage and debug your automation scripts.

4. Reading Data from Excel Files

Reading data from Excel files involves navigating through the Excel hierarchy to access specific cells. Here’s a step-by-step guide with sample code.

4.1. Prerequisites:

  • An existing Excel file (data.xlsx) with test data.
  • Properly configured Apache POI dependencies in your Maven project.

4.2. Step-by-Step Implementation

  1. Open the Excel File in Reading Mode: 
    FileInputStream fileInputStream = new FileInputStream("path_to_your_excel_file/data.xlsx");

    Explanation:

    • FileInputStream from java.io is used to read the Excel file.
    • Replace "path_to_your_excel_file/data.xlsx" with the actual path to your Excel file.
  2. Create a Workbook Instance: 
    XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

    Explanation:

    • XSSFWorkbook represents the entire workbook.
    • It is initialized with the FileInputStream to access the Excel file’s contents.
  3. Access a Specific Sheet: 
    XSSFSheet sheet = workbook.getSheet("Sheet1"); // Replace "Sheet1" with your sheet name

    Explanation:

    • getSheet(String name) retrieves the sheet by its name.
    • Alternatively, you can use getSheetAt(int index) to access a sheet by its index (starting from 0).
  4. Determine the Number of Rows and Cells: 
    int totalRows = sheet.getLastRowNum(); // Returns the index of the last row
Row firstRow = sheet.getRow(0); // Assuming the first row is the header
int totalCells = firstRow.getLastCellNum(); // Returns the number of cells in the first row

    Explanation:

    • getLastRowNum() returns the index of the last row (0-based index).
    • getRow(int rownum) retrieves a specific row.
    • getLastCellNum() returns the number of cells in the row.
  5. Iterate Through Rows and Cells to Read Data: 
    for (int r = 1; r <= totalRows; r++) { // Start from 1 if row 0 is header
    Row currentRow = sheet.getRow(r);
    for (int c = 0; c < totalCells; c++) {
        Cell currentCell = currentRow.getCell(c);
        String cellData = currentCell.toString(); // Converts cell data to String
        System.out.print(cellData + "\t");
    }
    System.out.println();
}

    Explanation:

    • Outer Loop (for (int r = 1; r <= totalRows; r++)): Iterates through each row, starting from 1 to skip headers.
    • Inner Loop (for (int c = 0; c < totalCells; c++)): Iterates through each cell in the current row.
    • currentCell.toString(): Converts the cell’s content to a string, regardless of its original data type.
    • Output Formatting: Uses tab (\t) and newline (\n) for readable console output.
  6. Closing Resources: 
    workbook.close();
fileInputStream.close();

    Explanation:

    • Always close Workbook and FileInputStream to free up resources and avoid memory leaks.

4.3. Sample Code: Reading Excel Data

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelDataReader {
    public static void main(String[] args) {
        String filePath = System.getProperty("user.dir") + "/testdata/data.xlsx"; // Dynamic path

        try {
            // 1. Open the Excel file
            FileInputStream fileInputStream = new FileInputStream(filePath);

            // 2. Create Workbook instance
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

            // 3. Access the sheet
            XSSFSheet sheet = workbook.getSheet("Sheet1"); // Replace with your sheet name

            // 4. Get total number of rows and cells
            int totalRows = sheet.getLastRowNum();
            Row firstRow = sheet.getRow(0);
            int totalCells = firstRow.getLastCellNum();

            System.out.println("Total Rows: " + totalRows);
            System.out.println("Total Cells: " + totalCells);

            // 5. Iterate through rows and cells
            for (int r = 1; r <= totalRows; r++) { // Start from 1 to skip header
                Row currentRow = sheet.getRow(r);
                for (int c = 0; c < totalCells; c++) {
                    Cell currentCell = currentRow.getCell(c);
                    String cellData = currentCell.toString();
                    System.out.print(cellData + "\t");
                }
                System.out.println();
            }

            // 6. Close resources
            workbook.close();
            fileInputStream.close();

        } catch (IOException e) {
            System.out.println("An error occurred while reading the Excel file.");
            e.printStackTrace();
        }
    }
}

Output Example:


Total Rows: 5
Total Cells: 4
Java    2023-10-10  200    New York
Python  2023-10-11  300    Los Angeles
C#      2023-10-12  400    Chicago
...

4.4 Handling Common Issues:

  • Log4j Errors:If you encounter errors related to Log4j (e.g., “Could not find a valid logging implementation”), ensure that you’ve added the necessary Log4j dependencies in your pom.xml. This step is optional but recommended for effective logging.
  • Indexing Errors:Remember that both row and cell indices start from 0. Ensure your loops reflect this to avoid NullPointerException or IndexOutOfBoundsException.

5. Writing Data to Excel Files

Writing data to Excel involves creating new workbooks, sheets, rows, and cells, and populating them with data. Here’s how you can achieve this using Apache POI.

5.1. Prerequisites:

  • No existing Excel file is required; the script can create a new one.
  • Properly configured Apache POI dependencies in your Maven project.

5.2. Step-by-Step Implementation

  1. Create a New Excel File in Writing Mode: 
    FileOutputStream fileOutputStream = new FileOutputStream("path_to_your_excel_file/newdata.xlsx");

    Explanation:

    • FileOutputStream from java.io is used to write data to a new Excel file.
    • If the file does not exist, it will be created automatically.
  2. Create a Workbook Instance: 
    XSSFWorkbook workbook = new XSSFWorkbook();

    Explanation:

    • Initializes a new workbook where you’ll create sheets, rows, and cells.
  3. Create a Sheet: 
    XSSFSheet sheet = workbook.createSheet("Data");

    Explanation:

    • createSheet(String name) creates a new sheet with the specified name.
  4. Create Rows and Cells with Data: 
    // Creating the first row (Row 0)
Row row0 = sheet.createRow(0);
Cell cell0_0 = row0.createCell(0);
cell0_0.setCellValue("Java");

Cell cell0_1 = row0.createCell(1);
cell0_1.setCellValue("2023-10-10");

Cell cell0_2 = row0.createCell(2);
cell0_2.setCellValue(200);

Cell cell0_3 = row0.createCell(3);
cell0_3.setCellValue("New York");

// Repeat for additional rows as needed

    Explanation:

    • createRow(int rownum) creates a new row at the specified index.
    • createCell(int cellnum) creates a new cell in the row at the specified index.
    • setCellValue() assigns a value to the cell. It is overloaded to accept different data types (e.g., String, int, double).
  5. Writing Data Dynamically Using Loops:To write dynamic data provided by the user at runtime, utilize nested loops. 
    Scanner scanner = new Scanner(System.in);
System.out.print("Enter number of rows: ");
int numberOfRows = scanner.nextInt();

System.out.print("Enter number of cells per row: ");
int numberOfCells = scanner.nextInt();

scanner.nextLine(); // Consume newline

for (int r = 0; r < numberOfRows; r++) {
    Row currentRow = sheet.createRow(r);
    for (int c = 0; c < numberOfCells; c++) {
        Cell currentCell = currentRow.createCell(c);
        System.out.print("Enter data for Row " + r + ", Cell " + c + ": ");
        String cellData = scanner.nextLine();
        currentCell.setCellValue(cellData);
    }
}

    Explanation:

    • Outer Loop (for (int r = 0; r < numberOfRows; r++)): Creates rows based on user input.
    • Inner Loop (for (int c = 0; c < numberOfCells; c++)): Creates cells within each row and populates them with user-provided data.
    • Scanner: Captures user input from the console.
  6. Write Workbook to File and Close Resources: 
    workbook.write(fileOutputStream);
workbook.close();
fileOutputStream.close();
scanner.close();

    Explanation:

    • workbook.write(OutputStream out): Writes the workbook data to the specified output stream.
    • Always close Workbook, FileOutputStream, and Scanner objects to free up resources.

5.3. Sample Code: Writing Excel Data

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;

public class ExcelDataWriter {
    public static void main(String[] args) {
        String filePath = System.getProperty("user.dir") + "/testdata/newdata.xlsx"; // Dynamic path

        try {
            // 1. Create a new Excel file
            FileOutputStream fileOutputStream = new FileOutputStream(filePath);

            // 2. Create Workbook instance
            XSSFWorkbook workbook = new XSSFWorkbook();

            // 3. Create a sheet named "Data"
            XSSFSheet sheet = workbook.createSheet("Data");

            // 4. Create Rows and Cells with Static Data
            Row row0 = sheet.createRow(0);
            Cell cell0_0 = row0.createCell(0);
            cell0_0.setCellValue("Java");

            Cell cell0_1 = row0.createCell(1);
            cell0_1.setCellValue("2023-10-10");

            Cell cell0_2 = row0.createCell(2);
            cell0_2.setCellValue(200);

            Cell cell0_3 = row0.createCell(3);
            cell0_3.setCellValue("New York");

            // 5. Create Rows and Cells with Dynamic Data
            Scanner scanner = new Scanner(System.in);
            System.out.print("Enter number of rows to create: ");
            int numberOfRows = scanner.nextInt();

            System.out.print("Enter number of cells per row: ");
            int numberOfCells = scanner.nextInt();
            scanner.nextLine(); // Consume newline

            for (int r = 1; r <= numberOfRows; r++) { // Start from 1 to continue after static data
                Row currentRow = sheet.createRow(r);
                for (int c = 0; c < numberOfCells; c++) {
                    Cell currentCell = currentRow.createCell(c);
                    System.out.print("Enter data for Row " + r + ", Cell " + c + ": ");
                    String cellData = scanner.nextLine();
                    currentCell.setCellValue(cellData);
                }
            }

            // 6. Write the workbook to the file
            workbook.write(fileOutputStream);

            // 7. Close resources
            workbook.close();
            fileOutputStream.close();
            scanner.close();

            System.out.println("Excel file written successfully!");

        } catch (IOException e) {
            System.out.println("An error occurred while writing to the Excel file.");
            e.printStackTrace();
        }
    }
}

Sample Interaction:


Enter number of rows to create: 2
Enter number of cells per row: 3
Enter data for Row 1, Cell 0: Python
Enter data for Row 1, Cell 1: 2023-10-11
Enter data for Row 1, Cell 2: 300
Enter data for Row 2, Cell 0: C#
Enter data for Row 2, Cell 1: 2023-10-12
Enter data for Row 2, Cell 2: 400
Excel file written successfully!

Resulting newdata.xlsx:


Java    2023-10-10  200    New York
Python  2023-10-11  300    
C#      2023-10-12  400

5.4 Handling Common Issues:

  • File Overwrite Warning:When writing to an existing file, FileOutputStream will overwrite it. Ensure you’re not unintentionally overwriting important data.
  • Data Types:setCellValue() is overloaded to handle different data types (String, double, boolean, etc.). Use the appropriate method based on the data type you intend to write.

6. Best Practices and Tips

6.1. Dynamic Path Handling

Use System.getProperty("user.dir"):

String filePath = System.getProperty("user.dir") + "/testdata/data.xlsx";

6.2. Exception Handling

  • Comprehensive Try-Catch Blocks:Enclose file operations within try-catch blocks to handle IOExceptions and prevent abrupt script termination.
  • Resource Management:Always close Workbook, FileInputStream, FileOutputStream, and Scanner objects in the finally block or use try-with-resources for automatic closure.

6.3. Utility Classes for Reusability

Create a Utility Class:

public class ExcelUtils {
    public static XSSFWorkbook getWorkbook(String filePath) throws IOException {
        FileInputStream fis = new FileInputStream(filePath);
        return new XSSFWorkbook(fis);
    }

    public static XSSFSheet getSheet(XSSFWorkbook workbook, String sheetName) {
        return workbook.getSheet(sheetName);
    }

    public static Row getRow(XSSFSheet sheet, int rowIndex) {
        return sheet.getRow(rowIndex);
    }

    public static Cell getCell(Row row, int cellIndex) {
        return row.getCell(cellIndex);
    }

    // Add more utility methods as needed
}

6.4. Data Validation

Validate Excel Data:

  • Before using the data in your tests, validate it to ensure correctness and completeness, reducing the risk of test failures due to data issues.

6.5. Avoid Hardcoding Paths

Use Dynamic Paths:

String filePath = System.getProperty("user.dir") + "/testdata/data.xlsx";

6.6. Logging and Reporting

Implement Logging:

  • Use logging frameworks like Log4j to capture detailed logs, aiding in debugging and monitoring test executions.

6.7. Consistent Naming Conventions

  • Use Descriptive Names: Name your sheets, rows, and cells descriptively to make the data easily understandable.

7. Conclusion

Mastering Data-Driven Testing using Excel files with Selenium WebDriver and Apache POI in Java is pivotal for creating robust and scalable automated tests. By understanding the Excel file structure, effectively integrating Apache POI, and implementing best practices, you can efficiently manage and utilize test data, enhancing your testing strategy’s effectiveness.

Recap of Key Concepts:

  • Data-Driven Testing: Separates test data from test logic, allowing for multiple test iterations with varying data sets.
  • Excel File Structure: Hierarchical organization from Excel File → Workbook → Sheet → Row → Cell.
  • Apache POI Integration: Enables reading from and writing to Excel files within Java-based Selenium projects.
  • Reading and Writing Operations: Utilize Apache POI classes and methods to interact with Excel data programmatically.
  • Best Practices: Emphasize dynamic path handling, exception management, utility classes, and logging for efficient automation scripting.

Next Steps:

  1. Implement Utility Classes: Create reusable utility classes to handle Excel operations, streamlining your Data-Driven Testing approach.
  2. Integrate with Test Frameworks: Combine Data-Driven Testing with frameworks like TestNG or JUnit to enhance test management and reporting.
  3. Explore Advanced Apache POI Features: Delve deeper into Apache POI to handle complex Excel operations, such as formatting, styling, and managing large datasets.
  4. Automate Data Validation: Incorporate data validation steps to ensure the integrity and accuracy of your test data before executing tests.

Happy Testing! 🚀

8. Practical Implementation of Data-Driven Testing

In this section, we’ll walk through the practical steps of implementing Data-Driven Testing using the utility files we previously created. We’ll cover:

  1. Creating a Data-Driven Test Case
  2. Handling Excel Operations with Utility Methods
  3. Integrating Properties Files for Configuration Management
  4. Executing and Validating Test Cases
  5. Best Practices and Troubleshooting

8.1. Creating a Data-Driven Test Case

To illustrate Data-Driven Testing, we’ll automate a Fixed Deposit (FD) Calculator application. The test case involves:

  1. Launching the Application
  2. Reading Test Data from Excel
  3. Entering Data into the Application
  4. Validating the Maturity Value
  5. Writing Results Back to Excel
  6. Handling Positive and Negative Scenarios

8.1.1. Understanding the Test Case

Scenario: Fixed Deposit (FD) Calculator

  • Inputs:
    • Principal Amount
    • Rate of Interest
    • Period (Number and Type: Days, Months, Years)
    • Frequency (Simple Interest)
  • Process:
    • Enter the input data.
    • Click on the “Calculate” button.
  • Outputs:
    • Maturity Value: The calculated amount based on the inputs.
  • Validation:
    • Compare the calculated maturity value with the expected value.
    • Update the result (“Pass” or “Fail”) in the Excel sheet.

8.1.2. Preparing Test Data

Create an Excel file named FDData.xlsx in the testdata folder with the following structure:

Principal Amount	Rate of Interest	Period	Period Type	Frequency	Expected Maturity Value
10000	        5	                2	    Years	        Simple	    11000
15000	        4.5	            1	    Years	        Simple	    15750
20000	        6	                3	    Years	        Simple	    23600
25000	        5.5	            2	    Years	        Simple	    27625
30000	        5	                2	    Years	        Simple	    33000
35000	        4	                1	    Months	        Simple	    35350

Notes:

  • The first five rows contain positive scenarios where the expected maturity value is correctly calculated.
  • The last row introduces a negative scenario with an incorrect expected maturity value to validate failure handling.

8.2. Handling Excel Operations with Utility Methods

Utilize the previously created ExcelUtils class to manage Excel operations efficiently. This approach avoids code duplication and enhances maintainability.

8.2.1. Recap of ExcelUtils Methods

  • getRowCount(String filePath, String sheetName): int
    • Returns the total number of rows in a specified sheet.
  • getCellCount(String filePath, String sheetName, int rowNum): int
    • Returns the total number of cells in a specified row.
  • getCellData(String filePath, String sheetName, int rowNum, int cellNum): String
    • Retrieves data from a specific cell.
  • setCellData(String filePath, String sheetName, int rowNum, int cellNum, String data): void
    • Writes data to a specific cell.
  • fillGreenColor(String filePath, String sheetName, int rowNum, int cellNum): void
    • Applies green color to a specific cell (optional).
  • fillRedColor(String filePath, String sheetName, int rowNum, int cellNum): void
    • Applies red color to a specific cell (optional).

8.3. Integrating Properties Files for Configuration Management

Properties files are essential for managing configuration data such as application URLs, credentials, and other constants. This integration ensures that any changes to configuration data don’t require modifying the test scripts themselves.

8.3.1. Creating the config.properties File

Place the config.properties file in the testdata folder with the following content:

appURL=http://example.com/fdcalculator
email=your_email@example.com
password=your_password
orderID=ORD12345
customerID=CUST67890

Key-Value Pairs:

  • appURL: The URL of the FD Calculator application.
  • email: Test email address.
  • password: Test password.
  • orderID: Example order ID.
  • customerID: Example customer ID.

8.3.2. Reading Properties Files

We’ll use the Properties class in Java to read data from the config.properties file. Here’s how to implement it:

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;

public class ConfigReader {
    public static Properties readProperties(String filePath) {
        Properties properties = new Properties();
        try {
            FileInputStream fis = new FileInputStream(filePath);
            properties.load(fis);
            fis.close();
        } catch (IOException e) {
            System.out.println("An error occurred while reading the properties file.");
            e.printStackTrace();
        }
        return properties;
    }

    public static void main(String[] args) {
        String filePath = System.getProperty("user.dir") + "/testdata/config.properties";
        Properties config = readProperties(filePath);

        String appURL = config.getProperty("appURL");
        String email = config.getProperty("email");
        String password = config.getProperty("password");
        String orderID = config.getProperty("orderID");
        String customerID = config.getProperty("customerID");

        System.out.println("Application URL: " + appURL);
        System.out.println("Email: " + email);
        System.out.println("Password: " + password);
        System.out.println("Order ID: " + orderID);
        System.out.println("Customer ID: " + customerID);
    }
}

Explanation:

  1. readProperties(String filePath): Properties
    • Purpose: Reads the properties file and returns a Properties object containing key-value pairs.
    • Parameters:
      • filePath: The dynamic path to the config.properties file.
  2. main Method:
    • Demonstrates how to use the readProperties method to retrieve configuration data.
    • Prints the retrieved values to the console for verification.

Output Example:


Application URL: http://example.com/fdcalculator
Email: your_email@example.com
Password: your_password
Order ID: ORD12345
Customer ID: CUST67890

Best Practices:

  • Centralized Configuration: Keep all configuration data in the properties file to avoid hardcoding values in test scripts.
  • Security: Avoid storing sensitive information (like passwords) in plain text. Consider using encryption or environment variables for sensitive data.
  • Dynamic Path Handling: Use System.getProperty("user.dir") to construct dynamic paths, enhancing portability across different environments.

8.4. Executing and Validating Test Cases

With the utility files and configuration management in place, we can now create and execute our Data-Driven Test Case for the FD Calculator.

8.4.1. Creating the FD Calculator Test Case

Create a new Java class named FDCalculatorTest in your test package. This class will contain the automation script to execute the DDT.

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.Select;
import java.util.Properties;

public class FDCalculatorTest {
    public static void main(String[] args) {
        // Step 1: Initialize WebDriver
        System.setProperty("webdriver.chrome.driver", "path_to_chromedriver");
        WebDriver driver = new ChromeDriver();
        
        // Step 2: Maximize Browser and Navigate to Application URL
        driver.manage().window().maximize();
        
        // Read properties file
        String configFilePath = System.getProperty("user.dir") + "/testdata/config.properties";
        Properties config = ConfigReader.readProperties(configFilePath);
        String appURL = config.getProperty("appURL");
        driver.get(appURL);
        
        // Excel file path
        String excelFilePath = System.getProperty("user.dir") + "/testdata/FDData.xlsx";
        String sheetName = "Sheet1";
        
        // Get row count
        int rowCount = ExcelUtils.getRowCount(excelFilePath, sheetName);
        System.out.println("Total Rows: " + rowCount);
        
        // Iterate through each row (starting from 1 to skip header)
        for (int i = 1; i <= rowCount; i++) {
            try {
                // Step 3: Read Data from Excel
                String principal = ExcelUtils.getCellData(excelFilePath, sheetName, i, 0);
                String rateOfInterest = ExcelUtils.getCellData(excelFilePath, sheetName, i, 1);
                String period = ExcelUtils.getCellData(excelFilePath, sheetName, i, 2);
                String periodType = ExcelUtils.getCellData(excelFilePath, sheetName, i, 3);
                String frequency = ExcelUtils.getCellData(excelFilePath, sheetName, i, 4);
                String expectedMaturity = ExcelUtils.getCellData(excelFilePath, sheetName, i, 5);
                
                // Step 4: Enter Data into Application
                WebElement principalField = driver.findElement(By.xpath("//input[@id='principal']"));
                principalField.clear();
                principalField.sendKeys(principal);
                
                WebElement roiField = driver.findElement(By.xpath("//input[@id='rate']"));
                roiField.clear();
                roiField.sendKeys(rateOfInterest);
                
                WebElement periodField = driver.findElement(By.xpath("//input[@id='period']"));
                periodField.clear();
                periodField.sendKeys(period);
                
                Select periodDropdown = new Select(driver.findElement(By.xpath("//select[@id='periodType']")));
                periodDropdown.selectByVisibleText(periodType);
                
                Select frequencyDropdown = new Select(driver.findElement(By.xpath("//select[@id='frequency']")));
                frequencyDropdown.selectByVisibleText(frequency);
                
                // Step 5: Click Calculate
                WebElement calculateButton = driver.findElement(By.xpath("//button[@id='calculate']"));
                calculateButton.click();
                
                // Wait for calculation to complete
                Thread.sleep(3000);
                
                // Step 6: Capture Actual Maturity Value
                WebElement maturityValueElement = driver.findElement(By.xpath("//span[@id='maturityValue']/strong"));
                String actualMaturity = maturityValueElement.getText();
                
                // Step 7: Validate Maturity Value
                double expected = Double.parseDouble(expectedMaturity);
                double actual = Double.parseDouble(actualMaturity.replace("$", ""));
                
                if (expected == actual) {
                    System.out.println("Test Case " + i + ": Passed");
                    ExcelUtils.setCellData(excelFilePath, sheetName, i, 6, "Pass");
                    ExcelUtils.fillGreenColor(excelFilePath, sheetName, i, 6);
                } else {
                    System.out.println("Test Case " + i + ": Failed");
                    ExcelUtils.setCellData(excelFilePath, sheetName, i, 6, "Fail");
                    ExcelUtils.fillRedColor(excelFilePath, sheetName, i, 6);
                }
                
                // Step 8: Clear Data for Next Iteration
                WebElement clearButton = driver.findElement(By.xpath("//button[@id='clear']"));
                clearButton.click();
                
                // Wait after clearing
                Thread.sleep(2000);
                
            } catch (Exception e) {
                System.out.println("An error occurred in Test Case " + i);
                e.printStackTrace();
            }
        }
        
        // Step 9: Close Browser
        driver.quit();
    }
}

Explanation:

  1. WebDriver Initialization:
    • Set the system property for ChromeDriver.
    • Instantiate the ChromeDriver and maximize the browser window.
    • Navigate to the FD Calculator application URL obtained from the properties file.
  2. Excel Operations:
    • Define the path to the FDData.xlsx file.
    • Retrieve the total number of rows to determine the number of test iterations.
  3. Test Iterations:
    • Loop through each row of the Excel sheet (excluding the header).
    • Read Data: Extract input values and expected maturity value from Excel using ExcelUtils.
    • Enter Data: Locate web elements using XPath and input the extracted data.
    • Calculate: Click the “Calculate” button and wait for the calculation to complete.
    • Capture Actual Maturity Value: Retrieve the calculated maturity value from the application.
    • Validate: Compare the actual maturity value with the expected value.
      • If they match, mark the test case as “Pass” and fill the cell with green color.
      • If they don’t match, mark it as “Fail” and fill the cell with red color.
    • Clear Data: Click the “Clear” button to reset the form for the next iteration.
  4. Exception Handling:
    • Encapsulate each iteration within a try-catch block to handle any unexpected errors without terminating the entire test suite.
  5. Cleanup:
    • After all iterations, close the browser using driver.quit() to end the session.

8.4.2. Handling Data Types and Formatting

  • Maturity Value Comparison:
    • The maturity values are in string format with a dollar sign (e.g., $11000).
    • Use replace("$", "") to remove the dollar sign before parsing to double.
  • Thread Sleep:
    • Thread.sleep(3000); pauses the execution for 3 seconds to allow the calculation to complete.
    • Adjust the sleep duration based on the application’s response time.

8.4.3. Executing the Test Case

  1. Ensure All Files Are Closed:
    • Close the FDData.xlsx and config.properties files to prevent file locking issues during execution.
  2. Run the Test Script:
    • Execute the FDCalculatorTest class.
    • Observe the console for “Passed” or “Failed” messages corresponding to each test case.
  3. Verify Excel Results:
    • Open the FDData.xlsx file post-execution.
    • Check the “Expected Maturity Value” and “Result” columns to ensure they reflect the test outcomes.
    • Cells marked “Pass” should be filled with green, and “Fail” with red.

Sample Console Output:


Total Rows: 6
Test Case 1: Passed
Test Case 2: Passed
Test Case 3: Passed
Test Case 4: Passed
Test Case 5: Passed
Test Case 6: Failed

Sample Excel Output:


Principal Amount	Rate of Interest	Period	Period Type	Frequency	Expected Maturity Value	Result
10000	            5	                2	    Years	        Simple	    11000	        Pass
15000	            4.5	            1	    Years	        Simple	    15750	        Pass
20000	            6	                3	    Years	        Simple	    23600	        Pass
25000	            5.5	            2	    Years	        Simple	    27625	        Pass
30000	            5	                2	    Years	        Simple	    33000	        Pass
35000	            4	                1	    Months	        Simple	    35350	        Fail

Notes:

  • The sixth test case is designed to fail to validate negative scenarios.
  • The Result column is automatically updated based on the validation.

8.5. Best Practices and Troubleshooting

8.5.1. Best Practices

  1. Use Dynamic Paths:
    • Always construct file paths dynamically using System.getProperty("user.dir") to ensure portability.
  2. Centralize Configuration:
    • Store all configuration data in properties files to avoid hardcoding and simplify maintenance.
  3. Modularize Code:
    • Use utility classes (ExcelUtils, ConfigReader) to encapsulate repetitive tasks, enhancing reusability and readability.
  4. Implement Robust Exception Handling:
    • Use try-catch blocks to handle unexpected issues without halting the entire test suite.
  5. Validate Data Types:
    • Ensure that data types are correctly handled, especially when converting between strings and numeric types.
  6. Use Explicit Waits:
    • Instead of Thread.sleep(), consider using Selenium’s WebDriverWait for more efficient and reliable waits.
  7. Maintain Clear Naming Conventions:
    • Use descriptive names for variables, methods, and files to improve code clarity.
  8. Avoid File Locking Issues:
    • Ensure that Excel and properties files are closed before and after test execution to prevent locking problems.

8.5.2. Troubleshooting Common Issues

  1. File Not Found Exception:
    • Cause: Incorrect file path or missing file.
    • Solution: Verify the file path using System.getProperty("user.dir") and ensure the file exists in the specified location.
  2. NullPointerException:
    • Cause: Attempting to access a cell or row that doesn’t exist.
    • Solution: Ensure that the Excel sheet has the expected number of rows and columns. Use ExcelUtils methods to retrieve counts dynamically.
  3. Number Format Exception:
    • Cause: Parsing non-numeric strings to numeric types.
    • Solution: Validate and sanitize input data. Remove any non-numeric characters (like $) before parsing.
  4. Element Not Found Exception:
    • Cause: Incorrect XPath or changes in the application’s UI.
    • Solution: Re-validate the XPath selectors using browser developer tools and update them as needed.
  5. Test Data Inconsistencies:
    • Cause: Mismatch between expected and actual data.
    • Solution: Ensure that the expected maturity values in Excel are accurately calculated using the correct formula.
  6. File Locking Issues:
    • Cause: Excel or properties files are open during test execution.
    • Solution: Close all instances of the files before running tests. Ensure that ExcelUtils properly closes file streams.

8.6. Conclusion

Implementing Data-Driven Testing enhances the scalability, reusability, and maintainability of automated test suites. By leveraging Excel for test data management and properties files for configuration, testers can efficiently execute multiple test scenarios with varying inputs without duplicating code.

Key Takeaways:

  • Separation of Concerns: Distinguish between test logic and test data to simplify test case management.
  • Utility Classes: Encapsulate repetitive tasks within utility classes to promote code reuse.
  • Configuration Management: Utilize properties files to centralize configuration data, reducing the need for code changes.
  • Robust Validation: Implement thorough validation mechanisms to ensure test accuracy and reliability.
  • Best Practices: Adhere to coding best practices to enhance test suite efficiency and ease of maintenance.

Next Steps:

  1. Explore Advanced DDT Techniques:
    • Incorporate frameworks like TestNG or JUnit for enhanced test management and reporting.
  2. Integrate with Continuous Integration (CI) Tools:
    • Automate test executions using CI tools like Jenkins or GitHub Actions for continuous testing.
  3. Enhance Reporting Mechanisms:
    • Implement detailed reporting using tools like ExtentReports or Allure for better visibility into test outcomes.
  4. Expand Utility Classes:
    • Add more utility methods to handle complex Excel operations, such as formatting, conditional styling, and data validation.
  5. Implement Page Object Model (POM):
    • Adopt the POM design pattern to further modularize test scripts and improve maintainability.

Happy Testing! 🚀


Last Post


Next Post