Applying row formatting in addition to xlsxwriter column formatting

Question

I format all 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()

I would like to add zebra striping to my rows, but I cannot find a way to format line by line without overwriting column formatting. As a test, I created a bold format and applied it to row 3, but it replaced the column formatting. Can I add row by row formatting without overwriting my column with column formatting? Or maybe there is an idiom for a zebra stripe that avoids line-by-line formatting?

EDIT: I think my situation is getting complicated because the data is already written to excel using the pandas function. In any case, will this simplify the formatting of rows and columns?

Answer 1

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

, , . ...

:

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

Per https://support.office.com/en-in/article/Apply-shading-to-alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880, Microsoft : "Banded Rows".

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