XlsxWriter: set_column() with one format for multiple non-continuous columns

Question

I want to write my Pandas dataframe to Excel and apply a format to multiple individual columns (e.g., A and C but not B) using a one-liner as such:

writer = pd.ExcelWriter(filepath, engine='xlsxwriter')
my_format = writer.book.add_format({'num_format': '#'})

writer.sheets['Sheet1'].set_column('A:A,C:C', 15, my_format)

This results in the following error:

File ".../python2.7/site-packages/xlsxwriter/worksheet.py", line 114, in column_wrapper

cell_1, cell_2 = [col + '1' for col in args[0].split(':')] ValueError: too many values to unpack

It doesn't accept the syntax 'A:A,C:C'. Is it even possible to apply the same formatting without calling set_column() for each column?

Joejoeann

Answer 1

If the column ranges are non-contiguous you will have to call set_column() for each range:

writer.sheets['Sheet1'].set_column('A:A', 15, my_format)
writer.sheets['Sheet1'].set_column('C:C', 15, my_format)

Note, to do this programmatically you can also use a numeric range:

for col in (0, 2):
    writer.sheets['Sheet1'].set_column(col, col, 15, my_format)

Gogh

Answer 2

Or you could reference columns like this:

for col in ('X', 'Z'):
        writer.sheets['Sheet1'].set_column(col+':'+col, None, my_format)

Harryharsh

Answer 3

For me

for col in (0, 2):
    writer.sheets['Sheet1'].set_column(col, col, 15, my_format)

did not work.

I had to go with

for col in range(0, 2):
    writer.sheets['Sheet1'].set_column(col, col, 15, my_format)

Christlike