How to iterate over columns to get row labeled data in Excel?

Question

I am writing a Java program in which I want to retrieve data which has row headers. My Excel sheet looks like below:

I have written a following simple Java code to retrieve values sequentially based on row headers:

....
try
    {
    fileSystem = new POIFSFileSystem (inpStrm);

    HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);
    HSSFSheet         sheet    = workBook.getSheetAt(0);
    int column = 1;
    for(int i = 0;i <=3;i++){

        for(int j = 0;j<=3;j++){
            HSSFRow row = sheet.getRow(j);
            System.out.print(row.getCell(column).getStringCellValue() + "    ");
        }
        System.out.println("");
        column++;
    }
    }
    catch(Exception e){
        e.printStackTrace();
    }
....

And after running code my output comes like below:

P1    M    C1    Hyderabad    
P2    M    C2    Pune    
P3    F    C3    Pune    
P4    M    C4    Hyderabad 

Now is there any easy and feasible way to do the same for large Excel sheets (50 row headers)?

If I use the POGO class having above four properties then how can I get list of objects with values from Excel?

Answer 1

Now is there any easy and feasible way to do the same for large Excel sheets (50 row headers)?

You could use the method sheet.getLastRowNum() as condition in your for loop to iterate over all rows and the method row.getLastCellNum() in a second loop to iterate over all cells in a specific row.

for(int rowNumber = 0; rowNumber < sheet.getLastRowNum(); rowNumber++) {
    HSSFRow row = sheet.getRow(rowNumber);

    for(int columnNumber = 0; columnNumber < row.getLastCellNum(); columnNumber++) {
        HSSFCell cell = row.getCell(columnNumber);
        if(cell != null) {
            // do something with the cell
        }
}

Important: The code above loops first through each column and then through each row.