How do I add formats to existing format objects "on the fly" using xlsxwriter

Is it possible to modify or add to an existing format "on the fly" in xlsxwriter?

Currently no.

From the docs:

Each unique cell format in an XlsxWriter spreadsheet must have a corresponding Format object. It isn’t possible to use a Format with a write() method and then redefine it for use at a later stage. This is because a Format is applied to a cell not in its current state but in its final state. Consider the following example:

format = workbook.add_format({'bold': True, 'font_color': 'red'})
worksheet.write('A1', 'Cell A1', format)

# Later...
format.set_font_color('green')
worksheet.write('B1', 'Cell B1', format)

Cell A1 is assigned a format which initially has the font set to the colour red. However, the colour is subsequently set to green. When Excel displays Cell A1 it will display the final state of the Format which in this case will be the colour green.

The most practical workaround when building many unique formats is to store formats in a dict indexed by their properties.


You can do something like this:

def copy_format(book, fmt):
    properties = [f[4:] for f in dir(fmt) if f[0:4] == 'set_']
    dft_fmt = book.add_format()
    return book.add_format({k : v for k, v in fmt.__dict__.items() if k in properties and dft_fmt.__dict__[k] != v})

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

initial_format = workbook.add_format({
    'font_size': 13,
    'bold': 1,
    'border': 1,
    'align': 'center',
})

new_format = copy_format(workbook, initial_format)
new_format.set_font_size(16)
new_format.set_font_color('white')