Conditional formatting - rules kept, background fills lost in 2.0.3

Issue #332 resolved
Chris Petersen
created an issue

As stated. I pull in an xlsx file with conditional formatting rules that change cell backgrounds. In 2.0.3, the rules are still there after a save (thank you! a great improvement from 1.6.2!), but all of the backgrounds now appear to be white.

Comments (13)

  1. CharlieC

    You can thank @Adam Morris for the all the conditional formatting stuff.

    This is possible due the need for conditional formatting to catch up with the new styles. I know that I recently fixed colour serialisation in the 2.1 branch. Can you try that and let me know if it's better? If not, please supply a sample input file and code.

    @Adam Morris Are you up to speed on the changes, yet?

  2. Chris Petersen reporter

    A big thank you to Adam Morris! :-) I'd be lying if I claimed to be up to speed with the changes yet. Luckily, my code is only applying cell-based, unconditional formatting in simple ways. The conditional stuff comes to me from other people's work. I'll grab the 2.1 branch and see how things go with that.

    Thanks!

  3. Adam Morris

    The issues with the background fills sounds familiar - my mind is fuzzy, but I think the conditional formatting needs the color to be written in a particular way... I believe there was something wrong with it in the last 1.8 branch.

    Charlie - still working on migrating my project to 2.1 - I've got the styles mostly up and working. Biggest hold-up at the moment is reviewing code to make sure my row/column references are updated to being 1-based instead of 0-based. Hoping to get it fully integrated within the next week.

  4. CharlieC

    @Adam Morris I didn't get any real problems with the conversion of code I made but I could have missed some things. The colours are now much simpler: c = Colour(**attrs) and pretty much Element('c', dict(c) to serialise. All the aRGB/indexed/theme/tint shit is handled by the class.

    Please add tests for any stuff that is breaking because it means that tests are missing. I broke out some of the writing tests from formatting/tests and copied them to writer/tests/test_lxml.py. If we can create, read, modify and write stuff we don't really need tests to check round-tripping.

  5. Laureline Guerin

    I had the same issue with my files. If I compare the source file and the file modified with openpyxl, there is something weird in styles.xml : patternFill elements have a patternType="none" attribute.

    If I patch _write_dxfs in writer/styles.py something like this :

    -  if f.fill_type:
    +  if f.fill_type and f.fill_type != 'none':
    

    patternType="none" is not added anymore and Excel displays my colors as expected.

  6. Adam Morris

    It looks like excel doesn't always write the patternType attribute for dxf styles.

    Looking at standards, for regular fills, the standard states that a missing patternType means patternType='none'. But I can't find any documentation on dxf pattern fills. At least on Office 2011 on a mac, it's reading and writing a missing patternType attribute and treating it as solid for the DXF styles.

    <dxfs count="2">
            <dxf>
                <fill>
                    <patternFill>
                        <bgColor rgb="FF92D050"/>
                    </patternFill>
                </fill>
            </dxf>
    
  7. Log in to comment