Alternating row color using xlsxwriter in Python 3

Question

Has anybody implemented alternating row color while generating excel using xlsxwriter in Python3?

data_format = workbook.add_format(
    {
        'bg_color': '#FFC7CE'
    })

worksheet.write(data_row, data_col + 1, row[1], data_format)

This sets the color for each column.

Communicative

Answer 1

There is nothing stopping you from setting the formats manually as follows. There are two approaches:

1. Add a format for each row as you go for the data using .add_format().

2. Add the data using an Excel table using .add_table(). The style for this allows for automatic banded rows. This also has the advantage that if the table is sorted, the banding is unaffected.

A context manager can be used to automatically close the workbook afterwards.

Approach 1:

Manually applying a cell format to each row:

import xlsxwriter

with xlsxwriter.Workbook('hello.xlsx') as workbook:
    worksheet = workbook.add_worksheet()

    data_format1 = workbook.add_format({'bg_color': '#FFC7CE'})
    data_format2 = workbook.add_format({'bg_color': '#00C7CE'})

    for row in range(0, 10, 2):
        worksheet.set_row(row, cell_format=data_format1)
        worksheet.set_row(row + 1, cell_format=data_format2)
        worksheet.write(row, 0, "Hello")
        worksheet.write(row + 1, 0, "world")

This would give you output looking as follows:

---

To apply this to a list of data, you could use the following approach. This also shows how it could be extended to use additional formats:

import xlsxwriter
from itertools import cycle

data = ["Row 1", "Row 2", "Row 3", "Row 4", "Row 5", "Row 6"]

with xlsxwriter.Workbook('hello.xlsx') as workbook:
    data_format1 = workbook.add_format({'bg_color': '#EEEEEE'})
    data_format2 = workbook.add_format({'bg_color': '#DDDDDD'})
    data_format3 = workbook.add_format({'bg_color': '#CCCCCC'})
    formats = cycle([data_format1, data_format2, data_format3])
    
    worksheet = workbook.add_worksheet()

    for row, value in enumerate(data):
        data_format = next(formats)

        worksheet.set_row(row, cell_format=data_format)
        worksheet.write(row, 0, value)

Approach 2:

Adding the data using .add_table() with a banded style:

import xlsxwriter

data = [["Row 1"], ["Row 2"], ["Row 3"], ["Row 4"], ["Row 5"], ["Row 6"]]

with xlsxwriter.Workbook('hello.xlsx') as workbook:
    worksheet = workbook.add_worksheet()
    worksheet.add_table('A1:A6', {'data' : data, 'header_row' : False})

Excel comes with a number of different pre-defined table styles, these can be selected by passing a style paramter as follows:

'style': 'Table Style Light 11'

For more information have a look at: Working with Worksheet Tables

Baksheesh

This is basically what I ended up doing independently; however, when I add an autofilter and sort a column, all the colors get messed up since they are static to the row. How do we fix that? – Thebault

@retsigam, if your data might be sorted, then a better approach is to make use of the .add_table() function. This can apply a banded style to the data which will remain after being sorted. I have update the answer to show this approach. – Baksheesh

Answer 2

I think this is cleaner - if just want fill-in the cells with two-colors alternation

import xlsxwriter

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

bg_format1 = workbook.add_format({'bg_color': '#78B0DE'}) # blue cell background color
bg_format2 = workbook.add_format({'bg_color': '#FFFFFF'}) # white cell background color

for i in range(10): # integer odd-even alternation 
    worksheet.set_row(i, cell_format=(bg_format1 if i%2==0 else bg_format2))
    # (or instead) if you want write and paint at once 
    # worksheet.write(i, 0, "sample cell text", (bg_format1 if i%2==0 else bg_format2))



workbook.close()

Stalk