Python轻松实现在Excel工作表中应用条件格式_Python

在日常数据处理工作中，条件格式是一项非常实用的功能。它可以根据单元格的内容自动应用特定的格式，帮助用户快速识别数据模式、突出显示关键信息或发现异常值。通过 python 自动化实现条件格式，可以大幅提高数据处理效率，特别是在处理大量数据或需要批量生成报表的场景中。

本文将介绍如何使用 python 和 spire.xls 库在 excel 工作表中应用多种类型的条件格式，包括基于单元格值的格式、高亮重复值和唯一值、数据条可视化以及图标集等。

环境准备

首先，需要安装 spire.xls 库：

pip install spire.xls

安装完成后，即可开始编写代码来实现条件格式功能。

基于单元格值的条件格式

最基本的条件格式类型是根据单元格的数值来应用格式。例如，可以将大于某个阈值的单元格标记为红色，将小于某个阈值的单元格标记为绿色。

以下示例演示如何创建一个包含示例数据的工作表，并应用基于单元格值的条件格式：

from spire.xls import *
from spire.xls.common import *

# 创建工作簿
workbook = workbook()
sheet = workbook.worksheets[0]

# 插入示例数据到 a1:c4 范围
sheet.range["a1"].numbervalue = 582
sheet.range["a2"].numbervalue = 234
sheet.range["a3"].numbervalue = 314
sheet.range["a4"].numbervalue = 50
sheet.range["b1"].numbervalue = 150
sheet.range["b2"].numbervalue = 894
sheet.range["b3"].numbervalue = 560
sheet.range["b4"].numbervalue = 900
sheet.range["c1"].numbervalue = 134
sheet.range["c2"].numbervalue = 700
sheet.range["c3"].numbervalue = 920
sheet.range["c4"].numbervalue = 450

# 设置行高和列宽
sheet.allocatedrange.rowheight = 15
sheet.allocatedrange.columnwidth = 17

# 创建第一个条件格式规则：大于 800 的值显示为红色
xcfs1 = sheet.conditionalformats.add()
xcfs1.addrange(sheet.allocatedrange)
format1 = xcfs1.addcondition()
format1.formattype = conditionalformattype.cellvalue
format1.firstformula = "800"
format1.operator = comparisonoperatortype.greater
format1.fontcolor = color.get_red()
format1.backcolor = color.get_lightsalmon()

# 创建第二个条件格式规则：小于 300 的值显示为绿色
xcfs2 = sheet.conditionalformats.add()
xcfs2.addrange(sheet.allocatedrange)
format2 = xcfs2.addcondition()
format2.formattype = conditionalformattype.cellvalue
format2.firstformula = "300"
format2.operator = comparisonoperatortype.less
format2.fontcolor = color.get_green()
format2.backcolor = color.get_lightblue()

# 保存文件
workbook.savetofile("cellvalueformatting.xlsx", excelversion.version2013)
workbook.dispose()

结果文档：

在这个示例中，我们使用了 conditionalformattype.cellvalue 类型来创建基于单元格值的条件格式。通过设置 firstformula 指定阈值，使用 operator 定义比较操作符（如 greater、less 等），然后设置字体颜色和背景颜色。这样可以直观地突出显示超出正常范围的数据。

高亮重复值和唯一值

在数据分析过程中，经常需要识别数据集中的重复项或唯一项。条件格式提供了专门的功能来实现这一点。

以下示例展示如何高亮显示重复值和唯一值：

from spire.xls import *
from spire.xls.common import *

# 创建工作簿
workbook = workbook()
sheet = workbook.worksheets[0]

# ===== 1. 写入示例数据 =====
data = [
    ["id", "name", "value"],
    [1, "a", 10],
    [2, "b", 20],
    [3, "c", 10],
    [4, "d", 30],
    [5, "e", 40],
    [6, "f", 20],
    [7, "g", 50],
    [8, "h", 60],
    [9, "i", 70],
]

for r, row in enumerate(data, start=1):
    for c, value in enumerate(row, start=1):
        sheet.range[r, c].text = str(value)

# ===== 2. 设置列宽（可选，让演示更清晰）=====
sheet.columns[0].columnwidth = 15
sheet.columns[1].columnwidth = 20
sheet.columns[2].columnwidth = 15

