CODEWITHSUNDEEP
pythonformattingxlsxxlsxwriter
om_prakash
om_prakash

Reputation: 70

set different bg_color for different columns in xlsxwriter python

I want to set different background fill colors for different columns, but I'm not getting as expected.

import xlsxwriter 

workbook = xlsxwriter.Workbook('Example5.xlsx') 
worksheet = workbook.add_worksheet() 
bg_format = workbook.add_format()
colors = ['green', 'cyan', 'magenta']
content = ["A", "B", "C", "D"]
for i in range(5):
    bg_format.set_bg_color(colors[i % 3])
    print(colors[i % 3])
    worksheet.write_column(1, i, content, cell_format=bg_format)
workbook.close()

what i am getting is:

the output i am getting

what i expect to get:

the output i am expecting

Upvotes: 1

Views: 3543

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

A Format in XlsxWriter is "applied to a cell not in its current state but in its final state" (see this section of the docs). So in your example only one unique format is created and that has the last color that was applied.

You can fix this by creating a new format in the loop:

for i in range(5):
    bg_format = workbook.add_format()
    bg_format.set_bg_color(colors[i % 3])
    print(colors[i % 3])
    worksheet.write_column(1, i, content, cell_format=bg_format)

However, that can be inefficient for a large number of iterations. A more efficient way would be to create a unique format for each of the color types you need and reuse them. Like this:

import xlsxwriter 

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

green_format   = workbook.add_format({'bg_color': 'green'})
cyan_format    = workbook.add_format({'bg_color': 'cyan'})
magenta_format = workbook.add_format({'bg_color': 'magenta'})

bg_formats = [green_format, cyan_format, magenta_format]
colors     = ['green', 'cyan', 'magenta']
content    = ["A", "B", "C", "D"]

for i in range(5):
    bg_format = bg_formats[i % 3]
    print(colors[i % 3])
    worksheet.write_column(1, i, content, cell_format=bg_format)

workbook.close()

Output:

enter image description here

Upvotes: 1

Related Questions