问题描述
我正在使用 openpyxl 来操纵Microsoft Excel工作表.
I am using openpyxl to manipulate a Microsoft Excel Worksheet.
我想做的是添加一个条件格式设置规则，如果行号为偶数，则用给定的颜色填充行，否则为空.
What I want to do is to add a Conditional Formatting Rule that fills the rows with a given colour if the row number is even, leaves the row blank if not.
在Excel中，这可以通过选择所有工作表，使用文本=MOD(ROW();2)=0或=EVEN(ROW()) = ROW()创建新的格式设置规则来完成.
In Excel this can be done by selecting all the worksheet, creating a new formatting rule with the text =MOD(ROW();2)=0 or =EVEN(ROW()) = ROW().
我尝试通过以下几行代码(例如，考虑前10行)来实现此行为:
I tried to implement this behaviour with the following lines of code (considering for example the first 10 rows):
redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
ws2.conditional_formatting.add('A1:A10', FormulaRule(formula=['MOD(ROW();2) = 0'], stopIfTrue=False, fill=redFill))
我的程序正常运行，但是当我尝试打开输出Excel文件时，它告诉我该文件包含不可读的内容，并询问我是否要恢复工作表内容.通过单击是"，工作表是我期望的，但是没有格式.
My program runs correctly but when I try to open the output Excel file, it tells me that the file contains unreadable content and it asks me if I want to recover the worksheet content. By clicking yes, the worksheet is what I expect but there is no formatting.
在openpyxl中(可能是在整个工作表中)应用这种格式的正确方法是什么?
What is the correct way to apply such a formatting in openpyxl (possibly to the entire worksheet)?
推荐答案
不幸的是，条件格式中处理公式的方式特别不透明.最好的办法是创建具有相关条件格式的文件，并通过解压缩来检查相关文件.规则存储在相关的工作表文件中，格式存储在样式文件中.
Unfortunately, the way formulae are handled in conditional formatting is particularly opaque. The best thing to do is to create a file with the relevant conditional format and inspect the relevant file by unzipping it. The rules are stored in the relevant worksheet files and the formats in the styles file.
但是，我怀疑问题可能仅仅是因为您使用的是;"分隔函数中的参数:为此，您必须始终使用逗号.
However, I suspect that the problem may simply because you are using ";" to separate parameters in the function: you must always use commas for this.
我的一个项目的示例公式:
A sample formula from one of my projects:
green_text = Font(color="006100")
green_fill = PatternFill(bgColor="C6EFCE")
dxf2 = DifferentialStyle(font=green_text, fill=green_fill)
r3 = Rule(type="expression", dxf=dxf2)
r3.formula = ["AND(ISNUMBER(C2), C2>=400)"]
这篇关于如何在openpyxl中应用条件格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！