Pandas Excelwriter Diverging Color Data Bar

Question

I've been fairly successful in conditionally formatting my excel file using pandas/excelwriter

However, I am having trouble create diverging colored databars, like the ones that can be create in excel:

I've tried to set a min and max color:

worksheet.conditional_format('G2:I80'{'type':'data_bar','min_color':'red','max_color':'green'})

But the result is this:

score 2 · Answer 1 · answered Apr 16 '18 at 13:42

2

This is now possible in XlsxWriter version 1.0.4. See the databar Conditional Formatting Options.

For you case you could do something like this:

worksheet.conditional_format('G2:I80',
                             {'type': 'data_bar',
                              'min_color': 'red',
                              'max_color':'green',
                              'data_bar_2010': True,
                              })

answered Apr 16 '18 at 13:42

jmcnamara

38,196
6
90
108

Can this be done in ```openpyxl``` ? If you happen to know – sophocles Aug 16 '22 at 16:37

score 1 · Answer 2 · answered Apr 05 '18 at 13:52

solved the problem here: https://github.com/jmcnamara/XlsxWriter/issues/501#issuecomment-378630647

There is attached worksheet.txt file that needs to be replaced
(rename file's extension from .txt to .py)

In order to use it just add: To add conditional_format just use:

worksheet.conditional_format("<RANGE>"),
     { 'guid': "<GUID>",
       'type': 'data_bar_bi_directional',
       'bar_color': '#8DB4E3',
       'min_type':'min',
       'max_type':'max'
      })

<RANGE> - replace with a range of cells that need to be extended with bi-directional data bars <GUID> - replace it with unique GUID

Pandas Excelwriter Diverging Color Data Bar

2 Answers2