openpyxl条件格式规则在Excel文件中，但不在格式中

Question

我的目标是创建一个Excel文件，并使用openpyxl使用条件格式根据单元格的值更改单元格的背景色。你知道吗

当我打开用Excel创建的文件时，我可以看到规则在那里，但是规则不包括要应用的格式（背景色设置为“无”）。因此，单元格没有背景色，尽管有关公式的单元格边框不可见，就像背景为白色时一样。 我不知道我是否犯了错误，或者openpyxl是否有问题。你知道吗

这是一个MWE：

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

ws['B2'] = -2
ws['B3'] = -1
ws['B4'] =  0
ws['C2'] = -1
ws['C3'] =  0
ws['C4'] =  1

fill = PatternFill(start_color='538DD5', fill_type='solid')
ws.conditional_formatting.add('B2:C4', CellIsRule(operator='lessThan', formula=[0], fill=fill))

wb.save('mwe.xlsx')
wb.close()

Answer 1

按照@GiovaniSalazar的答案，我做了更多的测试。 用于颜色的参数（start\u color、end\u color、fgColor、bgColor）与条件格式和简单格式（openpyxl中的bug？）的行为不同。你知道吗

下面是两者的比较。对这两种格式都有效的唯一方法是开始颜色+结束颜色。你知道吗

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

ws['C2'] = -4
ws['C3'] = -3
ws['C4'] = -2
ws['C5'] = -1
ws['D2'] =  4
ws['D3'] =  3
ws['D4'] =  2
ws['D5'] =  1


ws['C1'] = 'Cond. formatting'
ws['F1'] = 'Formatting'

ws['A2'] = 'start+end'
fill = PatternFill(start_color='538DD5', end_color='538DD5', fill_type='solid')
# OK
ws.conditional_formatting.add('C2:D2', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# OK
ws['F2'].fill = fill


ws['A3'] = 'start'
fill = PatternFill(start_color='538DD5', fill_type='solid')
# Problem (white background)
ws.conditional_formatting.add('C3:D3', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# OK
ws['F3'].fill = fill


ws['A4'] = 'fgColor'
fill = PatternFill(fgColor='538DD5', fill_type='solid')
# Problem (white background)
ws.conditional_formatting.add('C4:D4', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# OK
ws['F4'].fill = fill


ws['A5'] = 'bgColor'
fill = PatternFill(bgColor='538DD5', fill_type='solid')
# OK
ws.conditional_formatting.add('C5:D5', CellIsRule(operator='lessThan', formula=[0], fill=fill))
# Problem (black background)
ws['F5'].fill = fill


wb.save('mwe.xlsx')
wb.close()

输出Excel文件：

Output Excel file

Answer 2

您需要添加如下参数end\ U color：

fill = PatternFill(start_color='538DD5',end_color='538DD5',fill_type='solid')

检查此链接：https://openpyxl.readthedocs.io/en/stable/formatting.html

from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()
ws = wb.active

ws['B2'] = -2
ws['B3'] = -1
ws['B4'] =  0
ws['C2'] = -1
ws['C3'] =  0
ws['C4'] =  1

fill = PatternFill(start_color='538DD5',end_color='538DD5',fill_type='solid')
#print(fill)
ws.conditional_formatting.add('B2:C4', CellIsRule(operator='lessThan', formula=[0], fill=fill))
wb.save('mwe.xlsx')
wb.close()

结果：