python 创建 excel 条件格式formatconditions

这些例子都使用 xlwings

1
2
import xlwings as xw
from xlwings.utils import rgb_to_int

  

示例1:添加数据条 Databar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
def set_formatconditions_bar(self, i, j):<br>    
        # 获取区域范围
        rng_dl = self.yx_sheet1.range((2,j),(i,j))
        rng_dl.api.FormatConditions.Delete()
        # 添加数据条
        rng_dl.api.FormatConditions.AddDatabar()
        rng_dl.api.FormatConditions(rng_dl.api.FormatConditions.Count).SetFirstPriority()
        rng_dl.api.FormatConditions(1).MinPoint.Modify.newtype = xw.constants.ConditionValueTypes.xlConditionValueAutomaticMin
        rng_dl.api.FormatConditions(1).MaxPoint.Modify.newtype = xw.constants.ConditionValueTypes.xlConditionValueAutomaticMax
        # 数据条颜色
        rng_dl.api.FormatConditions(1).BarColor.Color = rgb_to_int([255,222,125])
        rng_dl.api.FormatConditions(1).BarColor.TintAndShade = 0
        # 数据条颜色方向等设置
        rng_dl.api.FormatConditions(1).BarFillType = xw.constants.DataBarFillType.xlDataBarFillSolid
        rng_dl.api.FormatConditions(1).Direction = xw.constants.Constants.xlContext
        rng_dl.api.FormatConditions(1).NegativeBarFormat.ColorType = xw.constants.DataBarNegativeColorType.xlDataBarColor
        rng_dl.api.FormatConditions(1).BarBorder.Type = xw.constants.DataBarBorderType.xlDataBarBorderNone
        rng_dl.api.FormatConditions(1).AxisPosition = xw.constants.DataBarAxisPosition.xlDataBarAxisAutomatic
     # axis颜色 设置
        rng_dl.api.FormatConditions(1).AxisColor.Color = rgb_to_int([255,255,255])
        rng_dl.api.FormatConditions(1).AxisColor.TintAndShade = 0

  

示例2:添加 三色刻度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
def set_formatconditions_colorscale(self,i,j):
        # 选择区域
        rng = sheet1.range((i+1,3),(i+1,j-4))
        # 删除原来的 条件格式
        rng.api.FormatConditions.Delete
        # 添加色彩刻度, 2为双色, 3为三色
        rng.api.FormatConditions.AddColorScale(ColorScaleType=3)
        rng.api.FormatConditions(rng.api.FormatConditions.Count).SetFirstPriority()
        # 最小值颜色设置
        rng.api.FormatConditions(1).ColorScaleCriteria(1).Type = xw.constants.ConditionValueTypes.xlConditionValueLowestValue
        rng.api.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = rgb_to_int([107,177,137])
        # 中间值颜色设置
        rng.api.FormatConditions(1).ColorScaleCriteria(2).Type = xw.constants.ConditionValueTypes.xlConditionValuePercentile
        rng.api.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = rgb_to_int([255,232,115]) # [255,255,255][255,216,64]
        # 最大值颜色设置
        rng.api.FormatConditions(1).ColorScaleCriteria(3).Type = xw.constants.ConditionValueTypes.xlConditionValueHighestValue
        rng.api.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = rgb_to_int([250,128,114])       
        

  

posted @   dontbealarmedimwithy  阅读(918)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
点击右上角即可分享
微信分享提示