文章目录
- 基于单元格值的条件格式规则允许用户根据单元格内的数值应用规则，以高亮显示满足特定条件的单元格（例如，大于或小于某个值）。 实现代码： 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()
- 色阶规则通过使用渐变色来显示单元格的值，使数据分布更加直观，特别适合在大数据集中识别趋势和模式。 实现代码： 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()
- 数据条规则通过在单元格内添加横向比例条来直观反映数值大小，便于快速比较数值。 实现代码： 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()
- 图标集规则通过使用符号（如箭头或交通灯）来表示数据趋势和类别，便于快速传达数据状态。 实现代码： 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()
- 最前/最后规则用于高亮显示数据集中的最高值或最低值，便于识别关键数据点。 实现代码： 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()
- 重复值/唯一值规则有助于识别数据中的重复数据或唯一数据，特别适用于数据验证和保证数据完整性的场景。 实现代码： 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()
- 发生日期条件格式规则允许根据单元格中的日期特征（如今天/本周/本月等时间范围）自动设置特殊格式。 实现代码： 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()
目录
- 工具与环境设置
- 了解Excel中的条件格式规则
- 使用Python对Excel表格数据应用条件格式
- 示例1：单元格值
- 示例2：色阶
- 示例3：数据条
- 示例4：图标集
- 示例5：最前/最后
- 示例6：重复值/唯一值
- 示例7：发生日期
- 完整代码
条件格式是电子表格软件（如Microsoft Excel、Google Sheets、WPS表格等）中的一项数据可视化功能。它允许用户通过预定义的条件规则，自动改变单元格或单元格区域的外观样式（包括但不限于背景颜色、字体样式、边框格式、数据条、色阶、图标集等），从而将数据中的特定模式、趋势或异常值以直观、醒目的方式呈现出来。这篇博客将探讨如何使用Python对Excel表格中的数据应用条件格式。
在使用Python操作Excel条件格式前，需完成以下环境准备：
安装Python
若未安装Python，可从Python官网下载对应版本并完成安装。
安装Free Spire.XLS库
通过以下命令安装Free Spire.XLS for Python库以操作Excel文件并应用条件格式：
pip install spire.xls.free
Excel提供了丰富的条件格式规则，以满足各种数据分析和可视化场景。以下是一些常用条件格式规则及功能说明：
| 类型 | 功能描述 |
| 单元格值 | 基于数值比较条件（如大于、小于、介于）设置格式 |
| 色阶 | 通过渐变色直观呈现数据分布（如红-黄-绿色阶） |
| 数据条 | 在单元格内显示横向比例条（支持实心/渐变样式），直观反映数值相对大小 |
| 图标集 | 使用符号集（如箭头/旗帜/交通灯等）表示数据趋势或状态 |
| 最前/最后 | 突出显示排名靠前/后的数据（如前10项、前10%、高于平均值等） |
| 重复值/唯一值 | 标识重复数据或唯一值 |
| 发生日期 | 按时间特征设置格式（如"最近7天"、"本月"、"昨天"等时间段） |
| 空值/非空值 | 标记空白单元格或含数据单元格 |
| 自定义公式 | 通过自定义Excel公式创建高级格式规则 |
| 文本包含 | 根据文本特征设置格式（如包含/开头为/结尾为特定字符等） |
| 整行/整列 | 基于条件自动格式化整行或整列 |
| 错误值 | 标识错误值（如#N/A、#VALUE!等错误类型） |
下面几个例子展示了如何使用Python在Excel中应用几种常见的条件格式规则。文章末尾提供了完整代码，涵盖几乎所有条件格式的实现方法。
基于单元格值的条件格式规则允许用户根据单元格内的数值应用规则，以高亮显示满足特定条件的单元格（例如，大于或小于某个值）。
实现代码：
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()
色阶规则通过使用渐变色来显示单元格的值，使数据分布更加直观，特别适合在大数据集中识别趋势和模式。
实现代码：
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()
数据条规则通过在单元格内添加横向比例条来直观反映数值大小，便于快速比较数值。
实现代码：
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()
图标集规则通过使用符号（如箭头或交通灯）来表示数据趋势和类别，便于快速传达数据状态。
实现代码：
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()
最前/最后规则用于高亮显示数据集中的最高值或最低值，便于识别关键数据点。
实现代码：
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()
重复值/唯一值规则有助于识别数据中的重复数据或唯一数据，特别适用于数据验证和保证数据完整性的场景。
实现代码：
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()
发生日期条件格式规则允许根据单元格中的日期特征（如今天/本周/本月等时间范围）自动设置特殊格式。
实现代码：
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复制Excel带有条件格式的单元格sheet实现步骤
- 使用Python设置Excel单元格数字的显示格式
- Python不改变Excel单元格样式方式—xls和xlsx两种格式
- Python 如何写入Excel格式和颜色
- Python xlwt设置excel单元格字体及格式