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

Question

Is it possible to modify or add to an existing format "on the fly" in xlsxwriter? I'd like this functionality so I can maintain a few primary formats and add new criteria on a case by case basis.

For example in the code below I'd like to add an underline format to the dark_blue_header_format in cell A2 only. However, this produces some unexpected results, which are shown in the picture below. I expected A1 and A3 to be blue with 24-size white text: and I expected A2 to be blue with 24-size white text and underlined.

import xlsxwriter

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

dark_blue_header_format = workbook.add_format({
    'bg_color': '#5081BB',
    'font_color': '#FFFFFF',
    'font_size': 24
})

worksheet.set_column('A:A', 30)

worksheet.write('A1', 'Company Name', dark_blue_header_format)
worksheet.write('A2', 'Underlined Company Name', dark_blue_header_format.set_underline())
worksheet.write('A3', 'Company Name', dark_blue_header_format)

workbook.close()

I've looked through the formatting docs and I haven't found anything that can add formats on the fly. If the set_whatever functionality behaves like this example then I don't understand what it's useful for.

If it isn't possible to add formatting to existing formats "on the fly" what is the best practice when building many unique formats?

Thanks!

Tanaka

Answer 1

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.

Joesphjoete

Great best-practice idea. And thanks for point me to the feature requests. – Tanaka

Can you give an example of the best practice? – Hevesy

Hi. I am stuck using an old version of pandas that does not support number formatting with its styling. I was going to just apply it after using to_excel but your documentation makes it seem like that is not possible. If I were to close the excel file and reopen, and then apply the formatting would that overwrite the current formats? – Wheelwright

Answer 2

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')

Anaesthetize

Too bad they make us use a hack like this. :P – Millisecond

Answer 3

Thank you all for addressing this issue. It is exactly what I encountered. Use case: to apply different formatting to different types of contents but on top of that, apply additional formatting ("highlights") to certain cells. For example, add right and/or bottom border to separate different parts of the table; highlight maximum values with additional formatting.

If a function which returns workbook format makes a copy, I afraid that too many format instances will be generated when filling in a big table. Therefore I implemented a solution which pre-generates formats with all combinations of highlights. The function returning format selects one of pre-generated objects. I am not sure if it is worth sharing, as I am new to Python.

workbook = xlsxwriter.Workbook("test.xlsx")
### example cell formats and highlights
frm_types={
    "norm":   {},
    "it":     {"italic":True},
    "bold":   {"bold":True},
    "boldit": {"bold":True,"italic":True}
            }
frm_highlights={
     "max":           {"bold":True,"font_color":"#006699"},
     "border_right":  {"right":1},
     "border_bottom": {"bottom":1},
     "align_right":   {"align":"right"}
         }
### Creating a table of workbook formats with all highlights combinations 
##  Given:
#    wb - workbook, 
#    frm - format properties as dict, 
#    hlist - list of highlights as dicts.
##  Recursively adds levels to multidim. list - [with no highlight, with highlight],
#     finally returns the last level containing a pair of workbook formats
def frm_hl_level(wb,frm,hlist):
    # create two dicts. of properties: without and with first highlight from hlist
    frm_nohl=copy(frm)
    frm_withhl=copy(frm)
    frm_withhl.update(hlist[0])
    # recursion step
    if len(hlist)==1:
        return [
            wb.add_format(frm_nohl),
            wb.add_format(frm_withhl)    
                ]
    else:
        return[
            frm_hl_level(wb,frm_nohl,hlist[1:]),
            frm_hl_level(wb,frm_withhl,hlist[1:])
                ]
## The formats dictionary: 
#   keys = format names, 
#   items = multidim. tables of corresponding formats with 
#     all highlights combinations
frm_data_dict={
        fnm:
            frm_hl_level( workbook, frm_types[fnm],
                             list(frm_highlights.values()) )
            for fnm in frm_types
        }
## Function which returns workbook format object
## Given 
#   l - list of strings: [1st = format name, rest = highlights names],
#   frms, hls, tbl - format dicts., highlights dicts., dict. of formats tables;
## Returns the corresponding workbook format from the dict. of tables
def frm_select(l,frms=frm_types,hls=frm_highlights,fdt=frm_data_dict):
    # list of highlights keys for identifying table indices
    hl_names=list(hls.keys())
    # list of indices for identifying table element
    ind_l=[0 for _ in hls] # init. - format with no highlights
    # add highlights to index
    for nm in l[1:]:
        ind_l[hl_names.index(nm)]=1
    # access the element of the table
    arr=fdt[l[0]]
    for ind in ind_l:
        arr=arr[ind]
    return arr

Distortion

Answer 4

In addition to the use of copy_format(), you can use function scope to clear it, since the format will be deleted after function returns.

def print_diff_colors(i):
    # generating random color below
    YellowFormat = wb.add_format({'bold':True, 'color':f"#{i}1{i}324"})
    sheet.write_rich_string(i,0 ,
                            'Some ',
                            YellowFormat, 'bold',
                            ' text'
                            )
    
for i in range(10):
    print_diff_colors(i)

This results in dynamically formatted cells:

Not sure if you still need it, but it's worth mentioning.

Rowel