Python Xlsxwriter - Row format overwrites column format

Question

I have data in a pandas dataframe. I need different columns to have different alignment (some centered, some left-aligned). After I write in the column headers, I then set the formatting of each column using:

centered = workbook.add_format()
centered.set_align('center')

right = workbook.add_format()
right.set_align('right')

left = workbook.add_format()
left.set_align('left')

cols = len(df.columns))

worksheet.set_column(1, 1, cell_format = left)
worksheet.set_column(2, 2, cell_format = centered)
worksheet.set_column(3, 3, cell_format = right)

Then I loop through the rows in my df and write each row in using:

for index, row in df.iterrows():
    worksheet.write_row(index, 1, list(row))

This gets me this result:

Now I want to highlight each row of data where the "Right" column has a value of 1.

To do this, I create a new format object and I modify the writing of data to:

highlight_format = workbook.add_format()
highlight_format.set_fg_color('#f0e5ec')

for index, row in df.iterrows():
    if row['Right'] == 1:
        worksheet.write_row(index, 1, list(row), cell_format = highlight_format)
    else:
        worksheet.write_row(index, 1, list(row))

This is the result:

It is as if the new format overwrites the old one. How can I get it so that the old format stays, and just the new formatting (just the highlight) is added?

This is the desired result:

Answer 1

If you superimpose a row and column format at runtime in Excel then it will create a third format that is a combination of the row and column formats and apply it to the cell in the intersection.

XlsxWriter doesn't automagically create and apply a new format like that so if you want cells formatted with the properties of two combined formats you will need to explicitly create and apply that format to the relevant cells. This will require a bit of logic and work but there is no simple workaround.

However, in this particular case you could apply a conditional format to highlight the rows based on the value in Column C (which seems to be what you are trying to do).

Update, here is an example with conditional formatting:

import random
import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

centered = workbook.add_format()
centered.set_align('center')

right = workbook.add_format()
right.set_align('right')

left = workbook.add_format()
left.set_align('left')

highlight_format = workbook.add_format({'bg_color': '#FFC7CE',
                                        'font_color': '#9C0006'})

worksheet.set_column(0, 0, cell_format = left)
worksheet.set_column(1, 1, cell_format = centered)
worksheet.set_column(2, 2, cell_format = right)

# Simulate the data.
for row_num in range(9):
    worksheet.write(row_num, 0, 'Data')
    worksheet.write(row_num, 1, 'Data')
    worksheet.write(row_num, 2, random.choice(('Y', 'N')))

# Add a conditional format.
worksheet.conditional_format(0, 0, 8, 2, {'type':     'formula',
                                          'criteria': '=$C1="Y"',
                                          'format':   highlight_format})

workbook.close()

Output: