Conditional formatting and validation don't work together

Issue #350 resolved
girl spider created an issue

Hi,

It seems if there is conditional formatting, then cell validation won't work, and vise versa.

Attached is my simple program. I can get either validation to work or conditional formatting to work, but not both. When I have both on, the program will run, but the generated excel file is messed up and won't open. If I repair and open, I get nothing.

Comments (10)

  1. CharlieC

    1.8 is no longer under development. Do you have the same problems with 2.x?

    The code for 2.1 looks slightly different:

    from openpyxl import Workbook
    from openpyxl.datavalidation import DataValidation, ValidationType
    from openpyxl.styles import Color, Border, Alignment, PatternFill
    
    wb = Workbook()
    
    dv = DataValidation(ValidationType.LIST, formula1 = '"PASS, FAIL"', allow_blank = False)
    wb.active.add_data_validation(dv)
    
    pullDownCell = wb.active['B5']
    pullDownCell.value = "FAIL"
    dv.add_cell(pullDownCell)
    
    myFill = PatternFill(start_color = 'FFFFFF00',
                         end_color= 'FFEE1111',
                         fill_type = 'solid')
    
    formatting = wb.active.conditional_formatting
    
    formulaStr = 'B5=\"FAIL\"'
    styleId = formatting.addDxfStyle(wb, None, None, myFill)
    rangeStr = 'B5'
    
    formatting.add(
            rangeStr,
            {'type': 'expression', 'dxfId': styleId, 'formula': [formulaStr], 'stopIfTrue': '1'}
            )
    
    wb.save("GuineaPig.xlsx")
    

    @Adam Morris I'm not sure of the replacement method for addDxfStyle

  2. Adam Morris

    At the moment, you call ws.conditional_formatting.setDxfStyles(wb) when you're done to set the styles.

    wb = Workbook()
    dv = DataValidation(ValidationType.LIST, formula1 = '"PASS, FAIL"', allow_blank = False)
    wb.active.add_data_validation(dv)
    pullDownCell = wb.active['B5']
    pullDownCell.value = "FAIL"
    dv.add_cell(pullDownCell)
    
    formatting = wb.active.conditional_formatting
    formulaStr = 'B5=\"FAIL\"'
    rangeStr = 'B5'
    myFill = PatternFill(start_color='FFEE1111', end_color='FFEE1111', fill_type='solid')
    
    formatting.add(rangeStr, FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=myFill))
    
    formatting.setDxfStyles(wb)
    wb.save("GuineaPig.xlsx")
    
  3. girl spider reporter

    Do I have to use 2.1? Tried Adam's code with 2.0.5 and got

    Traceback (most recent call last): File "temp.py", line 15, in <module> myFill = PatternFill(start_color='FFEE1111', end_color='FFEE1111', fill_type='solid') File "/Library/Python/2.7/site-packages/openpyxl/styles/fills.py", line 94, in init self.fgColor = fgColor File "/Library/Python/2.7/site-packages/openpyxl/styles/hashable.py", line 54, in setattr return object.setattr(self, args, *kwargs) File "/Library/Python/2.7/site-packages/openpyxl/descriptors/init.py", line 33, in set raise TypeError('expected ' + str(self.expected_type)) TypeError: expected <class 'openpyxl.styles.colors.Color'>

  4. CharlieC

    Please provide the sample code you're using with 2.0.5.

    2.1 is due for release shortly but we may still backport fixes to 2.0.x

  5. girl spider reporter

    from openpyxl import Workbook

    from openpyxl.datavalidation import DataValidation, ValidationType

    from openpyxl.styles import Color, Border, Alignment, PatternFill

    from openpyxl.formatting import FormulaRule

    wb = Workbook()

    dv = DataValidation(ValidationType.LIST, formula1 = '"PASS, FAIL"', allow_blank = False)

    wb.active.add_data_validation(dv)

    pullDownCell = wb.active['B5']

    pullDownCell.value = "FAIL"

    dv.add_cell(pullDownCell)

    formatting = wb.active.conditional_formatting

    formulaStr = 'B5=\"FAIL\"'

    rangeStr = 'B5'

    myFill = PatternFill(start_color='FFEE1111', end_color='FFEE1111', fill_type='solid')

    formatting.add(rangeStr, FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=myFill))

    formatting.setDxfStyles(wb)

    wb.save("GuineaPig.xlsx")

  6. CharlieC

    Thanks for the code. I'm enclosing the file made using 2.1 branch. I think that works as advertised. I'll check tomorrow about backporting. Could you checkout the 2.1 branch and see if it solves your problem.

  7. Log in to comment