使用Python Xlsxwriter创建Excel电子表格（第4部分：条件格式）
使用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
})
ws.conditional_format('S2:S11',
{'type': 'formula',
'criteria': '=S2>R2',
'format':format_g
})
图9