# ===== 3. 条件格式：c列（value列）重复值 =====
xcfs = sheet.conditionalformats.add()
xcfs.addrange(sheet.range["c2:c10"])
format1 = xcfs.addcondition()
format1.formattype = conditionalformattype.duplicatevalues
format1.backcolor = color.get_indianred()

# ===== 4. 条件格式：c列唯一值 =====
xcfs1 = sheet.conditionalformats.add()
xcfs1.addrange(sheet.range["c2:c10"])
format2 = xcfs1.addcondition()
format2.formattype = conditionalformattype.uniquevalues
format2.backcolor = color.get_yellow()

# ===== 5. 保存 =====
workbook.savetofile("highlightduplicateunique.xlsx", excelversion.version2010)
workbook.dispose()

结果文档：

这里使用了两种特殊的条件格式类型：conditionalformattype.duplicatevalues 用于标识重复值，conditionalformattype.uniquevalues 用于标识唯一值。这种功能在数据清洗和验证场景中非常有用，可以快速发现数据录入错误或异常记录。

应用数据条可视化

数据条是一种直观的可视化方式，它通过在单元格内显示条形图来表示数值的相对大小。数值越大，条形越长。

以下是应用数据条的示例代码：

from spire.xls import *
from spire.xls.common import *

# 创建工作簿
workbook = workbook()
sheet = workbook.worksheets[0]

# 插入示例数据
sheet.range["a1"].numbervalue = 582
sheet.range["a2"].numbervalue = 234
sheet.range["a3"].numbervalue = 314
sheet.range["a4"].numbervalue = 50
sheet.range["b1"].numbervalue = 150
sheet.range["b2"].numbervalue = 894
sheet.range["b3"].numbervalue = 560
sheet.range["b4"].numbervalue = 900
sheet.range["c1"].numbervalue = 134
sheet.range["c2"].numbervalue = 700
sheet.range["c3"].numbervalue = 920
sheet.range["c4"].numbervalue = 450

# 设置单元格尺寸
sheet.allocatedrange.rowheight = 15
sheet.allocatedrange.columnwidth = 17

# 添加数据条
xcfs = sheet.conditionalformats.add()
xcfs.addrange(sheet.allocatedrange)
format = xcfs.addcondition()
format.formattype = conditionalformattype.databar
format.databar.barcolor = color.get_cadetblue()

# 保存文件
workbook.savetofile("databarsformatting.xlsx", excelversion.version2013)
workbook.dispose()

结果文档：

数据条功能通过 conditionalformattype.databar 类型实现。可以通过 databar.barcolor 属性自定义条形的颜色。这种可视化方式特别适合用于快速比较一组数值的大小关系，无需创建单独的图表。

应用图标集

图标集是另一种强大的条件格式工具，它使用预定义的图标（如箭头、交通灯、旗帜等）来表示数据的状态或等级。

以下示例展示如何应用交通灯图标集：

from spire.xls import *
from spire.xls.common import *

# 创建工作簿
workbook = workbook()
sheet = workbook.worksheets[0]

# 插入示例数据
sheet.range["a1"].numbervalue = 582
sheet.range["a2"].numbervalue = 234
sheet.range["a3"].numbervalue = 314
sheet.range["a4"].numbervalue = 50
sheet.range["b1"].numbervalue = 150
sheet.range["b2"].numbervalue = 894
sheet.range["b3"].numbervalue = 560
sheet.range["b4"].numbervalue = 900
sheet.range["c1"].numbervalue = 134
sheet.range["c2"].numbervalue = 700
sheet.range["c3"].numbervalue = 920
sheet.range["c4"].numbervalue = 450

# 设置单元格尺寸
sheet.allocatedrange.rowheight = 15
sheet.allocatedrange.columnwidth = 17

# 添加图标集（三色交通灯）
xcfs = sheet.conditionalformats.add()
xcfs.addrange(sheet.allocatedrange)
format = xcfs.addcondition()
format.formattype = conditionalformattype.iconset
format.iconset.iconsettype = iconsettype.threetrafficlights1

# 保存文件
workbook.savetofile("iconsetsformatting.xlsx", excelversion.version2013)
workbook.dispose()

结果文档：

图标集通过 conditionalformattype.iconset 类型实现。iconsettype 枚举提供了多种图标样式选择，如 threetrafficlights1（三色交通灯）、threearrows（三向箭头）等。图标集常用于表示性能指标、状态评估或等级分类，使数据更加直观易懂。