使用Python Xlsxwriter创建Excel电子表格（第4部分：条件格式）

标签： Python与Excel,Xlsxwriter

这是Python xlsxwriter库应用系列第4部分，前3部分请参阅：

>>>使用PythonXlsxwriter创建Excel电子表格

>>>使用PythonXlsxwriter创建Excel电子表格（第2部分：公式，链接与命名区域）

>>>使用PythonXlsxwriter创建Excel电子表格（第3部分：格式，迷你图与图表）

本文介绍Python xlsxwriter库如何为Excel文件创建各种条件格式。

Excel条件格式

条件格式，根据条件设置格式，这是Excel中一个灵巧优雅的功能，允许我们根据特定条件高亮显示（在大多数情况下）单元格。当然，用户可以定义这些条件。

此外，条件格式的优点在于格式是动态的——当值改变时，格式也会改变。

在Excel中，单击“条件格式”，我们会看到一系列选择，当单击“新建规则”，将打开一个对话框，基本上汇总了所有选项。定义条件的方法非常灵活，因此无法涵盖每一种情况。这里将介绍一些常见的场景。

Python环境准备

让我们进入Python，生成一些值和xlsxwriter工作簿。

创建的三种格式是：format_r（红色）、format_y（黄色）和format_g（绿色）。

import xlsxwriter
wb =xlsxwriter.Workbook(r'D:\conditional_formatting.xlsx')
ws = wb.add_worksheet('格式')
list_1 = list(range(10))
list_2 = [5,4,3,2,1,9,8,7,6,0]
list_3 = [i for i in range(-10,10)]
list_text =['python','excel','python xlsxwriter','python automate excel','excel automatepython','python']
format_r =wb.add_format({'bg_color':   '#FFC7CE',
                         'font_color': '#9C0006'})
format_y =wb.add_format({'bg_color':   '#FFEB9C',
                          'font_color':'#9C6500'})
format_g =wb.add_format({'bg_color':   '#C6EFCE',
                          'font_color':'#006100'})

回顾

在本系列的第3部分，我们学到了：

1.需要一个工作簿对象（wb）和一个工作表对象（ws）。

2.可以使用“A1”或（行、列）样式表示法来引用单元格和单元格区域。

3.使用workbook.add_format()方法创建Excel格式。

4.通过将数据和格式同时写入单元格/区域来格式化单元格。

新概念

1.要创建条件格式，使用worksheet.conditional_format('A1'，{parameters})。

2.条件格式叠加在现有单元格格式上，并非所有单元格格式属性都可以修改，例如字体名称、大小、对齐方式等。

3.大多数情况下，我们使用条件格式只是为了突出显示单元格（改变单元格颜色）。

Xlsxwriter条件格式参数

conditional_format()方法中的参数必须是一个字典，其中包含描述格式类型和样式的属性。一些主要属性包括：

• type ：是格式化单元格、数字、文本、排序、平均值、重复还是公式？参见上图1，“类型”指的是“规则类型”。
• criteria ：是否要查找“大于/小于”、“包含”某些文本、前几项等。
• value ：通常与条件“大于7”、“介于5和7之间”、“高于”平均值等结合使用。
• format ：格式，通常只是更改单元格/字体颜色。

现在，让我们看看如何应用它们。

条件格式所有单元格颜色色标

如果你喜欢彩虹，那么可以指定三种颜色（min、mid和max），Excel将为我们打造一道美丽的彩虹。如果只喜欢双色，则将“类型”更改为“双色刻度”，然后只需删除“中间色”。

def color_scale():
   ws.write('N1','三色刻度')
   ws.write_column('N2',list_1)   
   ws.conditional_format('N2:N11',
   {'type':'3_color_scale',
    'min_color':'red',
    'mid_color':'yellow',
    'max_color':'green'
   })

条件格式所有单元格数据条

可以用数据条做很多不同的很酷的东西，很灵活。

def data_bar():
   ws.write('P1','data bar')
   ws.write_column('P2',list_3)
   ws.conditional_format('P2:P22',
        {'type': 'data_bar',
         'bar_color':'green',
         'bar_only': True, #True - 没有数据, False - 显示数据
         'bar_solid': False, # True - 纯色填充, False - 渐变色
         'bar_negative_color':'red',
         'bar_direction': 'left', # 或 'right'
         'bar_axis_position':'middle', # 或 'none'
        })

基于数字的条件格式

注意ws.conditional_format方法中的字典，特别是传递给这些属性的值。“criteria”可以是以下任一列（任一列都适用）：

‘between’

‘not between’

‘equal to’ ‘==’

‘not equal to’ ‘!=’

‘greater than’ ‘>’

‘less than’ ‘<’

‘greater than or equal to’ ‘>=’

‘less than or equal to’ ‘<=’

