Diverging color DataBar (conditional formatting) - openpyxl pandas

Question

How can I add a conditional formatting rule on a column of numeric data, using pandas and openpyxl, which would use a different colour for the negative against positive values? Ultimately the exact same result one would get when applying the data bar rule in Excel on a column.

Expected output:

---

I am fairly successful in applying conditional formatting to a column that contains only positive values, using:

from openpyxl.formatting.rule import DataBar

rule_pos = DataBarRule(start_type="percentile",start_value=10, end_type="percentile",
        end_value="90", color="FF638EC6", showValue="None", minLength=None,maxLength=None)

wb["sheet_name"].conditional_formatting.add("K44:K48", rule_pos)

which returns succesfully:

---

In Excelwriter this can be achieved with conditional_format utilising 'min_color': 'red', and 'max_color':'green' parameters.

I tried several different methods, for example applying both negative and positive rules with green / red data bars respectively, or adjusting the parameters of openpyxl's conditional formatting. The result that I get is the same colour, but smaller bars for the negative values. This proved to be trickier than what I expected.

Answer 1

As Charlie Clark pointed out: in cases where a particular formatting is not supported by openpyxl, you have to work directly with the XML behind your Excel file.

Here's how:

1. Manually create a sample workbook with the desired conditional formatting, say:

2. Once saved, change its extension from .xlsx to .zip.
3. Open the archive and go to xl/worksheets/sheet1.xml.
4. Search for the conditionalFormatting tag. Copy and save the entirety of it (if there is one – not all formattings include it). In my case:

<conditionalFormatting sqref="A1:A7"><cfRule type="dataBar" priority="1"><dataBar><cfvo type="min"/><cfvo type="max"/><color rgb="FF638EC6"/></dataBar><extLst><ext uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:id>{1A059263-86EF-43A0-9D4B-099ED0C104BA}</x14:id></ext></extLst></cfRule></conditionalFormatting>

5. Modify sqref so that it points to the range you wish to format.

6. Repeat step 4, but this time focus on the extLst tag. In my case:

<extLst><ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:conditionalFormattings><x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><x14:cfRule type="dataBar" id="{1A059263-86EF-43A0-9D4B-099ED0C104BA}"><x14:dataBar minLength="0" maxLength="100" border="1" negativeBarBorderColorSameAsPositive="0"><x14:cfvo type="autoMin"/><x14:cfvo type="autoMax"/><x14:borderColor rgb="FF638EC6"/><x14:negativeFillColor rgb="FFFF0000"/><x14:negativeBorderColor rgb="FFFF0000"/><x14:axisColor rgb="FF000000"/></x14:dataBar></x14:cfRule><xm:sqref>A1:A7</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings></ext></extLst>

7. Modify xm:sqref to fit your scenario.

8. One more missing piece of information is the "code name" of the target worksheet. Take a look inside the VBA editor (Alt + F11):

• target sheet – user-friendly name (visible to end users)
• target_ws – code name

Important: it's necessary to set a custom code name, otherwise the default one might not make it into the final xml. Just select the sheet under "Microsoft Excel Objects", press F4 and fill in the (Name) property.

9. Now it's showtime:

from zipfile import ZipFile, ZIP_DEFLATED

# Here are the strings we've extracted and modified before.
cf_str = '<conditionalFormatting sqref="A1:A5"><cfRule type="dataBar" priority="1"><dataBar><cfvo type="min"/><cfvo type="max"/><color rgb="FF638EC6"/></dataBar><extLst><ext uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:id>{1A059263-86EF-43A0-9D4B-099ED0C104BA}</x14:id></ext></extLst></cfRule></conditionalFormatting>'
ext_str = '<extLst><ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:conditionalFormattings><x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><x14:cfRule type="dataBar" id="{1A059263-86EF-43A0-9D4B-099ED0C104BA}"><x14:dataBar minLength="0" maxLength="100" border="1" negativeBarBorderColorSameAsPositive="0"><x14:cfvo type="autoMin"/><x14:cfvo type="autoMax"/><x14:borderColor rgb="FF638EC6"/><x14:negativeFillColor rgb="FFFF0000"/><x14:negativeBorderColor rgb="FFFF0000"/><x14:axisColor rgb="FF000000"/></x14:dataBar></x14:cfRule><xm:sqref>A1:A5</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings></ext></extLst>'
code_name = 'target_ws'

# substitute input.xlsx and output.xlsx with actual paths
source_archive = ZipFile('input.xlsx', 'r')
final_archive = ZipFile('output.xlsx', 'w', ZIP_DEFLATED, allowZip64=True)

# loop through the sheets, looking for the target one
for xml_file in source_archive.namelist():
    xml_str = source_archive.read(xml_file)
    xml_str = xml_str.decode("utf-8")
        
    if f'sheetPr codeName="{code_name}"' in xml_str:
        # insert cf_str right after "</sheetData>"
        xml_str = xml_str.replace('</sheetData>', f'</sheetData>{cf_str}')

        # insert ext_str right before "</worksheet>"
        xml_str = xml_str.replace('</worksheet>', f'{ext_str}</worksheet>')

    # write the xmls into the final_archive
    final_archive.writestr(xml_file, bytes(xml_str, "utf-8"))
        
source_archive.close()
final_archive.close()

DISCLAIMER – in the interest of simplicity, the above code presumes that neither <conditionalFormatting ... nor <extLst> ... bits are present in the source XML. Otherwise you would need to modify the string substitutions so that they inject the necessary substrings into the existing tag contents without causing conflicts.