color fill with conditional formatting not saved to excel

Issue #371 resolved
willie wonka created an issue

so I tried the example from the docs from here: http://openpyxl.readthedocs.org/en/latest/formatting.html

with the code:

ws.conditional_formatting.add('C2:C10',
       CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))

which i edited to

ws.conditional_formatting.add('C2:C10',
      CellIsRule(operator='lessThan', formula=['0'], stopIfTrue=True, fill=redFill))

when i run this i can see in excel under manage rules that the rule is correctly added but the format is set to No Format Set. I dont understand why this isnt saved to excel and this seems to be a bug.

Comments (19)

  1. willie wonka reporter
    __author__ = 'williewonka'
    
    from openpyxl import Workbook
    from openpyxl.styles import PatternFill
    from openpyxl.formatting import CellIsRule
    
    wb = Workbook()
    sheet = wb.get_active_sheet()
    
    for i in range(1,11):
        sheet.cell(row=i,column=1).value = i
    
    redFill = PatternFill(start_color='FFEE1111', end_color='FFEE1111', fill_type='solid')
    sheet.conditional_formatting.add('A1:A10',CellIsRule(operator='lessThan',formula=['5'],stopIfTrue=True, fill=redFill))
    wb.save("test.xlsx")
    

    with this code the bug is reproducable with a 100% rate. if you go in excel to home > conditional formatting > manage rules the rule is there but no color is assigned to it.

  2. CharlieC

    Thanks for the example. It is different to #332 and looks quite simply like the format you're defining is not being saved which means I broke something.

    @amorris fancy having a look to see what broke?

  3. willie wonka reporter

    yeah its just that it doesnt save it to the excel file because the module itself doenst give any errors or anything. i thought it was a bit like #332 ? how is it diferent.

  4. CharlieC

    #332 related to loading files with existing conditional formatting and the way in which fills are handled: for normal formatting you have to explicitly set a fill but conditional formats default to a solid fill. At least that's how Excel appears to behave. We have been reading fills without an explicit fill as having no fill, no we just ignore the attribute.

    In your case it looks like the formatting is not being stored in right attribute in order to be written.

  5. willie wonka reporter

    aha in that way. interesting stuff. i hope you can fix this. would be very nice. is there anything i can do?

  6. CharlieC

    Yes, looking at the code it looks like sheet.conditional_formatting.setDxfStyles(wb) needs calling before the file is saved as this will add the various rules to the workbook. Calling it manually seems to solve the problem.

  7. willie wonka reporter

    so the save function is at fault. if just that is at fault than a simple patch to the save function should fix this right?

  8. CharlieC

    Actually, it's the docs. I think I removed the line about having to call .setDxfStyles(wb) per worksheet.

    This could happen automatically but a simple patch doesn't quite do the trick, though you're welcome to try. I'll update the docs in the meantime.

  9. willie wonka reporter

    oh you need to call it on the sheet. yeah in the save function it has to be called per sheet than. docs change is indeed better than haha

  10. CharlieC

    Actually, the docs already contain the note:

    # Before writing, call setDxfStyles before saving when adding a conditional format that has a font/border/fill
    
  11. olivier appere

    Hi, I try this code but same problem, no format in excel (I'm using openpyxl v2.1.1):

    font = Font(name='Arial',size=10,bold=False,color=RED)
    ws.conditional_formatting.add('C3:C%s'%(row -1),CellIsRule(operator='equal',formula=['NOK'], stopIfTrue=True, font=font))
    ws.conditional_formatting.setDxfStyles(wb)
    
  12. Log in to comment