Python XlsxWriter - Number Formats

In Excel, different formatting options of numeric data are provided in the Number tab of Format Cells menu.

To control the formatting of numbers with XlsxWriter, we can use the set_num_format() method or define num_format property of add_format() method.

f1 = wb.add_format()
f1.set_num_format(FormatCode)
#or
f1 = wb.add_format('num_format': FormatCode)

Excel has a number of predefined number formats. They can be found under the custom category of Number tab as shown in the above figure. For example, the format code for number with two decimal points and comma separator is #,##0.00.

Example

In the following example, a number 1234.52 is formatted with different format codes.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.set_column('B:B', 30)

num=1234.52

num_formats = (
   '0.00',
   '#,##0.00',
   '0.00E+00',
   '##0.0E+0',
   '₹#,##0.00',
)
ws.write('A1', 'Formatted Number')
ws.write('B1', 'Format')

row = 1
for fmt in num_formats:
   format = wb.add_format({'num_format': fmt})
   ws.write_number(row, 0, num, format)
   ws.write_string(row, 1, fmt)
   row += 1
wb.close()

Output

The formatted number along with the format code used is shown in the following figure −