Conditional formatting saves but is not applied in Excel ver. 16
Created originally on Bitbucket by Sam Chaney
I’m using openpyxl 3.0.2 with Excel 16.0.11328.20468 64-bit. I wrote a python script to create an excel spreadsheet and apply conditional formatting. It works fine when I open the spreadsheet in LibreOffice, with the conditional formatting shown properly but when I copy it to my local machine (the machine the script runs on doesn’t have Excel installed) and open it in Excel, the conditional formatting is not applied/shown. If I look in conditional formatting → Manage Rules for the current workbook, I see all of the conditional formatting rules I created via openpyxl, but I have to double click on each one, then click OK then click Apply for them to actually be applied in the spreadsheet.
The function that I created to apply the conditional formatting is below:
def add_contains_text_rule (worksheet, search_text, fill_color, range):
fill = PatternFill(bgColor=fill_color)
differential_stye = DifferentialStyle(fill=fill)
rule = Rule( type="containsText", operator="containsText", text=search_text, dxf=differential_stye)
worksheet.conditional_formatting.add(range, rule)
I have “workbook.save(xslx_output)” at the end of the script to save any unsaved changes to the workbook. I cannot attach the full python script because it has work-sensitive information in it. I have, however attached an example of the spreadsheet the script produced showing the columns with conditional formatting that is saved but not applied. I’ve redacted all work-sensitive info from the workbook.Any help in resolving this bug would be greatly appreciated. Thanks!
Attachments: openpyxl_generated_spreadsheet_conditional_formatting_not_applied_-_sensitive_data_redacted_12-9-2019.xlsx