I have two columns in a dataframe named FirstName and LastName.
I need to make the font color of any cell's text in the FirstName column red if any cell in the LastName is not blank.
writer = pd.ExcelWriter(fileName, engine='xlsxwriter')
df.to_excel(writer,'Sheet1', index_label=None, index=False)
workbook = writer.book
redFont = workbook.add_format({'font_color': '#9C0006'})
worksheet = writer.sheets['Sheet1']
worksheet.conditional_format(1, 0, 9999999, 0,
{'type': 'formula',
'criteria': '=ISBLANK($B1)',
'format': redFont})
writer.save()
I do not get errors but the font color does not change. I can not figure out what I am doing wrong. Any ideas?
Thank you!
CodePudding user response:
There are a couple of small errors in the code: the range is bigger than the maximum range allowed by Excel (so XlsxWriter rejects the conditional format) and also if the range starts in cell A2
you should apply the conditional format using the reference cell $B2
. Also I think the logic should be reversed to match cells that are not blank
It should be something like this:
import pandas as pd
fileName = 'test.xlsx'
df = pd.DataFrame({'FirstName': ['Anna', 'Bob', 'Cian', 'Dora'],
'LastName': ['Aran', '', '', 'Dodd']})
writer = pd.ExcelWriter(fileName, engine='xlsxwriter')
df.to_excel(writer, 'Sheet1', index_label=None, index=False)
workbook = writer.book
redFont = workbook.add_format({'font_color': '#9C0006'})
worksheet = writer.sheets['Sheet1']
worksheet.conditional_format(1, 0, 1048575, 0,
{'type': 'formula',
'criteria': '=ISBLANK($B2)=FALSE',
'format': redFont})
writer.save()
Output: