除了使用xlsxwri进行列格式化之外，还应用逐行格式化

Question

我正在使用xlsxwriter模块格式化excel文件中的所有列：

def to_excel(video_report, feed):
    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter('daily_report.xlsx', engine='xlsxwriter')

    # Convert the dataframe to an XlsxWriter Excel object.
    video_report.to_excel(writer, sheet_name='Video Overview', na_rep="-")

    # Get the xlsxwriter objects from the dataframe writer object.
    workbook = writer.book
    worksheet = writer.sheets['Video Overview']

    # Add some cell formats.
    integer = workbook.add_format({'num_format': '0', 'align': 'center'})
    decimal = workbook.add_format({'num_format': '0.00', 'align': 'center'})
    percentage = workbook.add_format({'num_format': '0.0%', 'align': 'center'})

    zebra = workbook.add_format({'bold': True})

    worksheet.set_column('B:B', 13, integer)
    worksheet.set_column('C:C', 17, percentage)
    worksheet.set_column('D:D', 19, percentage)
    worksheet.set_column('E:E', 15, integer)
    worksheet.set_column('F:F', 15, percentage)
    worksheet.set_column('G:G', 15, decimal)
    worksheet.set_column('H:H', 13, integer)
    worksheet.set_column('I:I', 13, integer)
    worksheet.set_column('J:J', 13, integer)
    worksheet.set_column('K:K', 13, integer)
    worksheet.set_column('L:L', 13, integer)

    worksheet.set_row(3, 20, zebra)

    feed.to_excel(writer, sheet_name='Feed Position', na_rep="-")

    workbook1 = writer.book
    worksheet1 = writer.sheets['Feed Position']

    integer = workbook1.add_format({'num_format': '0', 'align': 'center'})

    worksheet1.set_column('B:HU', 4, integer)

    writer.save()

我想在我的行中添加斑马线，但我似乎找不到一种方式来逐行格式化，而不覆盖我的列格式。作为测试，我创建了一个粗体格式并将其应用于第3行，但它覆盖了我的列格式是否可以在不覆盖我的列格式的情况下逐行添加格式？或者有一个斑马线的习惯用法可以避免逐行格式化？

编辑：我认为我的情况变得更加困难，因为数据已经通过pandas函数写入excel。是否还有其他方法可以使行和列的格式设置更容易？

Answer 1

修改URLhttp://xlsxwriter.readthedocs.org/en/latest/example_conditional_format.html处的XLSXWriter示例代码

我建议在迭代数据时使用测试中的行值进行格式化。例如。。。

写入数据时：

###############################################################################
#
# Example 9.
#
caption = ('Rows with odd numbers are in light red. '
           'Rows with even numbers are in light green.')

# Write the data.
worksheet9.write('A1', caption)

for row, row_data in enumerate(data):
    if row%2 == 0:
        worksheet9.write_row(row + 2, 1, row_data, format1)
    else:
        worksheet9.write_row(row + 2, 1, row_data, format2)

写入数据后：

###############################################################################
#
# Example 10.
#
#
caption = ('Rows with odd numbers are in light red. '
           'Rows with even numbers are in light green.')
#
# Write the data.
worksheet10.write('A1', caption)
##
for row, row_data in enumerate(data):
    worksheet10.write_row(row + 2, 1, row_data)
##
# Write a conditional format over a range.
for row, row_data in enumerate(data):
    if row%2 == 0:
        worksheet10.set_row(row + 2, None, format1)
    else:
        worksheet10.set_row(row + 2, None, format2)

根据https://support.office.com/en-in/article/Apply-shading-to-alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880，微软提供了两种实现交替行格式的方法：条件格式或表样式的“带状行”。

使用公式=MOD(ROW(),2)=0的条件格式已完成，但Excel 2013无法解释它。

# Write a conditional format over a range DOES NOT WORK
worksheet1.conditional_format('A1:K12', {'type': 'cell',
                                         'criteria': '=MOD(ROW(),2)',
                                         'value': 0,
                                         'format': format1})

# Write another conditional format over the same range also DOES NOT WORK
worksheet1.conditional_format('A1:K12', {'type': 'cell',
                                         'criteria': '=MOD(ROW(),2)',
                                         'value': 1,
                                         'format': format2})