How to get the row index of last non-empty cell for a specific column name(passed as an argument) from an excel sheet using java?

Question

Abstract of the module which gets book name, sheet name and column name as arguments and expecting the module to return the desired row index.

public int getExcelData(String WBookName, String sheetName, String columnName) {

    int colNum = -1;
    int rowIndex = -1;
    InputStream inputStream =  getClass().getClassLoader().getResourceAsStream("/"+WBookName+".xls");
    try {
        excelWBook = new XSSFWorkbook(inputStream);
        Log.info(WBookName+"Excel File loaded Successfully");
    } catch (IOException e) {
        Log.fatal("Unable to load"+WBookName+" Excel File");
        e.printStackTrace();
    }
    excelWSheet = excelWBook.getSheet(sheetName);
    row = excelWSheet.getRow(0);

    for(int i=0; i<row.getLastCellNum();i++) {
        if(row.getCell(i).getStringCellValue().trim().equals(columnName)){
            colNum = i;
        }
    }
    {
        //code for getting last non-empty row for a columnName (Possible using Iterator?)
    }
    return rowIndex;

Answer 1

As Apache POI has the method Sheet#getLastRowNum(), I would use a for-loop going backwards from excelWSheet.getLastRowNum() to 0 and ask each row for a presence of a value.

The code would be something like (you should try it yourself, I am now just "programming in the browser") this:

for (int rowNum = excelWSheet.getLastRowNum(); rowNum >= 0; rowNum--) {
    final Row row = excelWSheet.getRow(rowNum);
    if (row != null && row.getCell(colNum) != null) {
        rowIndex = rowNum;
        break;
    }
}