CODEWITHSUNDEEP
openpyxl
Mad Physicist
Mad Physicist

Reputation: 114230

Determine if worksheet is empty in openpyxl

I have an application where I write to a worksheet to the last column + 2 if there is already data and to the last column + 1 if the worksheet is empty. I get what I think is an empty worksheet as follows:

from openpyxl.workbook.workbook import Workbook
book = Workbook()
sheet = book.active

When I do sheet.max_row and sheet.max_column, I get 1 for both properties. I would like to get zero, so I do the following:

if sheet.max_row == 1 and sheet.max_column == 1 and not sheet['A1'].value:
    start_col = 1
else:
    start_col = sheet.max_column + 2

Having to check the value of the cell seems a bit overkill, not to mention error-prone. I initially expected the following to work:

if sheet.max_column == 0:
    start_col = 1
else:
    start_col = sheet.max_column + 2

Is there a reason max_row and max_column are always >= 1? Is this a bug, an intentional feature or a reasonable side-effect of something else? Finally, is there a workaround (e.g. something like sheet.isempty()).

By the way, I found the following comment while browsing the bug tracker: https://bitbucket.org/openpyxl/openpyxl/issues/514/cell-max_row-reports-higher-than-actual#comment-21091771:

Rows with empty cells are still rows. They might be formatted for future use or other stuff, we don't know. 2.3 has slightly improved heuristics but will still contain rows of empty cells.

This (and another comment I can no longer find a link for) lead me to believe that the first cell effectively always exists. In this case, is it worth submitting a feature request?

Upvotes: 3

Views: 4721

Answers (2)

Moriartyalex
Moriartyalex

Reputation: 74

Since I do not find any direct answer I am adding this.

from openpyxl import load_workbook
book = load_workbook('sample.xlsx')
sheet = book.active

if [cell.value for cells in sheet.rows for cell in cells] == [None]:
    print ("Sheet is empty")

**sheet.rows can also be replaced with sheet.column, both are generators to iterate cells by rows and columns respectively. So using the list comprehension to list the values would help us to check empty sheets.

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19497

The only reliable way to determine if a worksheet is "empty" is to look at the _cells attribute. However, this is part of the internal API and liable to change. In fact it is almost certain to change.

The max_row and max_column attributes are used internally for counters and must be 1 or more.

A feature request without relevant code and tests would be rejected and I'm fairly sceptical about the idea of "empty" worksheet in general.

Upvotes: 6

Related Questions