Excel data reading (Apache POI) in Selenium Java - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a Workbook object from an Excel file input stream.

Selenium Java

FileInputStream file = new FileInputStream("data.xlsx");
Workbook workbook = new [1](file);

Drag options to blanks, or click blank then click option'

AXSSFWorkbook

BFileInputStream

CSheet

DCell

Attempts:

3 left

💡 Hint

Common Mistakes

Using FileInputStream instead of XSSFWorkbook.

Confusing Sheet or Cell classes with Workbook.

The XSSFWorkbook class is used to create a Workbook object for .xlsx files from a FileInputStream.

2fill in blank

medium

Complete the code to get the first sheet from the workbook.

Selenium Java

Sheet sheet = workbook.[1](0);

Drag options to blanks, or click blank then click option'

AcreateSheet

BgetSheetAt

CgetRow

DgetSheetName

Attempts:

3 left

💡 Hint

Common Mistakes

Using getSheetName which returns a String, not a Sheet object.

Using getRow which is for rows, not sheets.

The getSheetAt(int index) method returns the sheet at the given index from the workbook.

3fill in blank

hard

Fix the error in the code to read the string value from the first cell of the first row.

Selenium Java

Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String value = cell.[1]();

Drag options to blanks, or click blank then click option'

AgetNumericCellValue

BtoString

CgetStringCellValue

DgetDateCellValue

Attempts:

3 left

💡 Hint

Common Mistakes

Using getNumericCellValue on a string cell causes runtime errors.

Using toString() returns a generic string, not the cell content.

To read a string from a cell, use getStringCellValue(). Other methods are for different data types.

4fill in blank

hard

Fill both blanks to iterate over all rows and print the first cell's string value.

Selenium Java

for (int [1] = 0; [2] <= sheet.getLastRowNum(); [1]++) {
    Row row = sheet.getRow([1]);
    System.out.println(row.getCell(0).getStringCellValue());
}

Drag options to blanks, or click blank then click option'

Ai

Bj

CrowNum

Dindex

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names in the loop condition and increment.

Using a variable name not declared in the loop.

The variable i is commonly used as the loop counter. Both blanks must use the same variable to iterate correctly.

5fill in blank

hard

Fill all three blanks to create a map of row numbers to the string value of the first cell, filtering rows where the cell is not empty.

Selenium Java

Map<Integer, String> dataMap = new HashMap<>();
for (int [1] = 0; [1] <= sheet.getLastRowNum(); [1]++) {
    Row row = sheet.getRow([1]);
    if (row != null && row.getCell(0) != null && !row.getCell(0).getStringCellValue().[2]()) {
        dataMap.put([3], row.getCell(0).getStringCellValue());
    }
}

Drag options to blanks, or click blank then click option'

Ai

BisEmpty

Dtrim

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variables for loop and map key causing errors.

Using trim() instead of isEmpty() for the condition.

Not checking for null rows or cells.

The loop variable i is used for iteration and as the key in the map. The isEmpty() method checks if the cell string is empty to filter out empty cells.

Practice

1. What is the main purpose of using Apache POI in Selenium Java testing?

easy

A. To read and write Excel files for test data management

B. To locate web elements on a webpage

C. To execute JavaScript code in the browser

D. To generate HTML reports for test results

Solution

1. Step 1: Understand Apache POI's role

   Apache POI is a library used to handle Microsoft Office files, especially Excel, in Java programs.

2. Step 2: Connect Apache POI with Selenium testing

   In Selenium tests, Apache POI helps read and write Excel files to manage test data efficiently.

3. Final Answer:

   To read and write Excel files for test data management -> Option A

4. Quick Check:

   Apache POI = Excel file handling [OK]

Hint: Apache POI = Excel data handling in Java tests [OK]

Common Mistakes:

• Confusing Apache POI with Selenium WebDriver functions
• Thinking Apache POI is for web element locating
• Assuming Apache POI generates test reports

2. Which of the following is the correct way to open an Excel workbook using Apache POI in Java?

easy

A. Workbook workbook = new Workbook("file.xlsx");

B. Workbook workbook = WorkbookFactory.create(new FileInputStream("file.xlsx"));

