excel-formulaconditional-formattingxlsxwriter

xlsxwriter conditional formatting formula only works after manual save

Here's the code I'm using:

  row_sheet.conditional_format(f'$A$4:$A$10', {'type': 'formula',
                                                     'criteria':'=ISFORMULA()',
                                                     'format': cell_format})

It works perfectly but only after I open it in Excel, pick Edit, and Save without making any changes. If I don't do that, the formatting is not applied.

So here's what it looks like in Excel: enter image description here

and the rule editor:

enter image description here

When I click OK without making any changes the formatting is applied properly. Can anyone help me fix this?

Solution

  • There are 2 issues with the formula in the sample code. The first is that the formula should refer to a cell as shown in the Excel dialog (ISFORMULA(A4)) and the second is that ISFORMULA() is a Excel Future Function so it needs to have an additional prefix like this _xlfn.ISFORMULA(A4).

    After resolving these issues the code should give the correct output:

    import xlsxwriter

workbook = xlsxwriter.Workbook("conditional_format.xlsx")
row_sheet = workbook.add_worksheet()


# Add a format. Green fill with dark green text.
cell_format = workbook.add_format({"bg_color": "#C6EFCE", "font_color": "#006100"})

# Add some sample data to run the conditional formatting against.
row_sheet.write("A4", 10)
row_sheet.write("A5", "=1+1")
row_sheet.write("A6", 30)
row_sheet.write("A7", 40)
row_sheet.write("A8", "=1+1")
row_sheet.write("A9", 60)

row_sheet.conditional_format(
    "A4:A10",
    {"type": "formula", "criteria": "=_xlfn.ISFORMULA(A4)", "format": cell_format},
)

workbook.close()

    Output:

    enter image description here