Checking for empty cells with OpenPyXl

Question

I've poured over numerous versions of this same question, but simply cannot find a working solution for looping through a group of cells and checking for empty ones.

Here's my code:

wb = openpyxl.Workbook(write_only=False)
sheet = wb.get_sheet_by_name('Sheet')
for i in range(1, 20):
    if sheet.cell(row=i, column=1).value == None or 'None':
        print('Space' + str(i))
        sheet.cell(row=i, column=1) = i
    else:
        pass

wb.save('filename.xlsx')

But for some reason it writes to the empty cells AND overwrites the cells which had values written in them.

I comment out the loop I find that if I run the code it erases everything that was manually written into the spreadsheet to begin with. Maybe that's part of the problem?

Also I'm using LibreOffice if that makes a difference

The second part of your if statement will always evaluate to True which is why you overwrite existing cells. — Charlie Clark

Drazen Urch Drazen Urch · Accepted Answer · 2016-11-11T12:28:47

What Charlie mentions is of course correct.

Non empty strings in Python evaluate to True, so you are actually testing if cell value is None or if bool('None'), and the later is always True, hence your condition always evaluates to True.

To address your specific case I'm not sure why you are trying to test for an empty cell with 'None'.

In case you really want to do this your condition should look like this:

if sheet.cell(row=i, column=1).value in [None,'None']

If you want test for None or an empty string, so None or '' then of course your condition should be:

if sheet.cell(row=i, column=1).value in [None,'']

Hope this gets you on the right track..

Checking for empty cells with OpenPyXl

1 Answers