Python Excel条件格式设置-详解

于 2025-04-16 15:15:00 发布
阅读量1.1k 收藏 23
点赞数 30
CC 4.0 BY-SA版权
分类专栏： Python Excel 文章标签： python excel条件格式 突出显示单元格 最前或最后规则 色阶 数据条 日期条件格式
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SunnyDays1011/article/details/147254558

目录

工具与环境设置

了解Excel中的条件格式规则

使用Python对Excel表格数据应用条件格式

示例1：单元格值

示例2：色阶

示例3：数据条

示例4：图标集

示例5：最前/最后

示例6：重复值/唯一值

示例7：发生日期

完整代码

条件格式是电子表格软件（如Microsoft Excel、Google Sheets、WPS表格等）中的一项数据可视化功能。它允许用户通过预定义的条件规则，自动改变单元格或单元格区域的外观样式（包括但不限于背景颜色、字体样式、边框格式、数据条、色阶、图标集等），从而将数据中的特定模式、趋势或异常值以直观、醒目的方式呈现出来。这篇博客将探讨如何使用Python对Excel表格中的数据应用条件格式。

工具与环境设置

在使用Python操作Excel条件格式前，需完成以下环境准备：

  1. 安装Python
    若未安装Python，可从Python官网下载对应版本并完成安装。
  2. 安装Free Spire.XLS库
    通过以下命令安装Free Spire.XLS for Python库以操作Excel文件并应用条件格式：
pip install spire.xls.free

了解Excel中的条件格式规则

Excel提供了丰富的条件格式规则，以满足各种数据分析和可视化场景。以下是一些常用条件格式规则及功能说明：

类型

功能描述

单元格值

基于数值比较条件（如大于、小于、介于）设置格式

色阶

通过渐变色直观呈现数据分布（如红-黄-绿色阶）

数据条

在单元格内显示横向比例条（支持实心/渐变样式），直观反映数值相对大小

图标集

使用符号集（如箭头/旗帜/交通灯等）表示数据趋势或状态

最前/最后

突出显示排名靠前/后的数据（如前10项、前10%、高于平均值等）

重复值/唯一值

标识重复数据或唯一值

发生日期

按时间特征设置格式（如"最近7天"、"本月"、"昨天"等时间段）

空值/非空值

标记空白单元格或含数据单元格

自定义公式

通过自定义Excel公式创建高级格式规则

文本包含

根据文本特征设置格式（如包含/开头为/结尾为特定字符等）

整行/整列

基于条件自动格式化整行或整列

错误值

标识错误值（如#N/A、#VALUE!等错误类型）

使用Python对Excel表格数据应用条件格式

下面几个例子展示了如何使用Python在Excel中应用几种常见的条件格式规则。文章末尾提供了完整代码，涵盖几乎所有条件格式的实现方法。

示例1：单元格值

基于单元格值的条件格式规则允许用户根据单元格内的数值应用规则，以高亮显示满足特定条件的单元格（例如，大于或小于某个值）。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
worksheet = workbook.Worksheets[0]

# 为单元格区域"B2:B6"创建条件格式规则
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定义条件
condition = format.AddCondition()
# 设置格式类型为单元格值
condition.FormatType = ConditionalFormatType.CellValue
# 设置比较运算符为"大于"
condition.Operator = ComparisonOperatorType.Greater
# 指定阈值
condition.FirstFormula = "75"
# 设置背景色为黄色
condition.BackColor = Color.FromRgb(204, 204, 0)  

