How do I read cell value from Excel sheet based on the cell attribute (keyword) using Apache POI integration for Selenium

Question

Example of Excel Data

I need to read the above Example Data using cell reference attribute (highlighted in blue)

The table in the sheet is maintained in column order.

For example, if the table is something like:

firstName	Nick	Jack
lastName	Fury	Ryan
personalEmail	[email protected]	[email protected]

Then I want the script to run for:

firstName	Nick
lastName	Fury
personalEmail	[email protected]

And then run for:

firstName	Jack
lastName	Ryan
personalEmail	[email protected]

And be accessible using the corresponding attributes (firstName, lastName, personalEmail) in my code for the ExcelReader class.

Here's what I want to know:

1. Is there a way to achieve this using Apache-poi extension for Java?
2. What function libraries can I used from the apache-poi extension?
3. What code should I use in my utilities package?

Thanks in Advance :)

Answer 1

To solve this you need to reverse the data getting logic. So here we first need to get the column data and then traverse all its row.

ie. Nick -> Fury -> [email protected] and then moving to another column and fetch Jack -> Ryan -> [email protected]

Screenshot:

Important Note:

This code is to fetch xls file data using POI, kindly change the code as per your requirement.

(1). HSSFWorkbook: This class has methods to read and write Microsoft Excel files in .xls format.

(2).XSSFWorkbook: This class has methods to read and write Microsoft Excel and OpenOffice xml files in .xls or .xlsx format.

Code:

@Test(dataProvider = "getExcelData")
    public void testSheet(String firstName, String lastName, String personalEmail) {

    System.out.println(firstName+"  "+lastName+" "+personalEmail);
}

@DataProvider
public Object[][] getExcelData(){
    String excelSheetPath = System.getProperty("user.dir")+"/data.xls";
    String sheetName = "Sheet1";
    return getExcelData(excelSheetPath, sheetName);
}

public Object[][] getExcelData(String excelSheetPath, String sheetName) {
    Object[][] arrayExcelData = null;
    try (
            FileInputStream fileStream = new FileInputStream(excelSheetPath)
    ) {
        HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
        HSSFSheet sheet = workbook.getSheet(sheetName);
        Row row = sheet.getRow(0);
        int lastRowIndex = sheet.getLastRowNum() + 1;
        System.out.println("Last row index :" + lastRowIndex);
        int totalNoOfCols = row.getLastCellNum() - 1;
        System.out.println("Total columns :" + totalNoOfCols);

        arrayExcelData = new Object[totalNoOfCols][lastRowIndex];
        DataFormatter df = new DataFormatter();

        for (int i = 1; i <= totalNoOfCols ; i++) {
            for (int j = 0; j < lastRowIndex; j++) {
                row = sheet.getRow(j);
                Cell c = row.getCell(i);
                String cellData = df.formatCellValue(c);
                System.out.println(cellData);
                arrayExcelData[i-1][j] = cellData;
            }
            System.out.println("-----------");
        }
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println(e.getMessage());
    }
    return arrayExcelData;
}