Xlsx Writer Critera If Blank

Question

Given this format:

format0 = workbook.add_format({'bg_color': 'none'})

I'd like to apply it (no background color) if the cell is blank. Here's what I've tried so far:

worksheet.conditional_format('B2:B14', 
{'type':'cell', 
'criteria': '=isblank()=True', 
'format': format0
})

But I keep getting this error:

KeyError: 'value'

I'm pretty sure I'm not using a correct entry for 'criteria', but I'm not sure how to do it.

Thanks in advance!

Shandashandee

Answer 1

The border of the blank cells are not visible if you use white background color as given in the answer above.It can be modified to format0 = workbook.add_format({'bg_color':'white','border':1,'border_color':'#D5DBDB'})to give a border to the blank cells that looks similar to default excel cells.

Unhair

Answer 2

One solution:

It appears that setting the color to 'none' produces blue cells, for some reason. So instead, I opted to color blank cells white:

format0 = workbook.add_format({'bg_color': 'white'})

Then, by reading the documentation (what a great idea!):

worksheet.conditional_format('B2:B14', {'type':'blanks', 'format': format0})

I put this before other conditional formats for the same column.

Shandashandee

Thanks dude, this was useful! Was searching for 'empty' instead of 'blanks'. For reference, here is the page in the docs: xlsxwriter.readthedocs.io/working_with_conditional_formats.html – Gasometry

How to give a range such that the whole sheet looks like white colored? – Calder

@JayPatel, You can specify some big number in this worksheet.conditional_format('A1:ZZ100', {'type':'blanks', 'format': format0}) – Xe