熊猫要优于条件格式整列

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

我想将一个熊猫数据aframe写入Excel中并进行格式化。为此，我使用xlsxwriter。我的问题是双重的:

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?

首先，如何对整个列应用条件格式?在示例中，它们使用特定的单元格范围格式(如。worksheet1。conditional_format('B3:K12'，……)，是否有一种方法可以引用整个列?
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?

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

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()

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.

另外，我在Mac上运行Excel，不确定它是否有什么区别，但是我注意到Windows版本的使用，Mac版本的需要;的公式。不知道这如何影响xlsxwriter。

1 个解决方案

#1

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

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

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.

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

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

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

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?

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

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

您应该在Excel中找出这一点，然后将条件格式应用到XlsxWriter。类似这样的事情可能会奏效:

=ABS(B1-C1)/C1 > 0.15

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

另外，我在Mac上运行Excel，不确定它是否有什么区别，但是我注意到Windows版本的使用，Mac版本的需要;的公式。不知道这会如何影响xlsxwriter

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

有关如何使用XlsxWriter文档中的公式部分，请参阅。

#1