通过公式标准的xlsxwriter条件格式 [英] xlsxwriter conditional formatting by formula criteria

问题描述

我想将格式应用于列中的单元格.行索引是5、7、8、9、10、11和13.

我正在使用的代码如下:

  worksheet.conditional_format("C4:C14"，{'type':'formula'，'criteria':'= ISNUMBER(MATCH(ROW()，{5,7,8,9,10,11,13}，0))= TRUE'，'format':format_white}) 

结果是没有生成Excel文件.

公式 = ISNUMBER(MATCH(ROW()，{5,7,8,9,10,11,13}，0))= TRUE 可以按我预期的方式工作直接在Excel中.我使用一个更简单的公式'criteria':'= MOD(ROW()，2)= 0'来运行上面的代码，它可以正常工作，因此我怀疑问题出在标准字段中.

有人可以帮助我吗?预先感谢

解决方案

要使公式在XlsxWriter生成的文件中起作用，必须在Excel中起作用.

在这种情况下，条件公式公式在Excel中无效:

  = ISNUMBER(MATCH(ROW()，{5,7,8,9,10,11,13}，0))= TRUE 

如果您在Excel中手动添加它，则会收到以下错误/警告:

您不得将引用运算符(例如并集，交集和范围)或其他数组常量用于条件格式设置条件."

I want to apply format to cells in a column. The row indexes are 5,7,8,9,10,11 and 13.

The code I am using is the following:

 worksheet.conditional_format("C4:C14", {'type'    :  'formula',
                                           'criteria': '=ISNUMBER(MATCH(ROW(),{5,7,8,9,10,11,13},0))=TRUE',
                                           'format'  :   format_white})

The result is no Excel file is generated.

The formula =ISNUMBER(MATCH(ROW(),{5,7,8,9,10,11,13},0))=TRUE works as I expected when I type it directly in Excel. I run the code above with a simpler formula 'criteria': '=MOD(ROW(),2)=0' and it works, so I suspect the problem is in the criteria field.

Can anyone help me? Thanks in advance

解决方案

In order for a formula to work in an XlsxWriter generated file it has to work in Excel.

In this case the conditional formula formula isn't valid in Excel:

=ISNUMBER(MATCH(ROW(),{5,7,8,9,10,11,13},0))=TRUE

If you add it manually in Excel you get the following error/warning:

"You may not use reference operators (such as unions, intersections, and ranges) or other array constants for Conditional Formatting criteria".

这篇关于通过公式标准的xlsxwriter条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！