C. Workbook workbook = Workbook.open("file.xlsx");

D. Workbook workbook = new FileInputStream("file.xlsx");

Solution

1. Step 1: Identify correct Workbook creation method

   Apache POI uses WorkbookFactory.create(InputStream) to open Excel files safely.

2. Step 2: Check each option's syntax

   Workbook workbook = WorkbookFactory.create(new FileInputStream("file.xlsx")); correctly uses WorkbookFactory.create with FileInputStream. Others are invalid or incorrect constructors.

3. Final Answer:

   Workbook workbook = WorkbookFactory.create(new FileInputStream("file.xlsx")); -> Option B

4. Quick Check:

   Use WorkbookFactory.create with FileInputStream [OK]

Hint: Use WorkbookFactory.create with FileInputStream to open Excel [OK]

Common Mistakes:

• Trying to instantiate Workbook directly
• Using wrong constructors or methods
• Assigning FileInputStream to Workbook variable

3. Given the following code snippet, what will be printed if the Excel cell at row 0, column 1 contains the string "TestData"?

Workbook workbook = WorkbookFactory.create(new FileInputStream("data.xlsx"));
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(1);
System.out.println(cell.getStringCellValue());
workbook.close();

medium

A. TestData

B. null

C. 0

D. Throws NullPointerException

Solution

1. Step 1: Access the correct cell value

   The code accesses row 0, cell 1, which contains the string "TestData".

2. Step 2: Print the string cell value

   Calling getStringCellValue() on that cell returns "TestData", which is printed.

3. Final Answer:

   TestData -> Option A

4. Quick Check:

   Cell value = "TestData" printed [OK]

Hint: getStringCellValue() returns cell text directly [OK]

Common Mistakes:

• Assuming cell is null and causes exception
• Using getNumericCellValue() on string cell
• Confusing row and column indexes

4. Identify the error in the following code snippet that reads an Excel file using Apache POI:

FileInputStream fis = new FileInputStream("data.xlsx");
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
// Missing workbook.close()

medium

A. WorkbookFactory.create() cannot accept FileInputStream

B. getSheetAt(0) should be getSheet("Sheet1")

C. FileInputStream should be closed after use

D. getStringCellValue() cannot be called on numeric cells

Solution

1. Step 1: Check resource management

   The code opens FileInputStream but does not close it, which can cause resource leaks.

2. Step 2: Confirm correct usage of WorkbookFactory and methods

   WorkbookFactory.create accepts FileInputStream, getSheetAt(0) is valid, and getStringCellValue() is fine if cell is string.

3. Final Answer:

   FileInputStream should be closed after use -> Option C

4. Quick Check:

   Always close streams to avoid leaks [OK]

Hint: Always close FileInputStream and Workbook after use [OK]

Common Mistakes:

• Forgetting to close FileInputStream
• Assuming WorkbookFactory.create rejects streams
• Confusing sheet access methods

5. You want to read multiple rows from an Excel sheet and store the first column values into a List<String>. Which approach correctly reads all rows except the header row using Apache POI?

hard

A. Use a for loop from 1 to sheet.getPhysicalNumberOfRows(), get each row's cell(1) string value, add to list

B. Use a for loop from 0 to sheet.getLastRowNum(), get each row's cell(0) numeric value, add to list

C. Use a while loop with sheet.getPhysicalNumberOfRows(), get cell(1) string value, add to list

D. Use a for loop from 1 to sheet.getLastRowNum(), get each row's cell(0) string value, add to list

Solution

1. Step 1: Skip header row and loop through rows

   Start loop at 1 to skip header (row 0), loop until sheet.getLastRowNum() to cover all rows.

2. Step 2: Access first column cell and get string value

   Get cell at index 0 (first column), call getStringCellValue(), and add to list.

3. Final Answer:

   Use a for loop from 1 to sheet.getLastRowNum(), get each row's cell(0) string value, add to list -> Option D

4. Quick Check:

   Loop rows from 1, get cell(0) string values [OK]

Hint: Start loop at 1 to skip header, get cell(0) string values [OK]

Common Mistakes:

• Starting loop at 0 and including header
• Using wrong cell index (1 instead of 0)
• Using numeric value for string cells