使用Python設(shè)置Excel條件格式的完整指南

更新時(shí)間：2025年11月07日 09:55:58 作者：SunnyDays1011

條件格式是電子表格軟件中的一項(xiàng)數(shù)據(jù)可視化功能,這篇博客將探討如何使用Python對Excel表格中的數(shù)據(jù)應(yīng)用條件格式,感興趣的小伙伴可以了解一下

條件格式是電子表格軟件（如Microsoft Excel、Google Sheets、WPS表格等）中的一項(xiàng)數(shù)據(jù)可視化功能。它允許用戶通過預(yù)定義的條件規(guī)則，自動改變單元格或單元格區(qū)域的外觀樣式（包括但不限于背景顏色、字體樣式、邊框格式、數(shù)據(jù)條、色階、圖標(biāo)集等），從而將數(shù)據(jù)中的特定模式、趨勢或異常值以直觀、醒目的方式呈現(xiàn)出來。這篇博客將探討如何使用Python對Excel表格中的數(shù)據(jù)應(yīng)用條件格式。

工具與環(huán)境設(shè)置

在使用Python操作Excel條件格式前，需完成以下環(huán)境準(zhǔn)備：

安裝Python

若未安裝Python，可從Python官網(wǎng)下載對應(yīng)版本并完成安裝。

安裝Free Spire.XLS庫

通過以下命令安裝Free Spire.XLS for Python庫以操作Excel文件并應(yīng)用條件格式：

pip install spire.xls.free

了解Excel中的條件格式規(guī)則

Excel提供了豐富的條件格式規(guī)則，以滿足各種數(shù)據(jù)分析和可視化場景。以下是一些常用條件格式規(guī)則及功能說明：

類型	功能描述
單元格值	基于數(shù)值比較條件（如大于、小于、介于）設(shè)置格式
色階	通過漸變色直觀呈現(xiàn)數(shù)據(jù)分布（如紅-黃-綠色階）
數(shù)據(jù)條	在單元格內(nèi)顯示橫向比例條（支持實(shí)心/漸變樣式），直觀反映數(shù)值相對大小
圖標(biāo)集	使用符號集（如箭頭/旗幟/交通燈等）表示數(shù)據(jù)趨勢或狀態(tài)
最前/最后	突出顯示排名靠前/后的數(shù)據(jù)（如前10項(xiàng)、前10%、高于平均值等）
重復(fù)值/唯一值	標(biāo)識重復(fù)數(shù)據(jù)或唯一值
發(fā)生日期	按時(shí)間特征設(shè)置格式（如"最近7天"、"本月"、"昨天"等時(shí)間段）
空值/非空值	標(biāo)記空白單元格或含數(shù)據(jù)單元格
自定義公式	通過自定義Excel公式創(chuàng)建高級格式規(guī)則
文本包含	根據(jù)文本特征設(shè)置格式（如包含/開頭為/結(jié)尾為特定字符等）
整行/整列	基于條件自動格式化整行或整列
錯誤值	標(biāo)識錯誤值（如#N/A、#VALUE!等錯誤類型）

使用Python對Excel表格數(shù)據(jù)應(yīng)用條件格式

下面幾個(gè)例子展示了如何使用Python在Excel中應(yīng)用幾種常見的條件格式規(guī)則。文章末尾提供了完整代碼，涵蓋幾乎所有條件格式的實(shí)現(xiàn)方法。

示例1：單元格值

基于單元格值的條件格式規(guī)則允許用戶根據(jù)單元格內(nèi)的數(shù)值應(yīng)用規(guī)則，以高亮顯示滿足特定條件的單元格（例如，大于或小于某個(gè)值）。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]

# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定義條件
condition = format.AddCondition()
# 設(shè)置格式類型為單元格值
condition.FormatType = ConditionalFormatType.CellValue
# 設(shè)置比較運(yùn)算符為"大于"
condition.Operator = ComparisonOperatorType.Greater
# 指定閾值
condition.FirstFormula = "75"
# 設(shè)置背景色為黃色
condition.BackColor = Color.FromRgb(204, 204, 0)  

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

示例2：色階

色階規(guī)則通過使用漸變色來顯示單元格的值，使數(shù)據(jù)分布更加直觀，特別適合在大數(shù)據(jù)集中識別趨勢和模式。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]

# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定義條件
condition = format.AddCondition()
# 設(shè)置格式類型為色階
condition.FormatType = ConditionalFormatType.ColorScale

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

示例3：數(shù)據(jù)條

數(shù)據(jù)條規(guī)則通過在單元格內(nèi)添加橫向比例條來直觀反映數(shù)值大小，便于快速比較數(shù)值。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]

# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定義條件
condition = format.AddCondition()
# 設(shè)置格式類型為數(shù)據(jù)條
condition.FormatType = ConditionalFormatType.DataBar
# 設(shè)置數(shù)據(jù)條填充模式為漸變填充
condition.DataBar.BarFillType = DataBarFillType.DataBarFillGradient
# 設(shè)置數(shù)據(jù)條顏色
condition.DataBar.BarColor = Color.FromRgb(99, 142, 198)

# 保存修改后的工作簿
workbook.SaveToFile("數(shù)據(jù)條.xlsx", ExcelVersion.Version2016)
# 釋放工作簿資源
workbook.Dispose()

示例4：圖標(biāo)集

圖標(biāo)集規(guī)則通過使用符號（如箭頭或交通燈）來表示數(shù)據(jù)趨勢和類別，便于快速傳達(dá)數(shù)據(jù)狀態(tài)。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
worksheet = workbook.Worksheets[0]

# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
format = worksheet.ConditionalFormats.Add()
format.AddRange(worksheet.Range["B2:B6"])

# 定義條件
condition = format.AddCondition()
# 設(shè)置格式類型為圖標(biāo)集
condition.FormatType = ConditionalFormatType.IconSet
# 定義圖標(biāo)集類型為四向箭頭
condition.IconSet.IconSetType = IconSetType.FourArrows

# 保存修改后的工作簿
workbook.SaveToFile("圖標(biāo)集.xlsx", ExcelVersion.Version2016)
# 釋放工作簿資源
workbook.Dispose()

示例5：最前/最后

最前/最后規(guī)則用于高亮顯示數(shù)據(jù)集中的最高值或最低值，便于識別關(guān)鍵數(shù)據(jù)點(diǎn)。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
sheet = workbook.Worksheets[0]

# 為"B2:B6"范圍創(chuàng)建條件格式規(guī)則
format_1 = sheet.ConditionalFormats.Add()
format_1.AddRange(sheet.Range["B2:B6"])

# 添加條件
condition_1 = format_1.AddTopBottomCondition(TopBottomType.Top, 1)
# 設(shè)置高亮顏色為紅色
condition_1.BackColor = Color.get_Red()

# 創(chuàng)建另一個(gè)條件格式規(guī)則
format_2 = sheet.ConditionalFormats.Add()
format_2.AddRange(sheet.Range["B2:B6"])

# 添加條件
condition_2 = format_2.AddTopBottomCondition(TopBottomType.Bottom, 1)
# 設(shè)置高亮顏色為森林綠
condition_2.BackColor = Color.get_ForestGreen()

# 保存修改后的工作簿
workbook.SaveToFile("最前最后規(guī)則.xlsx", ExcelVersion.Version2016)
# 釋放工作簿資源
workbook.Dispose()

示例6：重復(fù)值/唯一值

重復(fù)值/唯一值規(guī)則有助于識別數(shù)據(jù)中的重復(fù)數(shù)據(jù)或唯一數(shù)據(jù)，特別適用于數(shù)據(jù)驗(yàn)證和保證數(shù)據(jù)完整性的場景。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
sheet = workbook.Worksheets[0]

# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
format_1 = sheet.ConditionalFormats.Add()
format_1.AddRange(sheet.Range["B2:B6"])

# 定義條件
condition_1 = format_1.AddCondition()
condition_1.FormatType = ConditionalFormatType.DuplicateValues
# 設(shè)置高亮顏色為淺黃色
condition_1.BackColor = Color.get_LightYellow()

# 創(chuàng)建另一個(gè)條件格式規(guī)則
format_2 = sheet.ConditionalFormats.Add()
format_2.AddRange(sheet.Range["B2:B6"])

# 定義條件
condition_2 = format_2.AddCondition()
condition_2.FormatType = ConditionalFormatType.UniqueValues
# 設(shè)置高亮顏色為天藍(lán)色
condition_2.BackColor = Color.get_SkyBlue()

# 保存修改后的工作簿
workbook.SaveToFile("重復(fù)或唯一值.xlsx", ExcelVersion.Version2016)
# 釋放工作簿資源
workbook.Dispose()

示例7：發(fā)生日期

發(fā)生日期條件格式規(guī)則允許根據(jù)單元格中的日期特征（如今天/本周/本月等時(shí)間范圍）自動設(shè)置特殊格式。

實(shí)現(xiàn)代碼：

from spire.xls import *

# 初始化工作簿并加載現(xiàn)有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")

# 獲取第一個(gè)工作表
sheet = workbook.Worksheets[0]

# 為單元格區(qū)域"C2:C6"創(chuàng)建條件格式規(guī)則
format = sheet.ConditionalFormats.Add()
format.AddRange(sheet.Range["C2:C6"])

# 添加條件
condition = format.AddTimePeriodCondition(TimePeriodType.LastMonth)
# 設(shè)置高亮顏色為橙色
condition.BackColor = Color.get_Orange()

# 保存修改后的工作簿
workbook.SaveToFile("發(fā)生日期.xlsx", ExcelVersion.Version2016)
# 釋放工作簿資源
workbook.Dispose()

完整代碼

from spire.xls import *

# 給Excel工作表添加條件格式
def AddConditionalFormattingForNewSheet(sheet):
    # 添加圖標(biāo)集
    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)

    # 添加最前/最后規(guī)則
    AddTop10_1(sheet)
    AddTop10_2(sheet)
    AddTop10_3(sheet)
    AddTop10_4(sheet)

    # 添加數(shù)據(jù)條
    AddDataBar1(sheet)
    AddDataBar2(sheet)

    # 添加文本包含規(guī)則
    AddContainsText(sheet)
    AddNotContainsText(sheet)

# 添加空值/非空值規(guī)則
    AddContainsBlank(sheet)
    AddNotContainsBlank(sheet)
    AddBeginWith(sheet)
    AddEndWith(sheet)

# 添加錯誤值規(guī)則
    AddContainsError(sheet)
    AddNotContainsError(sheet)
    AddDuplicate(sheet)
    AddUnique(sheet)

    # 添加發(fā)生日期規(guī)則
    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)

    # 設(shè)置工作表列寬和行高自適應(yīng)
    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]

# 對工作表應(yīng)用多種條件格式
AddConditionalFormattingForNewSheet(sheet)

# 保存結(jié)果文檔
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()