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

Issue

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 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:

  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 🙂

Solution

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:

enter image description here

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

Answered By – Abhishek Dhoundiyal

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published