CODEWITHSUNDEEP
pythonobjectcomparisonopenpyxl
MechEngineer
MechEngineer

Reputation: 1429

Openpyxl check for empty cell

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.

Upvotes: 32

Views: 134937

Answers (5)

V4ld3rr4m4
V4ld3rr4m4

Reputation: 1

In my case the fix was compare with None as String

if cell(row,col).value == "None":
    print(" Ignore cell ... ")

Upvotes: 0

Bhaskar
Bhaskar

Reputation: 1946

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.

Upvotes: 4

Felipe Valera
Felipe Valera

Reputation: 53

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")

Upvotes: 0

Mikku
Mikku

Reputation: 6664

This worked for me. 

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

Upvotes: 24

Aron Kisdi
Aron Kisdi

Reputation: 582

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)

Upvotes: 33

Related Questions