Search code examples
pythonexcelpandasopenpyxlxlsxwriter

How to map color names with their HEX to make a conditional formatting

I have the dataframe below:

import pandas as pd

df = pd.DataFrame({'ID': ['ID001', 'ID002', 'ID003', 'ID004', 'ID005', 'ID006'],
                   'Color': ['Red', 'Green', 'Blue', 'Green', 'Yellow', 'Purple']})

      ID   Color
0  ID001     Red
1  ID002   Green
2  ID003    Blue
3  ID004   Green
4  ID005  Yellow
5  ID006  Purple

And I'm trying to get this kind of Excel spreadsheet output :

enter image description here

By using xlsxwriter, the code below that I made has no effect :

with pd.ExcelWriter('TestColor_SpreadSheet.xlsx') as writer:
    df.to_excel(writer, index=False, sheet_name='TestWorksheet')

    workbook = writer.book
    worksheet = writer.sheets['TestWorksheet']

    format_red = workbook.add_format({'bg_color':'#FF0000'})

    worksheet.conditional_format('B1:B7', {'type': 'cell',
                                        'criteria': '=',
                                        'value': "Red",
                                        'format': format_red})

Do you have any propositions please on how to do this conditional formatting in xlsxwriter or maybe even with pandas ?

Solution

  • Matplotlib can convert color names to RGB and hex values for you

    >>> from matplotlib import colors
>>> 
>>> colors.to_rgb('blue')
(0.0, 0.0, 1.0)
>>> 
>>> colors.to_hex('blue')
'#0000ff'