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

文章/答案/技术大牛

发布

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

推荐文章

乐观的黄花菜 · 如何将excel文件中的特定单元格移动到python中具有openpyxl的新列-腾讯云开发者社区-腾讯云

2 周前

叛逆的警车 · 运用pandas和openpyxl删除空行以及合并单元格_python openxl 如何删除部分合并的行-CSDN博客

2 周前

坚强的南瓜 · openpyxl删除单元格内容 - CSDN文库

2 周前

性感的小蝌蚪 · easyPOI 导出excel模板时,如何删除行在将后面的行数据向上移动,且保持不报这个错误:Cannot add merged region A9:I9 to sheet because it ov

2 周前

还单身的消炎药 · Excel Open Xml中CellStyleXfs，cellStyle，cellXfs之间关系的总结_xfid-CSDN博客

2 周前

温文尔雅的大象 · 精选案例 | 矿坑公园—大地伤痕的新生及扩展_项目

2 月前

性感的小熊猫 · Pricing - Active Directory External Identities | Microsoft Azure

8 月前

个性的领带 · 家长速看！佛山今年普通高中自主招生安排出炉，报名时间→

12 月前

高大的香蕉 · 2023年2月份奥迪Q4 e-tron销量553台, 环比增长8.86%_销量数据 - 车主之家

1 年前

高大的茄子 · 卡塔尔海军未来的主力舰——深度解析：“多哈”级轻型护卫舰|舰艇|导弹|防空导弹|反舰导弹_网易订阅

1 年前

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

回答 1

Stack Overflow用户