xlsxwriter 条件格式问题

Question

我有如下所示的条件格式：

worksheet_budget.conditional_format('D2:D100', {'type': 'cell',

                                            'criteria': 'between',

                                            'minimum': 0,

                                            'maximum': 100,

                                            'format': caution})

worksheet_budget.conditional_format('D2:D100', {'type': 'cell',

                                           'criteria': '<',

                                           'value': 0,

                                           'format': over})

它的意图是对于每个小于 0 的值，将格式切换为大于和 0 - 100 之间的格式，以及警告格式。然而，最终发生的是，对于没有值的字段，它会将其视为 0 并使用警告格式。我只希望具有会计编号的字段具有两种格式之一。如果它是空的，它应该没有格式。

Answer 1

正如@martineau 指出的那样，并在文档中突出显示，将'type': 'cell'第二种格式更改为'type': 'blanks'应将over格式应用于所有没有值的单元格。您还需要删除criteria和value键：worksheet_budget.conditional_format('D2:D100', {'type': 'blanks',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'format': over})

Answer 2

几乎每次我在 XlsxWriter 中回答有关条件格式的问题时，我都会说同样的话：首先弄清楚如何在 Excel 中进行操作，然后将其应用到 XlsxWriter。将空白单元格视为零的条件格式问题似乎是 Excel 中的一个已知问题/功能。我遵循了这篇文章中有关该问题的方法之一的建议，并为空白单元格设置了额外的默认格式。我还需要设置stop_if_true属性。这是一个工作示例：import xlsxwriterworkbook = xlsxwriter.Workbook('conditional_format.xlsx')worksheet = workbook.add_worksheet()# Add a format. Light red fill with dark red text.format1 = workbook.add_format({'bg_color': '#FFC7CE',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'font_color': '#9C0006'})# Add a format. Green fill with dark green text.format2 = workbook.add_format({'bg_color': '#C6EFCE',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'font_color': '#006100'})# Add a default format.format3 = workbook.add_format()# Some sample data to run the conditional formatting against.data = [&nbsp; &nbsp; [34, -75, None, 75, 66, 84, 86],&nbsp; &nbsp; [6, 24, 1, 60, 3, 26, 59],&nbsp; &nbsp; [None, 79, 97, -13, 22, 5, 14],&nbsp; &nbsp; [-27, -71, None, 17, 18, 0, 47],&nbsp; &nbsp; [88, 25, -33, 23, 67, "", 36],&nbsp; &nbsp; ['', 100, 20, 88, 54, 54, 88],&nbsp; &nbsp; [6, 57, '', 28, 10, 41, 48],&nbsp; &nbsp; [52, 78, -1, 96, 26, 0, ""],&nbsp; &nbsp; [60, -54, 81, None, 81, 90, 55],&nbsp; &nbsp; [70, 5, 46, 14, 71, 41, 21],]for row, row_data in enumerate(data):&nbsp; &nbsp; worksheet.write_row(row + 2, 1, row_data)worksheet.conditional_format('B3:H12', {'type': 'blanks',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'stop_if_true': True,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'format': format3})worksheet.conditional_format('B3:H12', {'type': 'cell',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'criteria': 'between',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'minimum': 0,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'maximum': 100,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'format': format1})worksheet.conditional_format('B3:H12', {'type': 'cell',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'criteria': '<',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'value': 0,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'format': format2})workbook.close()输出：