Excel Data Handling in Java

Overview

This lecture covered how to perform data-driven testing in Selenium using Excel files, focusing on reading and writing Excel data in Java projects via the Apache POI library.

Introduction to Data-Driven Testing

• Data-driven testing allows test data to be stored in Excel files and used during automated testing.
• Selenium WebDriver does not natively support Excel, requiring integration with third-party libraries.

Working with Excel Files in Java

• To handle Excel files (.xls/.xlsx) in Java, use the Apache POI library.
• Add two Maven dependencies: poi and poi-ooxml to pom.xml.
• File input/output is managed with Java's FileInputStream and FileOutputStream classes.

Excel File Structure and Hierarchy

• An Excel file is a workbook, which contains sheets.
• Each sheet contains rows, and each row contains cells.
• Data reading/writing operations target the cell level, accessed using this hierarchy.

Apache POI Classes for Excel Handling

• XSSFWorkbook: represents the workbook.
• XSSFSheet: represents a sheet.
• XSSFRow: represents a row.
• XSSFCell: represents a cell.

Reading Data from Excel

• Steps:
  1. Open file with FileInputStream.
  2. Create XSSFWorkbook using the file stream.
  3. Access a sheet with getSheet or getSheetAt.
  4. Loop through rows (getLastRowNum) and cells (getLastCellNum).
  5. Read data from each cell using toString().
• Both rows and columns are indexed from 0 in Java.

Writing Data to Excel

• Steps:
  1. Create file with FileOutputStream.
  2. Instantiate XSSFWorkbook, then create a sheet via createSheet.
  3. Use createRow and createCell to build rows and cells.
  4. Write values using setCellValue.
  5. Write workbook to file and close resources.
• Data can be written statically or dynamically using loops and user input.

Advanced Usage Examples

• Dynamic data entry: Use Scanner to accept number of rows/cells and values at runtime.
• Writing data to specific rows/cells: Use explicit indices in createRow/createCell.
• Typically, data is read for test execution; writing is used less frequently.

Key Terms & Definitions

• Apache POI — Java API for reading and writing Microsoft Office files.
• Data-Driven Testing — Process where test data is separated from test logic, often managed externally.
• Workbook — The main Excel file; contains sheets.
• Sheet/Worksheet — A tab within a workbook, holding rows and cells.
• Row — Horizontal set of cells in a sheet.
• Cell — The smallest unit in a worksheet; holds the actual data.

Action Items / Next Steps

• Practice reading and writing Excel data using Apache POI.
• Add required dependencies to your Maven pom.xml.
• Prepare for next session: utility file creation for reusable Excel operations in data-driven tests.