# 保存修改后的工作簿
workbook.SaveToFile("基于单元格值.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

示例2：色阶

色阶规则通过使用渐变色来显示单元格的值，使数据分布更加直观，特别适合在大数据集中识别趋势和模式。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
worksheet = workbook.Worksheets[0]

# 为单元格区域"B2:B6"创建条件格式规则
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定义条件
condition = format.AddCondition()
# 设置格式类型为色阶
condition.FormatType = ConditionalFormatType.ColorScale

# 保存修改后的工作簿
workbook.SaveToFile("色阶.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

示例3：数据条

数据条规则通过在单元格内添加横向比例条来直观反映数值大小，便于快速比较数值。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
worksheet = workbook.Worksheets[0]

# 为单元格区域"B2:B6"创建条件格式规则
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定义条件
condition = format.AddCondition()
# 设置格式类型为数据条
condition.FormatType = ConditionalFormatType.DataBar
# 设置数据条填充模式为渐变填充
condition.DataBar.BarFillType = DataBarFillType.DataBarFillGradient
# 设置数据条颜色
condition.DataBar.BarColor = Color.FromRgb(99, 142, 198)

# 保存修改后的工作簿
workbook.SaveToFile("数据条.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

示例4：图标集

图标集规则通过使用符号（如箭头或交通灯）来表示数据趋势和类别，便于快速传达数据状态。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
worksheet = workbook.Worksheets[0]

# 为单元格区域"B2:B6"创建条件格式规则
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定义条件
condition = format.AddCondition()
# 设置格式类型为图标集
condition.FormatType = ConditionalFormatType.IconSet
# 定义图标集类型为四向箭头
condition.IconSet.IconSetType = IconSetType.FourArrows

# 保存修改后的工作簿
workbook.SaveToFile("图标集.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

示例5：最前/最后

最前/最后规则用于高亮显示数据集中的最高值或最低值，便于识别关键数据点。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
sheet = workbook.Worksheets[0]

# 为"B2:B6"范围创建条件格式规则
format_1 = sheet.ConditionalFormats.Add()
format_1.AddRange(sheet.Range["B2:B6"])

# 添加条件
condition_1 = format_1.AddTopBottomCondition(TopBottomType.Top, 1)
# 设置高亮颜色为红色
condition_1.BackColor = Color.get_Red()

# 创建另一个条件格式规则
format_2 = sheet.ConditionalFormats.Add()
format_2.AddRange(sheet.Range["B2:B6"])

# 添加条件
condition_2 = format_2.AddTopBottomCondition(TopBottomType.Bottom, 1)
# 设置高亮颜色为森林绿
condition_2.BackColor = Color.get_ForestGreen()

# 保存修改后的工作簿
workbook.SaveToFile("最前最后规则.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

示例6：重复值/唯一值

重复值/唯一值规则有助于识别数据中的重复数据或唯一数据，特别适用于数据验证和保证数据完整性的场景。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
sheet = workbook.Worksheets[0]

# 为单元格区域"B2:B6"创建条件格式规则
format_1 = sheet.ConditionalFormats.Add()
format_1.AddRange(sheet.Range["B2:B6"])

# 定义条件
condition_1 = format_1.AddCondition()
condition_1.FormatType = ConditionalFormatType.DuplicateValues
# 设置高亮颜色为浅黄色
condition_1.BackColor = Color.get_LightYellow()

# 创建另一个条件格式规则
format_2 = sheet.ConditionalFormats.Add()
format_2.AddRange(sheet.Range["B2:B6"])

# 定义条件
condition_2 = format_2.AddCondition()
condition_2.FormatType = ConditionalFormatType.UniqueValues
# 设置高亮颜色为天蓝色
condition_2.BackColor = Color.get_SkyBlue()

# 保存修改后的工作簿
workbook.SaveToFile("重复或唯一值.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

示例7：发生日期

发生日期条件格式规则允许根据单元格中的日期特征（如今天/本周/本月等时间范围）自动设置特殊格式。

实现代码：

from spire.xls import *

# 初始化工作簿并加载现有Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试.xlsx")

# 获取第一个工作表
sheet = workbook.Worksheets[0]

# 为单元格区域"C2:C6"创建条件格式规则
format = sheet.ConditionalFormats.Add()
format.AddRange(sheet.Range["C2:C6"])

# 添加条件
condition = format.AddTimePeriodCondition(TimePeriodType.LastMonth)
# 设置高亮颜色为橙色
condition.BackColor = Color.get_Orange()

# 保存修改后的工作簿
workbook.SaveToFile("发生日期.xlsx", ExcelVersion.Version2016)
# 释放工作簿资源
workbook.Dispose()

完整代码

from spire.xls import *

# 给Excel工作表添加条件格式
def AddConditionalFormattingForNewSheet(sheet):
    # 添加图标集
    AddDefaultIconSet(sheet)
    AddIconSet2(sheet)
    AddIconSet3(sheet)
    AddIconSet4(sheet)
    AddIconSet5(sheet)
    AddIconSet6(sheet)
    AddIconSet7(sheet)
    AddIconSet8(sheet)
    AddIconSet9(sheet)
    AddIconSet10(sheet)
    AddIconSet11(sheet)
    AddIconSet12(sheet)
    AddIconSet13(sheet)
    AddIconSet14(sheet)
    AddIconSet15(sheet)
    AddIconSet16(sheet)
    AddIconSet17(sheet)
    AddIconSet18(sheet)

    # 添加色阶
    AddDefaultColorScale(sheet)
    Add3ColorScale(sheet)
    Add2ColorScale(sheet)

    # 添加平均值
    AddAboveAverage(sheet)
    AddAboveAverage2(sheet)
    AddAboveAverage3(sheet)

    # 添加最前/最后规则
    AddTop10_1(sheet)
    AddTop10_2(sheet)
    AddTop10_3(sheet)
    AddTop10_4(sheet)

    # 添加数据条
    AddDataBar1(sheet)
    AddDataBar2(sheet)

    # 添加文本包含规则
    AddContainsText(sheet)
    AddNotContainsText(sheet)

# 添加空值/非空值规则
    AddContainsBlank(sheet)
    AddNotContainsBlank(sheet)
    AddBeginWith(sheet)
    AddEndWith(sheet)

# 添加错误值规则
    AddContainsError(sheet)
    AddNotContainsError(sheet)
    AddDuplicate(sheet)
    AddUnique(sheet)

    # 添加发生日期规则
    AddTimePeriod_1(sheet)
    AddTimePeriod_2(sheet)
    AddTimePeriod_3(sheet)
    AddTimePeriod_4(sheet)
    AddTimePeriod_5(sheet)
    AddTimePeriod_6(sheet)
    AddTimePeriod_7(sheet)
    AddTimePeriod_8(sheet)
    AddTimePeriod_9(sheet)
    AddTimePeriod_10(sheet)

    # 设置工作表列宽和行高自适应
    sheet.AllocatedRange.ColumnWidth = 15
    sheet.AllocatedRange.AutoFitRows()

def AddIconSet2(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M1:O2"])
    sheet.Range["M1:O2"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M1:O2"].Style.Color = Color.get_AliceBlue()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeArrows
    sheet.Range["M1"].Text = "ThreeArrows"
    sheet.Range["N1"].NumberValue = 15
    sheet.Range["O1"].NumberValue = 18
    sheet.Range["M2"].NumberValue = 14
    sheet.Range["N2"].NumberValue = 17
    sheet.Range["O2"].NumberValue = 20

def AddIconSet3(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M3:O4"])
    sheet.Range["M3:O4"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M3:O4"].Style.Color = Color.get_AntiqueWhite()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FourArrows
    sheet.Range["M3"].Text = "FourArrows"
    sheet.Range["N3"].NumberValue = 17
    sheet.Range["O3"].NumberValue = 20
    sheet.Range["M4"].NumberValue = 16
    sheet.Range["N4"].NumberValue = 19
    sheet.Range["O4"].NumberValue = 22

def AddIconSet4(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M5:O6"])
    sheet.Range["M5:O6"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M5:O6"].Style.Color = Color.get_Aqua()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FiveArrows
    sheet.Range["M5"].Text = "FiveArrows"
    sheet.Range["N5"].NumberValue = 17
    sheet.Range["O5"].NumberValue = 20
    sheet.Range["M6"].NumberValue = 16
    sheet.Range["N6"].NumberValue = 19
    sheet.Range["O6"].NumberValue = 22

def AddIconSet5(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M7:O8"])
    sheet.Range["M7:O8"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M7:O8"].Style.Color = Color.get_Aquamarine()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeArrowsGray
    sheet.Range["M7"].Text = "ThreeArrowsGray"
    sheet.Range["N7"].NumberValue = 21
    sheet.Range["O7"].NumberValue = 24
    sheet.Range["M8"].NumberValue = 20
    sheet.Range["N8"].NumberValue = 23
    sheet.Range["O8"].NumberValue = 26

def AddIconSet6(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M9:O10"])
    sheet.Range["M9:O10"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M9:O10"].Style.Color = Color.get_Azure()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FourArrowsGray
    sheet.Range["M9"].Text = "FourArrowsGray"
    sheet.Range["N9"].NumberValue = 23
    sheet.Range["O9"].NumberValue = 26
    sheet.Range["M10"].NumberValue = 22
    sheet.Range["N10"].NumberValue = 25
    sheet.Range["O10"].NumberValue = 28

def AddIconSet7(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M11:O12"])
    sheet.Range["M11:O12"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M11:O12"].Style.Color = Color.get_Beige()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FiveArrowsGray
    sheet.Range["M11"].Text = "FiveArrowsGray"
    sheet.Range["N11"].NumberValue = 25
    sheet.Range["O11"].NumberValue = 28
    sheet.Range["M12"].NumberValue = 24
    sheet.Range["N12"].NumberValue = 27
    sheet.Range["O12"].NumberValue = 30

def AddIconSet8(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M13:O14"])
    sheet.Range["M13:O14"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M13:O14"].Style.Color = Color.get_Bisque()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeFlags
    sheet.Range["M13"].Text = "ThreeFlags"
    sheet.Range["N13"].NumberValue = 27
    sheet.Range["O13"].NumberValue = 30
    sheet.Range["M14"].NumberValue = 26
    sheet.Range["N14"].NumberValue = 29
    sheet.Range["O14"].NumberValue = 32

def AddIconSet9(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M15:O16"])
    sheet.Range["M15:O16"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M15:O16"].Style.Color = Color.get_BlanchedAlmond()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FiveQuarters
    sheet.Range["M15"].Text = "FiveQuarters"
    sheet.Range["N15"].NumberValue = 29
    sheet.Range["O15"].NumberValue = 32
    sheet.Range["M16"].NumberValue = 28
    sheet.Range["N16"].NumberValue = 31
    sheet.Range["O16"].NumberValue = 34

def AddIconSet10(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M17:O18"])
    sheet.Range["M17:O18"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M17:O18"].Style.Color = Color.get_LightBlue()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FourRating
    sheet.Range["M17"].Text = "FourRating"
    sheet.Range["N17"].NumberValue = 31
    sheet.Range["O17"].NumberValue = 34
    sheet.Range["M18"].NumberValue = 30
    sheet.Range["N18"].NumberValue = 33
    sheet.Range["O18"].NumberValue = 36

def AddIconSet11(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M19:O20"])
    sheet.Range["M19:O20"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M19:O20"].Style.Color = Color.get_BlueViolet()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FiveRating
    sheet.Range["M19"].Text = "FiveRating"
    sheet.Range["N19"].NumberValue = 33
    sheet.Range["O19"].NumberValue = 36
    sheet.Range["M20"].NumberValue = 32
    sheet.Range["N20"].NumberValue = 35
    sheet.Range["O20"].NumberValue = 38

def AddIconSet12(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M21:O22"])
    sheet.Range["M21:O22"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M21:O22"].Style.Color = Color.get_Brown()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FourRedToBlack
    sheet.Range["M21"].Text = "FourRedToBlack"
    sheet.Range["N21"].NumberValue = 35
    sheet.Range["O21"].NumberValue = 38
    sheet.Range["M22"].NumberValue = 34
    sheet.Range["N22"].NumberValue = 37
    sheet.Range["O22"].NumberValue = 40

def AddIconSet13(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M23:O24"])
    sheet.Range["M23:O24"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M23:O24"].Style.Color = Color.get_BurlyWood()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeSigns
    sheet.Range["M23"].Text = "ThreeSigns"
    sheet.Range["N23"].NumberValue = 37
    sheet.Range["O23"].NumberValue = 40
    sheet.Range["M24"].NumberValue = 36
    sheet.Range["N24"].NumberValue = 39
    sheet.Range["O24"].NumberValue = 42

def AddIconSet14(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M25:O26"])
    sheet.Range["M25:O26"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M25:O26"].Style.Color = Color.get_CadetBlue()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeSymbols
    sheet.Range["M25"].Text = "ThreeSymbols"
    sheet.Range["N25"].NumberValue = 39
    sheet.Range["O25"].NumberValue = 42
    sheet.Range["M26"].NumberValue = 38
    sheet.Range["N26"].NumberValue = 41
    sheet.Range["O26"].NumberValue = 44

def AddIconSet15(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M27:O28"])
    sheet.Range["M27:O28"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M27:O28"].Style.Color = Color.get_Chartreuse()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeSymbols2
    sheet.Range["M27"].Text = "ThreeSymbols2"
    sheet.Range["N27"].NumberValue = 41
    sheet.Range["O27"].NumberValue = 44
    sheet.Range["M28"].NumberValue = 40
    sheet.Range["N28"].NumberValue = 43
    sheet.Range["O28"].NumberValue = 46

def AddIconSet16(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M29:O30"])
    sheet.Range["M29:O30"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M29:O30"].Style.Color = Color.get_Chocolate()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeTrafficLights1
    sheet.Range["M29"].Text = "ThreeTrafficLights1"
    sheet.Range["N29"].NumberValue = 43
    sheet.Range["O29"].NumberValue = 46
    sheet.Range["M30"].NumberValue = 42
    sheet.Range["N30"].NumberValue = 45
    sheet.Range["O30"].NumberValue = 48

def AddIconSet17(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M31:O32"])
    sheet.Range["M31:O32"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M31:O32"].Style.Color = Color.get_Coral()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.ThreeTrafficLights2
    sheet.Range["M31"].Text = "ThreeTrafficLights2"
    sheet.Range["N31"].NumberValue = 45
    sheet.Range["O31"].NumberValue = 48
    sheet.Range["M32"].NumberValue = 44
    sheet.Range["N32"].NumberValue = 47
    sheet.Range["O32"].NumberValue = 50

def AddIconSet18(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["M33:O35"])
    sheet.Range["M33:O35"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["M33:O35"].Style.Color = Color.get_CornflowerBlue()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    cf.IconSet.IconSetType = IconSetType.FourTrafficLights
    sheet.Range["M33"].Text = "FourTrafficLights"
    sheet.Range["N33"].NumberValue = 48
    sheet.Range["O33"].NumberValue = 52
    sheet.Range["M34"].NumberValue = 46
    sheet.Range["N34"].NumberValue = 50
    sheet.Range["O34"].NumberValue = 54
    sheet.Range["M35"].NumberValue = 48
    sheet.Range["N35"].NumberValue = 52
    sheet.Range["O35"].NumberValue = 56

def AddTimePeriod_10(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I19:K20"])
    sheet.Range["I19:K20"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I19:K20"].Style.Color = Color.get_MediumSeaGreen()
    cf = conds.AddTimePeriodCondition(TimePeriodType.Yesterday)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I19"]
    c.Value2 = DateTime.get_Now().AddDays(-2).Date
    c = sheet.Range["J19"]
    c.Value2 = DateTime.get_Now().AddDays(-1).Date
    c = sheet.Range["K19"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I20"]
    c.Text = "Yesterday"
    c = sheet.Range["J20"]
    c.Value2 = DateTime.get_Now().AddDays(1).Date
    c = sheet.Range["K20"]
    c.Value2 = DateTime.get_Now().AddDays(2).Date

def AddTimePeriod_9(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I17:K18"])
    sheet.Range["I17:K18"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I17:K18"].Style.Color = Color.get_MediumPurple()
    cf = conds.AddTimePeriodCondition(TimePeriodType.Tomorrow)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I17"]
    c.Value2 = DateTime.get_Now().AddDays(-2).Date
    c = sheet.Range["J17"]
    c.Value2 = DateTime.get_Now().AddDays(-1).Date
    c = sheet.Range["K17"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I18"]
    c.Text = "Tomorrow"
    c = sheet.Range["J18"]
    c.Value2 = DateTime.get_Now().AddDays(1).Date
    c = sheet.Range["K18"]
    c.Value2 = DateTime.get_Now().AddDays(2).Date

def AddTimePeriod_8(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I15:K16"])
    sheet.Range["I15:K16"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I15:K16"].Style.Color = Color.get_MediumOrchid()
    cf = conds.AddTimePeriodCondition(TimePeriodType.ThisWeek)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I15"]
    c.Value2 = DateTime.get_Now().AddDays(-2).Date
    c = sheet.Range["J15"]
    c.Value2 = DateTime.get_Now().AddDays(-1).Date
    c = sheet.Range["K15"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I16"]
    c.Text = "ThisWeek"
    c = sheet.Range["J16"]
    c.Value2 = DateTime.get_Now().AddDays(2).Date
    c = sheet.Range["K16"]
    c.Value2 = DateTime.get_Now().AddDays(3).Date

def AddTimePeriod_7(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I13:K14"])
    sheet.Range["I13:K14"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I13:K14"].Style.Color = Color.get_MediumBlue()
    cf = conds.AddTimePeriodCondition(TimePeriodType.ThisMonth)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I13"]
    c.Value2 = DateTime.get_Now().AddMonths(-1).Date
    c = sheet.Range["J13"]
    c.Value2 = DateTime.get_Now().AddDays(-1).Date
    c = sheet.Range["K13"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I14"]
    c.Text = "ThisMonth"
    c = sheet.Range["J14"]
    c.Value2 = DateTime.get_Now().AddMonths(1).Date
    c = sheet.Range["K14"]
    c.Value2 = DateTime.get_Now().AddMonths(2).Date

def AddTimePeriod_6(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I11:K12"])
    sheet.Range["I11:K12"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I11:K12"].Style.Color = Color.get_MediumAquamarine()
    cf = conds.AddTimePeriodCondition(TimePeriodType.NextWeek)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I11"]
    c.Value2 = DateTime.get_Now().AddDays(-3).Date
    c = sheet.Range["J11"]
    c.Value2 = DateTime.get_Now().AddDays(-2).Date
    c = sheet.Range["K11"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I12"]
    c.Text = "NextWeek"
    c = sheet.Range["J12"]
    c.Value2 = DateTime.get_Now().AddDays(3).Date
    c = sheet.Range["K12"]
    c.Value2 = DateTime.get_Now().AddMonths(4).Date

def AddTimePeriod_5(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I9:K10"])
    sheet.Range["I9:K10"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I9:K10"].Style.Color = Color.get_Maroon()
    cf = conds.AddTimePeriodCondition(TimePeriodType.NextMonth)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I9"]
    c.Value2 = DateTime.get_Now().AddDays(-3).Date
    c = sheet.Range["J9"]
    c.Value2 = DateTime.get_Now().AddMonths(-1).Date
    c = sheet.Range["K9"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I10"]
    c.Text = "NextMonth"
    c = sheet.Range["J10"]
    c.Value2 = DateTime.get_Now().AddMonths(1).Date
    c = sheet.Range["K10"]
    c.Value2 = DateTime.get_Now().AddMonths(2).Date

def AddTimePeriod_4(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I7:K8"])
    sheet.Range["I7:K8"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I7:K8"].Style.Color = Color.get_Linen()
    cf = conds.AddTimePeriodCondition(TimePeriodType.LastWeek)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I7"]
    c.Value2 = DateTime.get_Now().AddDays(-6).Date
    c = sheet.Range["J7"]
    c.Value2 = DateTime.get_Now().AddDays(-5).Date
    c = sheet.Range["K7"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I8"]
    c.Text = "LastWeek"
    c = sheet.Range["J8"]
    c.Value2 = DateTime.get_Now().AddDays(3).Date
    c = sheet.Range["K8"]
    c.Value2 = DateTime.get_Now().AddMonths(4).Date

def AddTimePeriod_3(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I5:K6"])
    sheet.Range["I5:K6"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I5:K6"].Style.Color = Color.get_Linen()
    cf = conds.AddTimePeriodCondition(TimePeriodType.LastMonth)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I5"]
    c.Value2 = DateTime.get_Now().AddDays(-6).Date
    c = sheet.Range["J5"]
    c.Value2 = DateTime.get_Now().AddMonths(-1).Date
    c = sheet.Range["K5"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I6"]
    c.Text = "LastMonth"
    c = sheet.Range["J6"]
    c.Value2 = DateTime.get_Now().AddDays(3).Date
    c = sheet.Range["K6"]
    c.Value2 = DateTime.get_Now().AddMonths(1).Date

def AddTimePeriod_2(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I3:K4"])
    sheet.Range["I3:K4"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I3:K4"].Style.Color = Color.get_LightSkyBlue()
    cf = conds.AddTimePeriodCondition(TimePeriodType.Last7Days)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I3"]
    c.Value2 = DateTime.get_Now().AddDays(-8).Date
    c = sheet.Range["J3"]
    c.Value2 = DateTime.get_Now().AddDays(-7).Date
    c = sheet.Range["K3"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I4"]
    c.Text = "Last7Days"
    c = sheet.Range["J4"]
    c.Value2 = DateTime.get_Now().AddDays(3).Date
    c = sheet.Range["K4"]
    c.Value2 = DateTime.get_Now().AddMonths(2).Date

def AddTimePeriod_1(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["I1:K2"])
    sheet.Range["I1:K2"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["I1:K2"].Style.Color = Color.get_LightSlateGray()
    cf = conds.AddTimePeriodCondition(TimePeriodType.Today)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["I1"]
    c.Value2 = DateTime.get_Now().AddDays(-8).Date
    c = sheet.Range["J1"]
    c.Value2 = DateTime.get_Now().AddDays(-7).Date
    c = sheet.Range["K1"]
    c.Value2 = DateTime.get_Now().Date
    c = sheet.Range["I2"]
    c.Text = "Today"
    c = sheet.Range["J2"]
    c.Value2 = DateTime.get_Now().AddDays(3).Date
    c = sheet.Range["K2"]
    c.Value2 = DateTime.get_Now().AddMonths(2).Date

def AddDuplicate(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E23:G24"])
    sheet.Range["E23:G24"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E23:G24"].Style.Color = Color.get_LightSlateGray()
    cf = conds.AddDuplicateValuesCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["E23"]
    c.Text = "aa"
    c = sheet.Range["F23"]
    c.Text = "bb"
    c = sheet.Range["G23"]
    c.Text = "aa"
    c = sheet.Range["E24"]
    c.Text = "bbb"
    c = sheet.Range["F24"]
    c.Text = "bb"
    c = sheet.Range["G24"]
    c.Text = "ccc"

def AddUnique(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E21:G22"])
    sheet.Range["E21:G22"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E21:G22"].Style.Color = Color.get_LightSalmon()
    cf = conds.AddUniqueValuesCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    c = sheet.Range["E21"]
    c.Text = "aa"
    c = sheet.Range["F21"]
    c.Text = "bb"
    c = sheet.Range["G21"]
    c.Text = "aa"
    c = sheet.Range["E22"]
    c.Text = "bbb"
    c = sheet.Range["F22"]
    c.Text = "bb"
    c = sheet.Range["G22"]
    c.Text = "ccc"

def AddNotContainsError(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E19:G20"])
    sheet.Range["E19:G20"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E19:G20"].Style.Color = Color.get_LightSeaGreen()
    cf = conds.AddNotContainsErrorsCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    c = sheet.Range["E19"]
    c.Text = "aa"
    c = sheet.Range["F19"]
    c.Text = "=Sum"
    c = sheet.Range["G19"]
    c.Text = "aa"
    c = sheet.Range["E20"]
    c.Text = "bbb"
    c = sheet.Range["F20"]
    c.Text = "sss"
    c = sheet.Range["G20"]
    c.Text = "=Max"

def AddContainsError(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E17:G18"])
    sheet.Range["E17:G18"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E17:G18"].Style.Color = Color.get_LightSkyBlue()
    cf = conds.AddContainsErrorsCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    c = sheet.Range["E17"]
    c.Text = "aa"
    c = sheet.Range["F17"]
    c.Text = "=Sum"
    c = sheet.Range["G17"]
    c.Text = "aa"
    c = sheet.Range["E18"]
    c.Text = "bbb"
    c = sheet.Range["F18"]
    c.Text = "sss"
    c = sheet.Range["G18"]
    c.Text = "=Max"

def AddBeginWith(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E15:G16"])
    sheet.Range["E15:G16"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E15:G16"].Style.Color = Color.get_LightGoldenrodYellow()
    cf = conds.AddBeginsWithCondition("ab")
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["E15"]
    c.Text = "aa"
    c = sheet.Range["F15"]
    c.Text = "abc"
    c = sheet.Range["G15"]
    c.Text = "aa"
    c = sheet.Range["E16"]
    c.Text = "bbb"
    c = sheet.Range["F16"]
    c.Text = "sss"
    c = sheet.Range["G16"]
    c.Text = "abcd"

def AddEndWith(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E13:G14"])
    sheet.Range["E13:G14"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E13:G14"].Style.Color = Color.get_LightGray()
    cf = conds.AddEndsWithCondition("ab")
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    c = sheet.Range["E13"]
    c.Text = "aa"
    c = sheet.Range["F13"]
    c.Text = "abc"
    c = sheet.Range["G13"]
    c.Text = "aab"
    c = sheet.Range["E14"]
    c.Text = "bbbc"
    c = sheet.Range["F14"]
    c.Text = "sab"
    c = sheet.Range["G14"]
    c.Text = "abcd"

def AddNotContainsBlank(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E11:G12"])
    sheet.Range["E11:G12"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E11:G12"].Style.Color = Color.get_LightCoral()
    cf = conds.AddNotContainsBlanksCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["E11"]
    c.Text = "aa"
    c = sheet.Range["F11"]
    c.Text = "  "
    c = sheet.Range["G11"]
    c.Text = "aab"
    c = sheet.Range["E12"]
    c.Text = "abc"
    c = sheet.Range["F12"]
    c.Text = "  "
    c = sheet.Range["G12"]
    c.Text = "abcd"

def AddContainsBlank(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E9:G10"])
    sheet.Range["E9:G10"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E9:G10"].Style.Color = Color.get_LightCyan()
    cf = conds.AddContainsBlanksCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    c = sheet.Range["E9"]
    c.Text = "aa"
    c = sheet.Range["F9"]
    c.Text = "  "
    c = sheet.Range["G9"]
    c.Text = "aab"
    c = sheet.Range["E10"]
    c.Text = "abc"
    c = sheet.Range["F10"]
    c.Text = "dvdf"
    c = sheet.Range["G10"]
    c.Text = "abcd"

def AddNotContainsText(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E7:G8"])
    sheet.Range["E7:G8"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E7:G8"].Style.Color = Color.get_LightGreen()
    cf = conds.AddNotContainsTextCondition("abc")
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    c = sheet.Range["E7"]
    c.Text = "aa"
    c = sheet.Range["F7"]
    c.Text = "abfd"
    c = sheet.Range["G7"]
    c.Text = "aab"
    c = sheet.Range["E8"]
    c.Text = "abc"
    c = sheet.Range["F8"]
    c.Text = "cedf"
    c = sheet.Range["G8"]
    c.Text = "abcd"

def AddContainsText(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["E5:G6"])
    sheet.Range["E5:G6"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E5:G6"].Style.Color = Color.get_LightBlue()
    cf = conds.AddContainsTextCondition("abc")
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    c = sheet.Range["E5"]
    c.Text = "aa"
    c = sheet.Range["F5"]
    c.Text = "abfd"
    c = sheet.Range["G5"]
    c.Text = "aab"
    c = sheet.Range["E6"]
    c.Text = "abc"
    c = sheet.Range["F6"]
    c.Text = "cedf"
    c = sheet.Range["G6"]
    c.Text = "abcd"

def AddDataBar2(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["E3:G4"])
    sheet.Range["E3:G4"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E3:G4"].Style.Color = Color.get_LightGreen()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.DataBar
    cf.DataBar.BarColor = Color.get_Orange()
    cf.DataBar.MinPoint.Type = ConditionValueType.Percentile
    cf.DataBar.MinPoint.Value = Double(30.78)
    cf.DataBar.ShowValue = False
    c = sheet.Range["E3"]
    c.NumberValue = 6
    c = sheet.Range["F3"]
    c.NumberValue = 9
    c = sheet.Range["G3"]
    c.NumberValue = 12
    c = sheet.Range["E4"]
    c.NumberValue = 8
    c = sheet.Range["F4"]
    c.NumberValue = 11
    c = sheet.Range["G4"]
    c.NumberValue = 14

def AddDataBar1(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["E1:G2"])
    sheet.Range["E1:G2"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["E1:G2"].Style.Color = Color.get_YellowGreen()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.DataBar
    cf.DataBar.BarColor = Color.get_Blue()
    cf.DataBar.MinPoint.Type = ConditionValueType.Percent
    cf.DataBar.ShowValue = True
    c = sheet.Range["E1"]
    c.NumberValue = 4
    c = sheet.Range["F1"]
    c.NumberValue = 7
    c = sheet.Range["G1"]
    c.NumberValue = 10
    c = sheet.Range["E2"]
    c.NumberValue = 6
    c = sheet.Range["F2"]
    c.NumberValue = 9
    c = sheet.Range["G2"]
    c.NumberValue = 14

def AddDefaultIconSet(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["A1:C2"])
    sheet.Range["A1:C2"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A1:C2"].Style.Color = Color.get_Yellow()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.IconSet
    sheet.Range["A1"].NumberValue = 0
    sheet.Range["B1"].NumberValue = 3
    sheet.Range["C1"].NumberValue = 6
    sheet.Range["A2"].NumberValue = 2
    sheet.Range["B2"].NumberValue = 5
    sheet.Range["C2"].NumberValue = 8

def AddDefaultColorScale(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["A5:C6"])
    sheet.Range["A5:C6"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A5:C6"].Style.Color = Color.get_Pink()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.ColorScale
    sheet.Range["A5"].NumberValue = 4
    sheet.Range["B5"].NumberValue = 7
    sheet.Range["C5"].NumberValue = 10
    sheet.Range["A6"].NumberValue = 6
    sheet.Range["B6"].NumberValue = 9
    sheet.Range["C6"].NumberValue = 12

def Add3ColorScale(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["A7:C8"])
    sheet.Range["A7:C8"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A7:C8"].Style.Color = Color.get_Green()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.ColorScale
    cf.ColorScale.MinValue.Type = ConditionValueType.Number
    cf.ColorScale.MinValue.Value = Int32(9)
    cf.ColorScale.MinColor = Color.get_Purple()
    sheet.Range["A7"].NumberValue = 6
    sheet.Range["B7"].NumberValue = 9
    sheet.Range["C7"].NumberValue = 12
    sheet.Range["A8"].NumberValue = 8
    sheet.Range["B8"].NumberValue = 11
    sheet.Range["C8"].NumberValue = 14

def Add2ColorScale(sheet):
    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["A9:C10"])
    sheet.Range["A9:C10"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A9:C10"].Style.Color = Color.get_White()
    cf = xcfs.AddCondition()
    cf.FormatType = ConditionalFormatType.ColorScale
    cf.ColorScale.MinColor = Color.get_Gold()
    cf.ColorScale.MaxColor = Color.get_SkyBlue()
    sheet.Range["A9"].NumberValue = 8
    sheet.Range["B9"].NumberValue = 12
    sheet.Range["C9"].NumberValue = 13
    sheet.Range["A10"].NumberValue = 10
    sheet.Range["B10"].NumberValue = 13
    sheet.Range["C10"].NumberValue = 16

def AddAboveAverage(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A11:C12"])
    sheet.Range["A11:C12"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A11:C12"].Style.Color = Color.get_Tomato()
    cf = conds.AddAverageCondition(AverageType.Above)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    sheet.Range["A11"].NumberValue = 10
    sheet.Range["B11"].NumberValue = 13
    sheet.Range["C11"].NumberValue = 16
    sheet.Range["A12"].NumberValue = 12
    sheet.Range["B12"].NumberValue = 15
    sheet.Range["C12"].NumberValue = 18

def AddAboveAverage2(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A13:C14"])
    sheet.Range["A13:C14"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A13:C14"].Style.Color = Color.get_LightPink()
    cf = conds.AddAverageCondition(AverageType.BelowEqual)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_LightSkyBlue()
    sheet.Range["A13"].NumberValue = 12
    sheet.Range["B13"].NumberValue = 15
    sheet.Range["C13"].NumberValue = 18
    sheet.Range["A14"].NumberValue = 14
    sheet.Range["B14"].NumberValue = 17
    sheet.Range["C14"].NumberValue = 20

def AddAboveAverage3(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A15:C16"])
    sheet.Range["A15:C16"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A15:C16"].Style.Color = Color.get_LightPink()
    cf = conds.AddAverageCondition(AverageType.AboveStdDev3)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_LightSkyBlue()
    sheet.Range["A15"].NumberValue = 12
    sheet.Range["B15"].NumberValue = 15
    sheet.Range["C15"].NumberValue = 18
    sheet.Range["A16"].NumberValue = 14
    sheet.Range["B16"].NumberValue = 17
    sheet.Range["C16"].NumberValue = 20

def AddTop10_1(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A17:C20"])
    sheet.Range["A17:C20"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A17:C20"].Style.Color = Color.get_Gray()
    cf = conds.AddTopBottomCondition(TopBottomType.Top, 10)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Yellow()
    sheet.Range["A17"].NumberValue = 16
    sheet.Range["B17"].NumberValue = 21
    sheet.Range["C17"].NumberValue = 26
    sheet.Range["A18"].NumberValue = 18
    sheet.Range["B18"].NumberValue = 23
    sheet.Range["C18"].NumberValue = 28
    sheet.Range["A19"].NumberValue = 20
    sheet.Range["B19"].NumberValue = 25
    sheet.Range["C19"].NumberValue = 30
    sheet.Range["A20"].NumberValue = 22
    sheet.Range["B20"].NumberValue = 27
    sheet.Range["C20"].NumberValue = 32

def AddTop10_2(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A21:C24"])
    sheet.Range["A21:C24"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A21:C24"].Style.Color = Color.get_Green()
    cf = conds.AddTopBottomCondition(TopBottomType.Bottom, 10)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Pink()
    sheet.Range["A21"].NumberValue = 20
    sheet.Range["B21"].NumberValue = 25
    sheet.Range["C21"].NumberValue = 30
    sheet.Range["A22"].NumberValue = 22
    sheet.Range["B22"].NumberValue = 27
    sheet.Range["C22"].NumberValue = 32
    sheet.Range["A23"].NumberValue = 24
    sheet.Range["B23"].NumberValue = 29
    sheet.Range["C23"].NumberValue = 34
    sheet.Range["A24"].NumberValue = 24
    sheet.Range["B24"].NumberValue = 31
    sheet.Range["C24"].NumberValue = 36

def AddTop10_3(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A25:C28"])
    sheet.Range["A25:C28"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A25:C28"].Style.Color = Color.get_Orange()
    cf = conds.AddTopBottomCondition(TopBottomType.TopPercent, 10)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Blue()
    sheet.Range["A25"].NumberValue = 24
    sheet.Range["B25"].NumberValue = 29
    sheet.Range["C25"].NumberValue = 34
    sheet.Range["A26"].NumberValue = 25
    sheet.Range["B26"].NumberValue = 36
    sheet.Range["C26"].NumberValue = 32
    sheet.Range["A27"].NumberValue = 24
    sheet.Range["B27"].NumberValue = 28
    sheet.Range["C27"].NumberValue = 31
    sheet.Range["A28"].NumberValue = 34
    sheet.Range["B28"].NumberValue = 26
    sheet.Range["C28"].NumberValue = 32

def AddTop10_4(sheet):
    conds = sheet.ConditionalFormats.Add()
    conds.AddRange(sheet.Range["A29:C32"])
    sheet.Range["A29:C32"].Style.FillPattern = ExcelPatternType.Solid
    sheet.Range["A29:C32"].Style.Color = Color.get_Gold()
    cf = conds.AddTopBottomCondition(TopBottomType.BottomPercent, 10)
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_Green()
    sheet.Range["A29"].NumberValue = 22
    sheet.Range["B29"].NumberValue = 33
    sheet.Range["C29"].NumberValue = 38
    sheet.Range["A30"].NumberValue = 30
    sheet.Range["B30"].NumberValue = 35
    sheet.Range["C30"].NumberValue = 39
    sheet.Range["A31"].NumberValue = 32
    sheet.Range["B31"].NumberValue = 37
    sheet.Range["C31"].NumberValue = 43
    sheet.Range["A32"].NumberValue = 34
    sheet.Range["B32"].NumberValue = 28
    sheet.Range["C32"].NumberValue = 32

outputFile = "多种条件格式.xlsx"

# 加载文档并添加工作表
workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]

# 对工作表应用多种条件格式
AddConditionalFormattingForNewSheet(sheet)

# 保存结果文档
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Python Excel条件格式设置

以上就是使用Python对Excel工作表中的数据应用多种条件格式的全部内容。感谢阅读！

大话 Python：python 操作 excel 系列 -- 数据的提取与筛选
2401_84140428的博客
05-01 1257
import numpy as npimport pandas as pddata_inner.loc[3]data_inner.loc[0:3]data_inner.loc[:3]data_inner.iloc[:2,:1]做了那么多年开发，自学了很多门编程语言，我很明白学习资源对于学一门新语言的重要性，这些年也收藏了不少的Python干货，对我来说这些东西确实已经用不到了，但对于准备自学Python的人来说，或许它就是一个宝藏，可以给你省去很多的时间和精力。别在网上瞎学了，我最近也做了一些资源的更新，只
python生成excel模板_使用python创建excel表格 --- XlsxWriter模板详解
weixin_39879674的博客
12-10 1262
前言最近在研究自动化巡检发邮件。因为是需要展现给领导看的，逼格当然得高，所以在邮件展现的地方需要用到excel模板，在网上查了下XlsxWriter的模板，发现还是挺强大的一个东西，能实现我所需要的高逼格：首先，XlsxWriter支持足够多的样式、图表，另外还支持函数，等各种DIY姿势。不足地方在于只能创建excel，无法去修改读取，只能是创建！一、创建一个表格从输出一个Hello world开...
python对Excel按条件进行内容补充(推荐)
12-31
关于xlrd/xlwt和openpyxl的差别 两者都是对于excel文件的操作插件，两者的主要区别在于写入操作， 其中xlwt针对Ecxec2007之前的版本，即.xls文件，其要求单个sheet不超过65535行， 而openpyxl则主要针对Excel2007之后的版本（.xlsx），它对文件大小没有限制。 另外还有区别就是二者在读写速度上的差异，xlrd/xlwt在读写方面的速度都要优于openpyxl，但xlwt无法生成xlsx openpyxl的用法 官方文档 先了解下Worksheet.cell() 方法。 这提供了对使用行和列表示法的单元格的访问： d = ws.cell(ro
openpyxl打开 sheet_关于python调用Excel“条件格式”——二探openpyxl
weixin_39789646的博客
11-21 408
熟悉我的人大概，知道我早期写过 “监控自觉系统”，“日报自处理程序”日报那个程序就是有用到openpyxl 来操作Excel不过还是要道歉本期的质量，还是第一次发文我得承认这是本来要发表在我的github上的README。但是今天不知道什么原因，我上传不了文件，也许是我两三年没更新的原因吧。你们就当读README吧，如果想要文件我会尽快弄好我的git，请期待 0 0# Report-form-to...
用python处理excel的基本语法_Python操作Excel学习笔记（28）：条件格式
weixin_39580124的博客
11-23 883
excelperfectExcel支持3种不同类型的条件格式：内置、标准和自定义。内置条件格式将特定规则与预定义样式相组合。标准条件格式将特定规则与自定义格式相组合。此外，可以定义自定义公式来应用使用不同样式的自定义格式。创建条件格式的基本语法为：>>>fromopenpyxl.formattingimportRule>>>fromopenpyxl.stylesimportFont, Patter...
python excel条件格式_条件格式 — openpyxl 3.0.5 文档
weixin_39962889的博客
11-30 1079
>>>from openpyxl import Workbook>>>from openpyxl.styles import Color, PatternFill, Font, Border>>>from openpyxl.styles.differential import DifferentialStyle>>>from openpyxl.formatting.rule import Colo...
python excel条件格式_Python数据探索，直接用pandas做条件格式，看清数据走势
weixin_39621495的博客
11-30 170
转发本文并私信我"python"，即可获得Python资料以及各种心得(持续更新的)前言之前已经多篇文章关于使用 pandas 处理数据，那仅仅是工作的开端，只是把数据整理完毕而不继续探索数据，那么就白白浪费了 Python 这样的好工具。今天要探索的数据来自于微软的 power bi 关于 TV Game 的销量相关案例，我们开始吧。本文概要通过本文你将会学到以下知识点：pandas 连接多表数...
Python Excel-XLW File-Maker-开源
04-28
而"Python Excel-XLW File-Maker"可能使用了xlwt库，这是一个用于创建.xls格式Excel文件的纯Python库，能够实现基本的Excel功能，如创建工作表、设置单元格格式、插入图表等。 在项目中，`xlw_opcodes.py`和`xlw_...
Python对Excel操作的详解-综合文档
05-20
更复杂的需求可能涉及到条件格式化、公式计算、数据验证等，这些可以通过openpyxl或其他库如xlwings来实现。 7. **性能优化**： 当处理大量数据时，可以使用`chunksize`参数分块读取Excel文件，或者使用内存高效...
Python库 | pyexcel-htmlr-0.6.0.tar.gz
03-09
2. **转换为Excel格式**：解析出的表格数据可以被保存为Excel文件，支持`.xls`、`.xlsx`等多种格式。 3. **自定义处理**：在读取和转换过程中，你可以根据需要对数据进行清洗、过滤等预处理，提高数据质量。 此外，...
python excel条件格式_python – 条件格式单元格,基于它在另一列中的对应值
weixin_40001634的博客
11-30 1060
Excel似乎不喜欢字符串上条件格式的单引号.如果你在里面有双引号,它可以工作,即"criteria": '=($B$2="y")'与"criteria": "=($B$2='y')"我在下面给出了一个完整的可重现的示例,其中包含解决方案的屏幕截图.import pandas as pddf = pd.DataFrame({"Name": ["A", "B", "C"], "Status": ['...
python excel条件格式_使用Python的xlsxwri在Excel中使用字符串条件格式“等于”
weixin_39903571的博客
11-30 464
我有相对大的Excel电子表格，我在那里应用条件格式。但是，单元格的内容相对较短（最多3个字母）。所以，我需要完全匹配一个字符串。例如：“A”应该格式化，但不能再包含“A”（“ABC”、“BCA”、“BAC”等）。在我尝试了不同的选择使用'文本'和'细胞'选项，但我惨败。这是我的测试用例：import xlsxwriterworkbook = xlsxwriter.Workbook('condit...
python excel条件格式_整列转换为Excel条件格式
weixin_39668890的博客
11-30 613
我想写一个熊猫数据框到Excel格式。为此，我使用xlsxwriter。我有两个问题：首先，如何对整列应用条件格式？在examples中，它们使用特定的单元格区域格式（例如worksheet1.conditional_format('B3:K12', ...)），有没有方法可以引用整个列？其次，如果B列与C列相差超过15%，我想用红色标记B列。正确的公式是什么？我目前正在尝试下面的代码，但不起作用...
python 如何在excel某一列加上条件格式
weixin_41403661的博客
06-30 214
Python,Excel,代码示例相关视频讲解：python的or运算赋值用法用python编程Excel有没有用处？011_编程到底好玩在哪？查看python文件_输出py文件_cat_运行python文件_shel在Excel某一列加上条件格式 在日常工作中，我们经常需要将Excel表格中的数据通过条件格式进行高...
【Python基础】pandas的骚操作：一行 pandas 代码搞定 Excel “条件格式”！
fengdu78的博客
01-28 1020
来源：Python数据科学作者：东哥起飞本篇是pandas100个骚操作系列的第 7篇：一行 pandas 代码搞定 Excel “条件格式”！系列内容，请看????「pandas10...
如何用Python操作Excel自动化办公？一个案例教会你openpyxl——样式和条件格式
Data_lukyo的博客
02-22 2964
虽然现在各种各样强大的工具很多，但是人人都离不开Excel，原因就在于简单易用、快捷DIY。它不仅是CRUD（增删改查）的工具，也是可视化设计便利的工具。日常工作中，除了基本的数据处理，还需要对Excel表格、单元格、图表进行格式整理、样式美化，便于直观明确的传达信息。本篇接着上两篇讲解如何让通过openpyxl进行单元格合并、边框、字体、颜色、行高列宽、对齐等功能。
openpyxl给excel设置条件格式
WLB 的博客
12-03 3383
对数据的重要程度，进行颜色渐变的操作记录
Python Openpyxl给Excel增加条件规则
最新发布
neweastsun的专栏
03-11 1305
在进入代码实现示例之前，让我们先了解openpyxl中与条件格式相关的一些重要概念。使用openpyxl python库添加条件格式是一个非常简单的过程，一点也不令人困惑。在本教程之后，我们将能够在excel文件中执行条件格式。在本文的代码实现示例中，我们讨论了三种主要的条件格式技术，如基于单元格的规则、颜色比例和基于公式的规则。
python xlsxwriter 条件格式_xlsxwriter 操作 Excel
weixin_36302268的博客
02-09 468
xlsxwriter 简介用于以Excel 2007+ XLSX文件格式编写文件，相较之下PhpSpreadsheet支持更多的格式读写。优点文本，数字和公式写入，速度很快，占用内存小支持诸如格式设置，图像，图表，页面设置，自动过滤器，条件格式设置等功能缺点无法读取或修改现有的Excel XLSX文件演示其使用流程，与你使用excel流程一致，只不过将你主步骤分解成了一个个对象实例来操作，通过引用...
Python库excel-dates-0.0.2：解析Excel日期数据包
这可能包括日期格式的转换、日期的解析和生成、与Excel日期相关的时间计算等。Python库如pandas、openpyxl和xlrd等也提供了处理Excel文件的功能，但“excel-dates-0.0.2.tar.gz”可能提供了专门针对日期处理的工具或...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值