Pandas to Excel条件格式化整个列 [英] Pandas to Excel conditional formatting whole column

问题描述

我想用格式将Pandas数据框写入Excel.为此，我正在使用xlsxwriter.我的问题是双重的:

I want to write a Pandas dataframe into Excel with formatting. For this I'm using xlsxwriter. My question is twofold:

• 首先，如何将条件格式应用于整个列?在示例中，它们使用特定的单元格范围格式(例如worksheet1.conditional_format('B3:K12', ...))，一种我可以引用整列的方法?

• First, how can I apply conditional formatting to a whole column? In the examples they use specific cell-range formatting (eg. worksheet1.conditional_format('B3:K12', ...)), is there a way I can reference a whole column?

第二，如果列B与列C的差异超过15％，我想用红色标记列B.正确的公式是什么?

Second, I want to mark the column B with red if it differs from column C by more than 15%. What would be the correct formula for this?

我目前正在尝试下面的代码，该代码不起作用(我不知道如何引用列).

I'm currently trying the below code, which doesn't work (I don't know how to reference columns).

writer = pd.ExcelWriter('pacing.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Last year vs. current state')
#worksheet.set_column('B:B', 18, format1)
workbook  = writer.book
worksheet = writer.sheets['Last year vs. current state']
format_missed = workbook.add_format({'bg_color': '#ff0000'})
format_ok = workbook.add_format({'bg_color': '#008000'})

worksheet.conditional_format('C:C', {'type':     'formula',
                                        'criteria': '=ABS((B-C)/C) > 15',
                                        'format':   format_missed})
writer.save() 

此外，我正在Mac上运行Excel，不确定是否有什么区别，但是我注意到Windows版本在哪里使用,，Mac版本需要;作为公式.不知道这会如何影响xlsxwriter.

Also, I'm running Excel on a Mac, not sure if it makes any difference, but I've noticed where the Windows version uses ,, the Mac version needs ; for formulas. Don't know how this affects xlsxwriter.

推荐答案

首先，如何将条件格式应用于整个列

First, how can I apply conditional formatting to a whole column

您可以指定列中从第一行到最后一行的单元格范围，如C1:C1048576.它是内部存储的方式，只是显示为C:C.

You can specify the cell range from the first to the last row in the column like C1:C1048576. This is how it is stored internally, it is just displayed as C:C.

这适用于在XlsxWriter中可以使用列范围的所有情况.

This applies to all cases where a column range can be used in XlsxWriter.

第二，如果B列与C列的差异超过15％，我想用红色标记B列.正确的公式是什么?

Second, I want to mark the column B with red if it differs from column C by more than 15%. What would be the correct formula for this?

这是您应该在Excel中解决的问题，然后将条件格式应用于XlsxWriter.这样的事情可能会起作用:

That is something you should figure out in Excel and then apply the conditional format to XlsxWriter. Something like this will probably work:

=ABS(B1-C1)/C1 > 0.15

此外，我正在Mac上运行Excel，不确定是否会有所不同，但是我注意到Windows版本在哪里使用Mac版本需要使用Excel；用于公式.不知道这会如何影响xlsxwriter

Also, I'm running Excel on a Mac, not sure if it makes any difference, but I've noticed where the Windows version uses ,, the Mac version needs ; for formulas. Don't know how this affects xlsxwriter

请参阅XlsxWriter文档的使用公式部分，以获取有关如何工作的解释与此.

See the Working with Formulas section of the XlsxWriter docs for an explanation of how to work with this.

这篇关于Pandas to Excel条件格式化整个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！