Session 41 - Selenium with Java | Data Driven Testing with MS-Excel

Summary: Data-Driven Testing (DDT) with MS-Excel (and Selenium)

1) What is Data-Driven Testing (DDT) using MS-Excel

• Definition: Prepare test data in an Excel sheet, read that data, and reuse it to run the same test flow multiple times.
• Execution model: Each Excel row represents a different test input set. The automation:
  1. reads row data from Excel,
  2. feeds it into the application,
  3. performs validations,
  4. optionally writes results back into Excel (e.g., Pass/Fail, styling).

2) Core challenge in naïve implementations

If each data-driven test case includes both:

• Application code (browser, element interaction, assertions), and
• Excel code (open workbook, count rows/columns, read/write cells),

then issues arise:

• Higher test complexity: application logic + Excel logic become intertwined.
• Excel logic duplication: repeated Excel operations across many test cases.

3) Solution: Excel “Utility File” (reusable helper class in Java)

Create a separate utility class (Java) to hold reusable static methods for Excel operations.

• Benefits:
  • cleaner test cases (reduced complexity inside test code),
  • reusable Excel operations across the framework.

4) Excel Utility File: major methods described

An example utility class (e.g., ExcelUtils) uses Apache POI workbook/sheet/cell concepts.

File/Workbook handling

• Uses FileInputStream for reading and (for writing) FileOutputStream.
• Works with Workbook, Sheet, Row, Cell equivalents.
• Applies formatting with CellStyle (optional).

Reading helpers

• getRowCount(filePath, sheetName)
  • opens workbook, finds the last row number, and returns row count.
• getCellCount(filePath, sheetName, rowNumber)
  • returns number of cells in a given row.
• getCellData(filePath, sheetName, rowNumber, cellNumber)
  • reads a cell value as string.
  • Uses either:
    • cell.toString(), or
    • DataFormatter.formatCellValue(cell) (useful for different cell types like numeric/text/date).
  • Includes try-catch logic to safely handle empty cells (returns empty string instead of failing on missing data).

Writing helpers

• setCellData(filePath, sheetName, rowNumber, cellNumber, data)
  • updates result cells (e.g., “Passed/Failed”) in the same Excel file.
  • Designed to:
    • read existing workbook structure,
    • update a specific cell,
    • write the workbook back.

Optional styling helpers

• fillGreenColor(...) / fillRedColor(...)
  • use CellStyle and IndexedColors to color cells based on result.

5) Data-Driven Test Case workflow (Selenium + Excel)

The tutorial builds a Selenium automation test that uses:

• Selenium WebDriver to interact with a calculator-like application
• Excel row-driven iteration

Required steps to build a DDT

1. Understand the test case flow (what to validate).
2. Prepare test data in Excel (including positive and negative cases).
3. Develop automation code that reads Excel rows and runs validations.

DDT loop logic

• Use a for loop over rowCount.
• For each Excel row:
  1. Read inputs from specific columns (e.g., principal, rate, period, frequency).
  2. Fill application fields, including dropdown selection using Select and visible text.
  3. Click Calculate.
  4. Capture the dynamic expected output from the UI using a carefully built XPath (targeting a stable parent/id region and then retrieving inner strong text).
  5. Compare actual vs expected:
     • convert expected and actual strings to double before comparing.
  6. Update Excel:
     • set result cell to Passed or Failed
     • optionally apply green/red styling.
  7. Click Clear to reset the application before the next row.
• Execution ends after processing all Excel rows.

6) Example dataset: Fix Deposit / Simple Interest calculation

Excel columns typically include:

• principal amount
• rate of interest
• period (value + dropdown type: days/months/years)
• frequency/interest type (e.g., simple interest)
• expected maturity value

Validation uses a simple interest concept:

• P * R * (period) / 100 (conceptually)

Result column behavior:

• first rows are positive → expect Pass
• last row(s) are negative/invalid → expect Fail

7) Practical note: Excel file locking

If the Excel file is opened during execution, automation may lock it.

• Recommendation: close Excel before running tests to avoid locking/read/write issues.

---

Properties File concept (next topic introduced)

8) Why use .properties files in automation frameworks

• Stores common configuration data (like URLs, usernames, passwords) as key-value pairs.
• Avoids hardcoding the same values in every test.
• If values change (e.g., URL/credentials), update only once in the properties file.

9) Reading properties in Java

• Uses java.util.Properties
• Loads the file using FileInputStream
• Retrieves values with:
  • properties.getProperty("key")
• Also mentions:
  • reading all keys via stringPropertyNames()
  • reading all values via values()

---

Assignment: DDT practice

• Automate a new calculator-like web app (URL provided in the course).
• Requirements:
  • clear pre-populated default values,
  • read new input values from an Excel test data sheet,
  • select dropdown values appropriately,
  • validate displayed computed result against expected value from Excel (may include currency formatting like $),
  • write Pass/Fail into the result column and use the provided Excel utility file.

---

Main speakers / sources (from subtitles)

• Main speaker: Instructor (unnamed)
• Technical sources referenced: Java + Apache POI (implied by Excel utility usage) and Selenium WebDriver (implied throughout)