sheet.getLastRowNum() function gives invalid count with Apache POI 3.9

Question

I am using Apache POI 3.9 for xls and xlsx file processing.

As per the logic, I want to iterate over each row and want to collect data for further processing. For that I am using sheet.getLastRowNum() to retrieve number of rows in the xls sheet.

But it seems that sheet.getLastRowNum() gives wrong count if number of records are more than 10.

It works fine if total number of rows are 10. Otherwise it gives result deducted by one. i.e If there are 15 row in the sheet then It gives 14 as Last Row number.

Can Anyone suggest me,How to solve this problem ??

Here is the code that i am using to collect the data ...

public static List<LinkedList<String>> populateExcelSheetContentWithHeader(Sheet sheet,int columnsCount,int rowsCount) throws Exception{
        Row row = null;

        List<LinkedList<String>> excelFileRecordsList = new LinkedList<LinkedList<String>>();                   
        int emptyColCount = 0;      

        String freeTextData = null;
        LinkedList<String> excelFileDataList =null;

        int actualRows = sheet.getLastRowNum();

        if (actualRows < rowsCount) {
            rowsCount = actualRows;
        }


        ///ITERATE OVER EACH ROW AND POPULATE THE DATA
        for(int index=0;index<rowsCount;index++){

            row = sheet.getRow(index);

            if(row!=null){
                emptyColCount = 0;
                excelFileDataList =new LinkedList<String>();

                for(int colIndex=0;colIndex<columnsCount;colIndex++){
                    freeTextData = "";

                    if(isEmptyCell(row.getCell(colIndex))){
                        emptyColCount++;
                    }else{
                        freeTextData = getCellValue(row.getCell(colIndex),false);
                    }

                    //ADD TEXT DETILS TO THE LIST
                    excelFileDataList.add(freeTextData);

                }

                //CHECK FOR END OF FILE
                if(emptyColCount != columnsCount){
                    excelFileRecordsList.add(excelFileDataList);
                }else{
                    break;
                }               
            }           

        }
        return excelFileRecordsList;
    }

Any suggestion is appreciated .

nope .. this is the different case. Here I am getting the count perfectly If number of rows in excell file is less than 10. It gives wrong count (count that is deducted by one) if number of rows are greater than 10. — Gunjan Shah, May 27 '13 at 12:21
ˋgetLastRowNumber()ˋ is 0-based, so it gives the wrong result below 10 rows. What is the result of ˋgetPhysicalNumberOfRows()ˋ for the given sheets? Sometimes rows are counted even when they are visually empty ... are the files manually edited or generated? try to remove the first 10 empty rows and check if you get the same count as before — kiwiwings, May 27 '13 at 13:56

score 0 · Answer 1 · answered Mar 17 '16 at 13:54

it is possible that you are converting from CSV or you did any kind of modifications (copy or paste) inside your xls? I had the same exact behaviour when I did some changes like I mentioned. To fix it I had to copy the original again. Sorry for not providing a real solution, I just say what happened. Maybe that helped

sheet.getLastRowNum() function gives invalid count with Apache POI 3.9

1 Answers1