CODEWITHSUNDEEP
pythonxlsxwriter
VincFort
VincFort

Reputation: 1180

Xlsxwriter change format colour based on if statement

I have a python script that creates a report.

I would like to apply colour a to row a and then colour b to row b, ... I could create a variable formatA = workbook.add_format({'bg_color': 'red'}) and formatB = workbook.add_format({'bg_color': 'blue'}) and so on.

However, I think it would be easier to do format = workbook.add_format({'bg_color': 'red'}) and then when I loop through the rows I want to use, do format.set_bg_color('blue') and apply it.

However, it does not change the background colour and only applies blue to every row.

Upvotes: 2

Views: 3658

Answers (1)

patrickjlong1
patrickjlong1

Reputation: 3823

The Modifying Formats section of the xlsxwriter documentation page on formats explains this problem very well (link here). Essentially, it explains that it is not possible to use a Format and then redefine it to use at a later as a Format is applied to cells in their final state.

Perhaps consider, as an alternative, creating a function that creates a format. You can then call this function as needed when you want to change cell color or some other format you add to that function.

I've provided an example below of how you might do this.

import pandas as pd

df = pd.DataFrame({'col_a': ['a','b','c'],
                  'col_b': [1,2,3]})



def format_function(bg_color = 'red'):
    global format
    format=workbook.add_format({
    'bg_color' : bg_color
    })                

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook=writer.book
worksheet = writer.sheets['Sheet1']



# Setting the format but not setting the row height.
format_function(bg_color = 'red')
worksheet.set_row(1, None, format)
format_function(bg_color = 'blue')
worksheet.set_row(2, None, format)
format_function(bg_color = 'pink')
worksheet.set_row(3, None, format)


writer.save()

Expected Output:

Expected Output

Upvotes: 2

Related Questions