使用Python設置Excel條件格式的完整指南
更新時間：2025年11月07日 09:55:58 作者：SunnyDays1011
條件格式是電子表格軟件中的一項數據可視化功能,這篇博客將探討如何使用Python對Excel表格中的數據應用條件格式,感興趣的小伙伴可以了解一下
條件格式是電子表格軟件（如Microsoft Excel、Google Sheets、WPS表格等）中的一項數據可視化功能。它允許用戶通過預定義的條件規(guī)則，自動改變單元格或單元格區(qū)域的外觀樣式（包括但不限于背景顏色、字體樣式、邊框格式、數據條、色階、圖標集等），從而將數據中的特定模式、趨勢或異常值以直觀、醒目的方式呈現出來。這篇博客將探討如何使用Python對Excel表格中的數據應用條件格式。
工具與環(huán)境設置
在使用Python操作Excel條件格式前，需完成以下環(huán)境準備：
安裝Python
若未安裝Python，可從Python官網下載對應版本并完成安裝。
安裝Free Spire.XLS庫
通過以下命令安裝Free Spire.XLS for Python庫以操作Excel文件并應用條件格式：
pip install spire.xls.free
了解Excel中的條件格式規(guī)則
Excel提供了豐富的條件格式規(guī)則，以滿足各種數據分析和可視化場景。以下是一些常用條件格式規(guī)則及功能說明：
| 類型 | 功能描述 |
| 單元格值 | 基于數值比較條件（如大于、小于、介于）設置格式 |
| 色階 | 通過漸變色直觀呈現數據分布（如紅-黃-綠色階） |
| 數據條 | 在單元格內顯示橫向比例條（支持實心/漸變樣式），直觀反映數值相對大小 |
| 圖標集 | 使用符號集（如箭頭/旗幟/交通燈等）表示數據趨勢或狀態(tài) |
| 最前/最后 | 突出顯示排名靠前/后的數據（如前10項、前10%、高于平均值等） |
| 重復值/唯一值 | 標識重復數據或唯一值 |
| 發(fā)生日期 | 按時間特征設置格式（如"最近7天"、"本月"、"昨天"等時間段） |
| 空值/非空值 | 標記空白單元格或含數據單元格 |
| 自定義公式 | 通過自定義Excel公式創(chuàng)建高級格式規(guī)則 |
| 文本包含 | 根據文本特征設置格式（如包含/開頭為/結尾為特定字符等） |
| 整行/整列 | 基于條件自動格式化整行或整列 |
| 錯誤值 | 標識錯誤值（如#N/A、#VALUE!等錯誤類型） |
使用Python對Excel表格數據應用條件格式
下面幾個例子展示了如何使用Python在Excel中應用幾種常見的條件格式規(guī)則。文章末尾提供了完整代碼，涵蓋幾乎所有條件格式的實現方法。
示例1：單元格值
基于單元格值的條件格式規(guī)則允許用戶根據單元格內的數值應用規(guī)則，以高亮顯示滿足特定條件的單元格（例如，大于或小于某個值）。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
worksheet = workbook.Worksheets[0]
# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
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：色階
色階規(guī)則通過使用漸變色來顯示單元格的值，使數據分布更加直觀，特別適合在大數據集中識別趨勢和模式。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
worksheet = workbook.Worksheets[0]
# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
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：數據條
數據條規(guī)則通過在單元格內添加橫向比例條來直觀反映數值大小，便于快速比較數值。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
worksheet = workbook.Worksheets[0]
# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
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：圖標集
圖標集規(guī)則通過使用符號（如箭頭或交通燈）來表示數據趨勢和類別，便于快速傳達數據狀態(tài)。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
worksheet = workbook.Worksheets[0]
# 為單元格區(qū)域"B2:B6"創(chuàng)建條件格式規(guī)則
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：最前/最后
最前/最后規(guī)則用于高亮顯示數據集中的最高值或最低值，便于識別關鍵數據點。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
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)
# 設置高亮顏色為紅色
condition_1.BackColor = Color.get_Red()
# 創(chuàng)建另一個條件格式規(guī)則
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("最前最后規(guī)則.xlsx", ExcelVersion.Version2016)
# 釋放工作簿資源
workbook.Dispose()
示例6：重復值/唯一值
重復值/唯一值規(guī)則有助于識別數據中的重復數據或唯一數據，特別適用于數據驗證和保證數據完整性的場景。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
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
# 設置高亮顏色為淺黃色
condition_1.BackColor = Color.get_LightYellow()
# 創(chuàng)建另一個條件格式規(guī)則
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：發(fā)生日期
發(fā)生日期條件格式規(guī)則允許根據單元格中的日期特征（如今天/本周/本月等時間范圍）自動設置特殊格式。
實現代碼：
from spire.xls import *
# 初始化工作簿并加載現有Excel文件
workbook = Workbook()
workbook.LoadFromFile("測試.xlsx")
# 獲取第一個工作表
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)
# 設置高亮顏色為橙色
condition.BackColor = Color.get_Orange()
# 保存修改后的工作簿
workbook.SaveToFile("發(fā)生日期.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)
# 添加最前/最后規(guī)則
AddTop10_1(sheet)
AddTop10_2(sheet)
AddTop10_3(sheet)
AddTop10_4(sheet)
# 添加數據條
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)
# 設置工作表列寬和行高自適應
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條件格式內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了Python的print用法示例,需要的朋友可以參考下2014-02-02
這篇文章主要介紹了Django框架實現逆向解析url的方法,結合實例形式分析了Django逆向解析URL的原理、步驟、相關實現技巧與注意事項,需要的朋友可以參考下2018-07-07
基于騰訊云服務器部署微信小程序后臺服務（Python+Django）
這篇文章主要介紹了基于騰訊云服務器部署微信小程序后臺服務（Python+Django）,小編覺得挺不錯的，現在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-05-05
這篇文章主要介紹了基于Python實現天天酷跑功能,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了python實現自動登錄人人網并訪問最近來訪者實例,該實例是在前面登錄人人網實例基礎上的擴展,是非常實用的一個技巧,需要的朋友可以參考下2014-09-09
本文主要介紹了Python配置文件管理之ini和yaml文件讀取，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-02-02
django authenticate用戶身份認證的項目實踐
Django的contrib.auth模塊中的authenticate()函數用于對用戶的憑據進行身份驗證,本文就來介紹一下django authenticate用戶身份認證的使用,具有一定的參考價值,感興趣的可以了解一下2023-08-08
Python 模塊(Module),是一個 Python 文件,以 .py 結尾,包含了 Python對象定義和Python語句,下面這篇文章主要給大家介紹了關于Python模塊導入的相關資料,需要的朋友可以參考下2021-10-10
這篇文章主要介紹了python小程序實現刷票功能詳解,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-07-07
這篇文章主要為大家介紹了python函數實例萬花筒實現過程詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-06-06