How to Read Column Wise Data from Excel File-Apache POI

Today someone on the Stack-overflow, asked me that he wanted to read the below Example Data using cell reference attribute.

The table in the sheet is maintained in column order.

For example, if the table is something like:

Then he wanted the script to run for:

And then in the second iteration wanted to run for:

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

Here’s what he wanted to achieve:

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?

Now, let’s build a logic for the above problem:

Solution:

To solve this we 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 fetching 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;
}

Output: