用颜色pd.ExcelWriter格式化Excel文件 [英] Format excel file with colors pd.ExcelWriter

问题描述

我有一个数据框df

df
Out[15]: 
           Subject ID  
StartDate                                                                    
2017-11-06        ID1  
2017-11-07        ID1
2017-11-08        ID2  
2017-11-10        ID3  

然后将其保存在excel文件中

and I save it in an excel file

writer = pd.ExcelWriter(os.path.join(folders_path,'summary.xls'))
df.to_excel(writer,'Sheet1')
writer.save() 

但是我想根据Subject ID设置行的颜色，即我希望每个ID都使用不同的颜色

However I would like to format the color of the rows according to the Subject ID , i.e. I would like a different colour for each ID

推荐答案

您必须使用"xlsxwriter"引擎并定义格式.

You have to use the 'xlsxwriter' engine and define your formats.

以下是可能的代码段:

#!/usr/bin/env python3

from datetime import date
import pandas as pd

data = [['2017-11-06', 'ID1'],
        ['2017-11-07', 'ID1'],
        ['2017-11-08', 'ID2'],
        ['2017-11-10', 'ID3']]

df = pd.DataFrame(data, columns=['StartDate', 'Subject ID'])
print(df)

writer = pd.ExcelWriter('/home/spomared/tmp/summary.xls', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

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

my_formats = {'"ID1"': '#FF0000',
              '"ID2"': '#00FF00',
              '"ID3"': '#0000FF'}

for val, color in my_formats.items():
    fmt = workbook.add_format({'font_color': color})
    worksheet.conditional_format('B2:B5', {'type': 'cell',
                                           'criteria': '=',
                                           'value': val,
                                           'format': fmt})

writer.save()

这是输出的Excel文件:

Here is the output Excel file:

您可以在 http://xlsxwriter.readthedocs.io/working_with_conditional_formats上找到一些条件格式的引用. html

这篇关于用颜色pd.ExcelWriter格式化Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！