Python Xlsxwriter 简明教程

Python XlsxWriter - Conditional Formatting

Excel 使用“@ {s5}”基于用户定义的条件更改范围内单元格的外观。从条件格式设置菜单中，可以定义涉及各种类型值的条件。

Excel uses conditional formatting to change the appearance of cells in a range based on user defined criteria. From the conditional formatting menu, it is possible to define criteria involving various types of values.

conditional formatting

在下方的工作表中，A 列具有不同的数字。小于 50 的数字以红色字体和灰色背景色显示。

In the worksheet shown below, the column A has different numbers. Numbers less than 50 are shown in red font color and grey background color.

conditional formatting1

这是通过定义以下条件格式规则实现的：

This is achieved by defining a conditional formatting rule below −

conditional formatting2

The conditional_format() method

在 XlsxWriter 中，在 Worksheet 类中定义“@ {s6}”方法。若要实现上述显示结果，请按以下代码调用“@ {s7}”方法：

In XlsxWriter, there as a conditional_format() method defined in the Worksheet class. To achieve the above shown result, the conditional_format() method is called as in the following code −

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[56,95,63,34,81,47,74,5,99,12]
row=0

for num in data:
   ws.write(row,0,num)
   row+=1
f1 = wb.add_format({'bg_color': '#D9D9D9', 'font_color': 'red'})
ws.conditional_format(
   'A1:A10',{
      'type':'cell', 'criteria':'<', 'value':50, 'format':f1
   }
)
wb.close()

Parameters

“@ {s8}”方法的第一个参数是单元格范围，第二个参数是条件格式设置选项的字典。

The conditional_format() method’s first argument is the cell range, and the second argument is a dictionary of conditional formatting options.

选项字典使用以下参数配置条件格式设置规则：

The options dictionary configures the conditional formatting rules with the following parameters −

“@ {s9}”选项是必需参数。其值可以是单元格、日期、文本、公式等。每个参数都有子参数，例如条件、值、格式等。

The type option is a required parameter. Its value is either cell, date, text, formula, etc. Each parameter has sub-parameters such as criteria, value, format, etc.

  1. Type is the most common conditional formatting type. It is used when a format is applied to a cell based on a simple criterion.

  2. Criteria parameter sets the condition by which the cell data will be evaluated. All the logical operator in addition to between and not between operators are the possible values of criteria parameter.

  3. Value parameter is the operand of the criteria that forms the rule.

  4. Format parameter is the Format object (returned by the add_format() method). This defines the formatting features such as font, color, etc. to be applied to cells satisfying the criteria.

date 类型与单元格类型相似，并且使用相同的标准和值。但是，值参数应给为 datetime 对象。

The date type is similar the cell type and uses the same criteria and values. However, the value parameter should be given as a datetime object.

text 类型指定 Excel 的“特定文本”样式条件格式。它用于使用标准和值参数执行简单的字符串匹配。

The text type specifies Excel’s "Specific Text" style conditional format. It is used to do simple string matching using the criteria and value parameters.

Example

当使用 formula 类型时，条件格式取决于用户定义的公式。

When formula type is used, the conditional formatting depends on a user defined formula.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = [
   ['Anil', 45, 55, 50], ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85], ['Karishma', 55, 65, 45]
]
for row in range(len(data)):
   ws.write_row(row,0, data[row])

f1 = wb.add_format({'font_color': 'blue', 'bold':True})

ws.conditional_format(
   'A1:D4',
   {
      'type':'formula', 'criteria':'=AVERAGE($B1:$D1)>60', 'value':50, 'format':f1
   })
wb.close()

Output

使用 MS Excel 打开结果工作簿。我们可以看到满足上述条件的行按照格式对象显示为蓝色。条件格式规则管理器还显示我们在上述代码中设置的标准。

Open the resultant workbook using MS Excel. We can see the rows satisfying the above condition displayed in blue color according to the format object. The conditional format rule manager also shows the criteria that we have set in the above code.

parameters