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

Question

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	nick-fury@example.com	jack-ryan@example.com

Then I want the script to run for:


firstName	Nick
lastName	Fury
personalEmail	nick-fury@example.com

And then run for:


firstName	Jack
lastName	Ryan
personalEmail	jack-ryan@example.com

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

Here's what I want to know:

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

Thanks in Advance :)

There are many tutorials on Google. Did you try any one of them ? — cruisepandey, Jul 17 '21 at 11:24
See https://poi.apache.org/components/spreadsheet/quick-guide.html#CellContents. But `Excel` sheets store data row wise. Having data stored column wise, having data labels in first column and data records in next column, contradicts that. Only possibility is traversing all rows reading data labels from first column. Then again traversing all rows reading data of first record from second column. Then again traversing all rows reading data of second record from third column. And so on. — Axel Richter, Jul 17 '21 at 12:11

score 1 · Accepted Answer · answered Jul 18 '21 at 14:16

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 -> nick-fury@example.com and then moving to another column and fetch Jack -> Ryan -> jack-ryan@example.com

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;
}

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

1 Answers1