Openpyxl check for empty cell

Question

openpyxl seems to be a great method for using Python to read Excel files, but I've run into a constant problem. I need to detect whether a cell is empty or not, but can't seem to compare any of the cell properties. I tried casting as a string and using "" but that didn't work. The type of cell when it is empty is None, or NoneType but I can't figure out how to compare an object to that.

Suggestions? I understand openpyxl is under development, but maybe this is more a general Python problem.

Once again, a little more searching has yielded the answer: use `if cell.value is None` — MechEngineer, Oct 26 '11 at 20:07

score 30 · Answer 1 · answered Dec 06 '11 at 22:46

30

To do something when cell is not empty add:

if cell.value:

which in python is the same as if cell value is not None (i.e.: if not cell.value == None:)

Note to avoid checking empty cells you can use

worksheet.get_highest_row()

and

worksheet.get_highest_column()

Also I found it useful (although might not be a nice solution) if you want to use the contents of the cell as a string regardless of type you can use:

unicode(cell.value)

answered Dec 06 '11 at 22:46

Aron Kisdi

544
4
8

20

**Watch out!** `bool(cell.values) == False` if `cell.value = 0` – Mark Mikofski Feb 20 '15 at 18:52
6

In newest openpyxl, which has removed get_highest_row and get_highest_column method. They have been replaced by max_row and max_column property – Shoham Sep 17 '17 at 10:49
9

This answer is incorrect. Checking this way will treat zeros as empty cells. – alexis Mar 23 '18 at 12:57

score 20 · Answer 2 · edited Dec 27 '15 at 08:22

20

This worked for me.

if cell.value is None:
    print("Blank")
else:
    print("Not Blank")

edited Dec 27 '15 at 08:22

Tomasz Jakub Rup

10,502
7
48
49

answered Dec 27 '15 at 08:18

Mikku

6,538
3
15
38

is there a command-line shortcut that you guys know that can replace blank cells with ""? THANK YOU! – Cmag Feb 02 '20 at 20:39
1

I have a worksheet, where the cell looks blank, both `bool(not cell.value is None)` and `bool(not cell.value == None)` give True, but `print(cell.value)` gives nothing, and `print(len(cell.value))` gives 0, totally confusing – Heinz May 15 '20 at 20:25

score 4 · Answer 3 · answered May 19 '18 at 11:30

The

if cell.value is None:

is the best option to use for this case.

if you wish to use it in an iteration, you can use it like this:

ws=wb.active
names=ws['C']
for x in names:
    if x.value is None:
        break
    print(x.value)

The problem with ws.max_column and ws.max_row is that it will count blank columns as well, thus defeating the purpose.

score -1 · Answer 4 · edited May 05 '17 at 11:38

-1

You can change the cell or cells you wish to validate Also you can code, for example:

import openpyxl

wb = openpyxl.load_workbook("file.xlsx")
sheet = wb.get_sheet_by_name('Sheet1')

if sheet.cell(row = 1, column=7).value == None:
    print("Blank") 
else:
    print("No blank")

edited May 05 '17 at 11:38

C.Fe.

325
3
11

answered Sep 08 '16 at 22:07

Felipe R Valera

43
1
6

Openpyxl check for empty cell

4 Answers4

Linked

Related