条件格式化与xlsx编写器生成含有“文本:含有”的损坏文件

Question

所以我有一些代码，可以根据单元格值对Excel文件应用条件格式。我想根据“文本”类型使用条件“包含”添加一些到相同的范围。这些列都是日期字符串，我想对包含“2017”的日期应用一种格式。在

这是整个块，如果我注释掉文本的条件格式，它可以很好地处理单元格的条件格式：

mtbook = mytrials_writer.book
header_format = mtbook.add_format({'bg_color': '#7e98f7','bold': True})
notFoundFormat = sitebook.add_format({'bg_color':'red'})
notExpFormat = sitebook.add_format({'bg_color':'silver'})
foundFormat = sitebook.add_format({'bg_color':'lime'})
for worksheet in mtbook.worksheets():
    # for every column
    for i in range(len(subreportCols)):
        # write the value of the first cell in the column to the first cell of that column
        worksheet.write(0, i, subreportCols[i], header_format)
        worksheet.set_column(0, 50, 17)
        worksheet.set_row(0, 25, None)
        worksheet.conditional_format('A2:Z100', {'type':'text', 'criteria': 'containing', 'value':'2017','format':notFoundFormat})
        #worksheet.conditional_format('A2:Z100', {'type':'text', 'criteria': 'containing', 'value':'"2016-"','format':foundFormat})
        #worksheet.conditional_format('A2:Z100', {'type':'text', 'criteria': 'containing', 'value':'"2015-"','format':foundFormat})
        worksheet.conditional_format('A2:Z100', {'type':'cell', 'criteria': '==', 'value':'"Miss/Inc"','format':notFoundFormat})
        worksheet.conditional_format('A2:Z100', {'type':'cell', 'criteria': '==', 'value':'"NotExp."','format':notExpFormat})

如果我启用下面的行，代码将运行，但Excel文件将打开，询问是否要修复，因为它已损坏；如果我说是，则文档中的任何地方都没有格式。在

^{pr2}$

错误信息是：“我们发现文件中的某些内容有问题。你想尽力恢复吗？如果您信任此工作簿的来源，请单击“是”

这是返回的错误日志：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error255040_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\mgancsos\Documents\Data Sources\Python\Testing\TQ_MyTrials_upload.xlsx'</summary><repairedRecords><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet1.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet2.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet3.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet4.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet5.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet6.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet7.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet8.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet9.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet10.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet11.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet12.xml part</repairedRecord><repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet13.xml part</repairedRecord><repairedRecord>Repaired Records: C</repairedRecord><repairedRecord>Repaired Records: C</repairedRecord><repairedRecord>Repaired Records: C</repairedRecord><repairedRecord xml:space="preserve">Repaired Records: </repairedRecord></repairedRecords></recoveryLog>

谢谢你！在

Answer 1

我认为问题是你想要匹配的字符串的双引号。试试这个：

worksheet.conditional_format('A2:Z100', 
    {'type': 'text', 
     'criteria': 'containing', 
     'value':'2016-',
     'format': foundFormat})