def based_on_number():
   ws.merge_range('B1:C1','>7 绿色, <5 红色, 其间黄色')
   ws.write_column('B2', list_1)
   ws.write_column('C2', list_2)
   ## 大于7, 绿色
   ws.conditional_format('B2:C11',
        {'type': 'cell',
         'criteria': '>',
         'value': 7,
         'format':format_g
   ## 小于5, 红色
   ws.conditional_format('B2:C11',
        {'type': 'cell',
         'criteria': '<',
         'value': 5,
         'format':format_r
   ## 在5与7之间, 黄色
   ws.conditional_format('B2:C11',
        {'type': 'cell',
         'criteria': 'between',
         'minimum':5,
         'maximum':7,
         'format':format_y
         })

图4

基于单元格输入的数字的条件格式

这与前面的示例类似，只是我们没有对阈值5和7进行硬编码。我们将让格式取决于单元格值，甚至更动态。

注意下面代码中的“value”属性，我们需要使用绝对引用，否则它将不起作用。通常，对于任何“value”属性，我们都需要使用绝对引用。

在Excel中生成后，你将看到，当我们修改单元格B19和C19中的值时，格式会发生变化。

def based_on_number_input():
   ws.merge_range('B18:C18','使用数字输入')
   ws.write('B19',7) ## 阈值
   ws.write('C19',5) ## 阈值
   ws.write_column('B20', list_1)
   ws.write_column('C20', list_2)
   ws.conditional_format('B20:C29',
         {'type': 'cell',
          'criteria': '>',
          'value': 'B19',
          'format':format_g
   ws.conditional_format('B20:C29',
         {'type': 'cell',
          'criteria': '<',
          'value': 'C19',
          'format':format_r
         })

基于文本的条件格式

可以检查单元格是否包含某些文本。criteria属性包括以下内容：

‘containing’

‘not containing’

‘begins with’

‘ends with’

def based_on_text():
   ws.write('E1','包含"python"')
   ws.write('F1','以"python" 开始')
   ws.write_column('E2', list_text)
   ws.write_column('F2', list_text)
   ws.conditional_format('E2:E11',
         {'type': 'text',
          'criteria': 'containing',
          'value': 'python',
          'format':format_g
   ws.conditional_format('F2:F11',
         {'type': 'text',
          'criteria': 'begins with',
          'value': 'python',
          'format':format_g
         })

图5

条件格式设置排名靠前/靠后的值

可以通过“顶部”或“底部”值（即前5个最大值）或百分比（即所选值的底部10%）突出显示项目。省略“criteria”表示按计数，而设置“criteria”：%表示按百分比。

def top_n():
   ws.write('H1','前 5')
   ws.write_column('H2',list_2)
   ws.conditional_format('H2:H11',
         {'type': 'top',
          'value': 5,
          #'criteria': '%',
          'format':format_g
         })

图6

设置高于/低于平均值条件格式

Excel将计算所选区域的平均值，然后将区域中的每个数字与平均值进行比较，并相应地设置格式。

def average():
   ws.write('J1','平均值')
   ws.write_column('J2',list_2)
   ws.conditional_format('J2:J11',
         {'type': 'average',
          'criteria': 'above',
          'format':format_g
         })

图7

条件格式突出唯一/重复值

可以突出显示选定区域内的重复（或唯一）值。

def duplicate():
   ws.write('L1','重复')
   ws.write_column('L2',list_text)
   ws.conditional_format('L2:L11',
         {'type': 'duplicate',  #或 'unique'
          'format':format_g
         })

图8

基于公式的条件格式

可以根据公式进行条件格式设置，使我们的Excel更加动态。

但是，基于公式的格式可能有点棘手，因为某些情况需要绝对引用，而另一些情况需要非绝对引用。策略是：尝试Excel中的公式，无论单元格引用中是否包含$。如果它在Excel中工作，那么将相同的公式应用到Python中也会起作用。

下面的代码比较R列和S列中的数字，然后突出显示（绿色）两列之间较大的数字。

注意，“type”设置为“formula”，在“criteria”中，我们键入公式，就好像只针对（所选区域中）第一项一样。在conditional_format方法中，正在格式化单元格R2:R11，第一个元素是R2，因此公式为'=R2>S2'。如果想将该格式应用于R3:R11，那么公式需要是’=R3>S3’，以此类推。

另外，在本例中，我们比较两列，因此在公式中不使用绝对引用。在其他情况下，可能需要使用绝对引用来实现基于公式的格式设置工作。

def based_on_formula():
   ws.merge_range('R1:S1','比较 R 和 S, 突出显示大值 #')
   ws.write_column('R2', list_1)
   ws.write_column('S2', list_2)
   ws.conditional_format('R2:R11',
         {'type': 'formula',
          'criteria': '=R2>S2',
          'format':format_g