How to apply format as 'Text' and 'Accounting' using xlsxwriter

Question

I have been using xlsxwriter for a while and find it to be really helpful. I have used it for multiple purposes like custom data validation, custom formats etc However there are two things i am not able to perform.

1. set format of a cell to 'text' and not 'general'. i have tried the apis write_string, write_blank but they always apply the format general

2. set format as accounting. i have tried add_format({'num_format': '#,###'}. but this simply sets the format as 'custom' instead of accounting

Please help.

Gamble

Have you read xlsxwriter.readthedocs.org/en/latest/format.html#set_num_format? – Dither

Answer 1

1. To set a cell format to text you need to apply a text format to the cell (just like in Excel). To do this you set the num_format property of the format to '@'.
2. If you set a string format like #,### then this will generally show up in Excel as a custom format even if it equates to one of the built-in formatting categories like accountancy. This is the same behaviour as Excel.

In order to get one of the built-in formats you need to use a format index instead of a string. The table in the num_format section of the docs shows the available indices and their equivalent string formats. For accountancy you need to use one of the accountancy-like format indices such as 44 (_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)). The example below shows both of these:

import xlsxwriter

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

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

format1 = workbook.add_format({'num_format': '@'})
format2 = workbook.add_format({'num_format': 44})

worksheet.write(0, 0, 1234)
worksheet.write(1, 0, 1234, format1)
worksheet.write(2, 0, 1234, format2)

workbook.close()

Output:

Update: See also this newer, more specific, section of the XlsxWriter docs on Number Format Categories.

Carthusian

Thank you so much... straight frm the author too.. :) you are a life savior – Gamble

Great answer. The reference to the oficial documentation is correct but the info of Index 44 for Accounting makes the diference here. Thanks – Gotthelf

Can one format the column after the fact? Say, ` currency = workbook.add_format({'num_format': 44}) worksheet.set_column(df.columns.get_loc('total_cost'), None, currency)` – Teryn