Exporting amounts using space as '000 delimiter

user_7228014 0
Viewed 589

I would like all amounts exported to Excel to use space as '000 delimiter and ',' for decimal. E.g: "3 257 132,54" (common number format in Europe)

I tried to adapt the example provided on xlsxwriter.readthedocs.io :

format1 = workbook.add_format({'num_format': '#,##0.00'})

As follows

format1 = workbook.add_format({'num_format': '# ##0,00'})

I am using the code from the xlsxwriter doc. I just modified the '000 delimiter and the decimal point:

# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})

# Set the column width and format.
worksheet.set_column('B:B', 18, format1)

I obtain a very surprising result. The example provided above will appear,in Excel, as: 3257 132,54. Almost good, but the '000 separator is only used once for thousands, but not for millions or billions. (nb: the comma as decimal separator works fine)

Is there a trick I missed?

xlsxwriter pandas formatting
1 Answers

You just need to use whatever number format that you would use in Excel for this. Probably something like ### ### ###.00 (although it doesn't have a comma for a decimal):

import xlsxwriter

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

format1 = workbook.add_format({'num_format': '### ### ###.00'})

worksheet.set_column('B:B', 18, format1)

worksheet.write(0, 1, 123.123)
worksheet.write(1, 1, 1234.123)
worksheet.write(2, 1, 12345.123)
worksheet.write(3, 1, 123456.123)
worksheet.write(4, 1, 1234567.123)
worksheet.write(5, 1, 12345678.123)

workbook.close()

Output:

enter image description here

You can find the exact number format you need by setting it in Excel and then checking what it is in the custom section of the number format dialog.

user_102380
Related