opepyxl not counting rows in spreadsheets?

[jjartus]

Prepare a spreadsheet with anything in cells A1..A5
Save as 'Myfile.xlsx?
run this code:

    import openpyxl

    wb = openpyxl.load_workbook('Myfile.xlsx')
    sheet = wb.get_sheet_by_name('My sheet')
    print(sheet.max_row)

Answer 5. OK

Now delete A5 in Myfile and save.
Run again the code.
Answer 5 (not 4). Why?

[buran]

As the name suggest and also described in the docs:

max_row - The maximum row index containing data (1-based)

i.e. that is not number of rows with data, but max row index (1-based)

[jjartus]

Excuse me buran, but it seems that you have not understood my question. When you delete A5 certainly you shorten column A, but this is irrelevant here. What is relevant is that there are no filled cells in row 5. This is what the user sees, but openpyxel does not. And still further.
There are two ways to erase A5. You can select the cell and delete it, or yo can selecte another empty cell, for instance A6, and drag the cross in the the lower right corner to A5. There are 4 rows in both cases. Openpyxl detects the second case, but not the first. This is an anomally that could amount to a bug.
I am not able to determine the exact cause, but I guess it could come either from a cache efect, or from the different inner workings of excel in both cases. It could be also dependent from the excel version. Mine is 2006.
Hope I have made myself clear.

[buran]

Indeed, I misunderstood your question. I was under impression you clear content in cell A1. My bad, sorry.

Now, however, I tried to replicate your problem and was not able to do so. Using openpyxl 2.6.3. Openpyxl correctly detects max row number, regardless how A5 is deleted - select the cell and press Del, drag empty cell from row 6, delete the cell and move cells up, delete entire row, etc.

Are you sure you save the workbook after you delete the A5 cell/content? Make sure you don't have something in rest of the cells...
I doubt excel version will have any effect (i.e. openpyxl does not rely on MS Excel to read the file). different/older openpyxl may be the reason (if there was bug in a version before 2.6.3)

[jjartus]

I have checked and recheked the operation, and I stand by what I wrote. There is no possibility of error. I have 3 windows open in my screen: the 2 excel files, and the Pyscripter window. I created the first excel file, and saved it. Then saved it 'save as' with a new name that differs from the first one just in one character. Then I perform the 2 delete operations in parallel in both files and save both files. Then I run the script in both files. What I have now in my screen is 2 visually identical files with a different row count in python. I also saved the wrong file 3 more times just for if this could force to empty a memory caché; to no avail.No mistake possible.
The only correction I make is the excel version I have: 2016, not 2006.
Thanks for your interest.

[buran]

what openpyxl version do you use? Anyone else able to confirm OP problem?
I also use excel 2016 in my office

[jjartus]

I use openpyxl 3.0.0,
python 3.6.0.0 x64
excel 2016
windows 7

[buran]

I cannot test on windows till Monday, but with openpyxl 3.0.0 on Linux I cannot reproduce the error - it works as expected anyway you delete the content/cell A5. Sorry... Maybe someone else would be able to replicate it. IMHO OS or excel version would not matter. Maybe openpyxl version, but both 2.6.3 and 3.0.0 work as expected.

[jjartus]

Ok.
To make things simpler, just type a letter in cell A1 and delete it. openpyxl finds one row in an empty sheet.

[buran]

I was about to suggest you report the problem if you think you have found a bug, but found that this was discussed on number of occasions, e.g.:
https://bitbucket.org/openpyxl/openpyxl/...han-actual

See author's comments. It looks at sheet dimensions property and obviously styles, fonts, cell format, etc. also count, not only cell content

By the way, your latest example with empty sheet works exactly as excel would do in VBA, i.e. last row on empty sheet is 1.