Applying formatting row by row in addition to column formatting with xlsxwriter

Question

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

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?

EDIT: 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?

Pulp

Answer 1

Modifying the XLSXWriter sample code at 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...

While writing data:

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

After writing data:

###############################################################################
#
# 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 offers two methods to achieve alternating row formats : Conditional Formatting OR Table Style "Banded Rows".

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

Raleigh

Good answer. The conditional format solution will work if you specify the type as formula (and omit value). Like this: {'type': 'formula', 'criteria': '=MOD(ROW(),2) = 0', 'format': format1}. And a similar format for odd numbers: =MOD(ROW(),2) = 1'. – Tannie

Also the "Table Style Banded Rows" option is supported in the XlsxWriter Table feature. – Tannie

Once again, jmcnamara has the best answers. Thank you sir! – Raleigh

Would you consider adding this as an alternative answer? Your solution makes better use of the XLSXwriter features. Mine is functional, but not elegant. – Raleigh

Your answer is fine and I'm always happy when someone else provides an XlsxWriter answer. It means the documentation is working. :-) What I will do is add this to the conditional format example in the repo. It is a use case that I hadn't thought of but could be useful. – Tannie