XlsxWriter error for percent format

Question

I'm using Pandas and exporting data to excel using XlsxWriter. One of the data columns has floats and needs to be formatted as percent, so this is how I do it:

percent_fmt = workbook.add_format({'num_format': '0.00%'})
worksheet.set_column('E:E', percent_fmt)

After that the following error appears:

File "C:\Program Files\Anaconda\lib\site-packages\xlsxwriter\worksheet.py", line 4688, in _write_col_info / float(max_digit_width) * 256.0) / 256.0

TypeError: unsupported operand type(s) for *: 'Format' and 'int'

What am I doing wrong here?

Horsley

Answer 1

You need to specify a width before the format or None if you don't want to adjust the width.

worksheet.set_column('E:E', None, percent_fmt)

Bobbette

I want to use this, but I don't have the workbook object that is necessary to create the percent_fmt setting here. Is it possible to create percent_fmt some other way? I looked on your docs and importing xlsxwriter and pulling the workbook object from it may not work for my application. – Parthenogenesis

You will need a reference to the parent workbook in order to create a new format. And it isn't possible to get a workbook reference from a worksheet. You will either need to pass in the format you want to use or the workbook object so you can generate a format. The first is the better option. – Bobbette

Thanks. I've got the entire thing written around df.to_excel() so it's hard to get the workbook object.. unless you have an easy way to do that in such cases? – Parthenogenesis

See the following for how to get the XlsxWriter workbook object: xlsxwriter.readthedocs.io/working_with_pandas.html – Bobbette

AH! I can't believe how many times I looked for that and never found this page. Thank you for a great writer! I use it extensively :) – Parthenogenesis