在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

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

回答 1

Stack Overflow用户