在openpyxl中，是否有一种方法可以查看对单元格应用了哪些条件格式规则？开发者社区

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

文章/答案/技术大牛

发布

from openpyxl import load_workbook
wb = load_workbook('Workbook_Name.xlsx', data_only = True)
ws = wb['Worksheet1']
# Code that shows me each cf rule's formula, fill type, priority, etc
cellrangeslist = list(ws.conditional_formatting._cf_rules)
for cellrange in cellrangeslist:
    print('{:30s}{:^10s}{:30s}'.format('----------------------------',str(cellrange.sqref),'----------------------------'))
    for i in cellrange.cfRule:
        print('{:10s}{:8s}{:40s}{:10s}{:10s}'.format(str(i.dxf.fill.bgColor.index), str(i.dxf.fill.bgColor.type), str(i.formula), str(i.stopIfTrue), str(i.priority)))
# This is where I want to be able to identify which cf rule is applied to a given cell
# Code that interprets cell styling into appropriate tags, e.g.
for r in ws.iter_rows(min_row = ws.min_row, max_row = ws.max_row, min_col = ws.min_column, max_col = ws.max_column):
     for cell in r:
          if cell.font.b == True:
                 cell.value = "[bold]"+cell.value
# Code to write each cell as a string literal to a CSV file
#

----------------------------      C1    ----------------------------
FF92D050  rgb     ['$A1-$B1>0']                           None      2
FFFF0000  rgb     ['$A1-$B1<0']                           None      1

import xlwings as xw
from xlwings.constants import RgbColor
def colour_lookup(cfc):
    cell_colour = (key for key, value in colour_dict.items() if value == cfc)
    for key in cell_colour:
        return key
colour_dict = { key: getattr(RgbColor, key) for key in dir(RgbColor) if not key.startswith('_') }
wb = xw.Book('test.xlsx)
ws = wb.sheets('Sheet1')
cf = ws['C1'].api.FormatConditions
print("Number of conditional formatting rules: " + str(cf._inner.Count))
print("Colour applied to conditional format cell:\n\tEnumerated: " +
      str(cf._inner.Parent.DisplayFormat.Interior.Color))
print("\tRGBColor: " + colour_lookup(cf._inner.Parent.DisplayFormat.Interior.Color))
print("------------------------------------------------")
for idx, cf_detail in enumerate(cf, start=1):
    print("Conditional Format " + str(idx))
    print(cf_detail._inner.Formula1)
    print(cf_detail._inner.Interior.Color)
    print("\tRGBColor: " + colour_lookup(cf_detail._inner.Interior.Color))
    print("")

Number of conditional formatting rules: 2
Colour applied to conditional format cell:
    Enumerated: 32768.0
    RGBColor: rgbGreen
------------------------------------------------
Conditional Format 1
=$A1-$B1<0
255.0

推荐文章

爱旅游的丝瓜 · 2026 Feature Highlights C

3 月前

俊秀的鼠标垫 · Office精灵精华演示

1 月前

会搭讪的骆驼 · excel重复项求和技巧，如何快速实现数据合并？ • 简道云

1 月前

无聊的杨桃 · Excel VBA技术问询：从单个单元格提取多值分配给多个变量

1 月前

体贴的炒面 · 用Excel软件更改大小写-联想乐享知识库

1 月前

呐喊的石榴 · Welcome to Apache NetBeans

2 月前

独立的滑板 · OutOfMemoryException from DUCE.Channel.SyncFlush() · Issue #8397 · dotnet/wpf · GitHub

9 月前

开心的棒棒糖 · Xiaojun Ding-西安交通大学人文社会科学学院

12 月前

茫然的海豚 · Warhol/Cutrone - Exhibitions - Galerie Gmurzynska

12 月前

潇洒的弓箭 · python-pandapower电力系统短路电流计算（算例2：基于短路计算的开关状态评估））_pandapower短路电流计算-CSDN博客

1 年前

问 在openpyxl中，是否有一种方法可以查看对单元格应用了哪些条件格式规则？ EN

回答 1

Stack Overflow用户