cells.getMaxrow returning wrong value


This is kind of critical issue. Attached 2 excel sheets. Just check the PayPalAccountAL tab alone. In US_Template.xlsx, when I do cells.getMaxDataRow() returns me 2 but in US_Template_2020.xlsx the same returns 1. Both attached xls although both are exactly same except column order. Below the code snippet how I parse and get the maxRow. Quick help will be greatly appreciated

Here is how I read
public Worksheet mergeALTemplate() throws Exception{
Worksheet sheet = workbook.getWorksheets().get(Sheet.PayPalAccountAL.name());
return mergeListingData(sheet, Section.PayPalAccountActivityLog);

        private Worksheet mergeListingData(Worksheet sheet, Section section ) throws Exception{
            Cells cells = sheet.getCells();
            AtomicInteger startRow = getMaxDataRow(cells);  
     protected AtomicInteger getMaxDataRow(Cells cells){
            int maxDataRow = cells.getMaxDataRow(); //this returns different value
            for(Object obj : cells.getMergedCells()) {
                CellArea cellArea = (CellArea)obj;
                if (cellArea.EndRow > maxDataRow)
                    maxDataRow = cellArea.EndRow;
            return new AtomicInteger(maxDataRow);

Archive.zip (327.4 KB)

The files “US_Template_2020 .xlsx” and “US_Template.xlsx” are different. For “US_Template_2020.xlsx”, PayPalAccountAL!B3 is empty but for “US_Template.xlsx” it is not.

So there is no issue for the different values of MaxDataRow.

I don’t understand. I see B3 is empty in both files. I see only 2 rows in both files so with starting row index 0, the expected maxDataRow must be 2 isn’t?

can you explain why in US_Template_2020.xlsx file the maxDataRow is returned as 1. It does return correctly for US_Template.xlsx as 2.

In MS Excel 2013, the B3 cell in US_Template.xlsx shows single quote '. In MS Excel 2016, B3 shows whitespace which can not be treated as empty. So the contents of two files are different anyway. Could you please verify and let us know your feedback.

I am using Excel 2020 version. I don’t see anything in that field as attached. It might be white space as you said which am. not sure as I can’t recognise empty cell and white space. How are you saying it has white space

Screen Shot 2020-09-11 at 12.16.58 AM.png (330.7 KB)

Please double click in B3 cell of both files and let us know if you notice the cursor one character ahead in US_Template.xlsx.

I see that… thanks a lot for the quick response… I appreciate it.


You are welcome.