除了使用 xlsxwriter 进行列格式化之外，还逐行应用格式化 [英] Applying formatting row by row in addition to column formatting with xlsxwriter

问题描述

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

I am formatting all of my columns in an excel file using the xlsxwriter module:

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 行，但它覆盖了我的列格式是否可以逐行添加格式而不用逐列格式覆盖我的列?或者也许有一种斑马条纹的习惯用法可以避免逐行格式化?

I would like to add zebra striping to my rows, but I can't seem to find a way to format row by row, without overwriting my column formatting. As a test, I created a bold format and applied it to row 3, but it overwrote my column formatting Is it possible to add row by row formatting without overwriting my column by column formatting? Or maybe there is an idiom for zebra striping that would avoid the row by row formatting?

我认为我的情况变得更加困难，因为数据已经通过 Pandas 函数写入 excel.无论如何，是否可以使行和列的格式更容易?

I think my situation becomes more difficult because the data is already written to excel via a pandas function. Is there anyway around this that would make formatting both rows and columns easier?

推荐答案

在 URL http://xlsxwriter.readthedocs.org/en/latest/example_conditional_format.html

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

I suggest formatting as you iterate over the data, using the row value in a test. For example...

写入数据时:

###############################################################################
#
# 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 ，Microsoft 提供了两种方法来实现交替行格式:条件格式或表格样式带状行".

Per https://support.office.com/en-in/article/Apply-shading-to-alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880 , Microsoft offers two methods to achieve alternating row formats : Conditional Formatting OR Table Style "Banded Rows".

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

Conditional Formatting with formula =MOD(ROW(),2)=0 completes, but Excel 2013 cannot interpret it.

# 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})

这篇关于除了使用 xlsxwriter 进行列格式化之外，还逐行应用格式化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！