Applying a single conditional formatting rule to multiple ranges

Issue #812 resolved
EightAndAHalfTails
created an issue

Excel supports applying a single conditional formatting rule to multiple cell ranges at once, for example by using the notation "A1:D10,A20:D30".

However, when I try to pass a range string like the above to Workbook.conditional_formatting.add(), the formatting simply does not appear in the final file.

Note that this is not the same as applying two separate rules to each range individually. If you wanted to have a ColourScaleRule across two separate datasets, only the one-rule method would preserve the same colour for the same values. Having two rules would make each rule scale to its own data.

Another user has mentioned this bug on stackoverflow here

Is there a way to make this work that's non-obvious, or is this a bug?

Comments (4)

  1. CharlieC

    Thanks for the report. There are several instances in Excel where multiple ranges can be passed in so a generic solution would be nice but I'm not quite sure of the API.

    At the moment I think that supporting this would require considerable refactoring of the conditional formatting code. PR (based on 2.5) would be welcome.

  2. EightAndAHalfTails reporter

    I had a look at the xml generated when I set up a multiple-range rule in excel. The sqref field contains "A1:D10 A20:D30". i.e. it uses a space as the delimiter rather than a comma. By passing a string using this notation to the openpyxl formatter, a list of ranges works as expected.

    I guess this means all that's needed is an update to the documentation to inform users to use this notation rather than the one they might be used to using in excel itself.

  3. CharlieC

    Glad it's working for you, even if this is more undocumented fucking around by Excel. :-/

    From what I remember of the implementation this might have trouble when reading the files with this kind of definition, though Excel often disaggregates rules for reasons known only to itself.

    If you can roundtrip a file then we would only need an update to the documentation, though I'd like to implement this correctly at some point.

  4. Log in to comment