CODEWITHSUNDEEP
excelpython-2.7xlsxwriter
marvincod2
marvincod2

Reputation: 11

How to get correct number formatting of formula result in excel

Excel has property to set number formatting automatically for some numbers or formula results. For example, if I enter formula '=12%+12%' it returns 24.00% as a result. But when I use xlsxwriter module to write formula it gives me not formatted value 0.24

I use the next:

workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write_formula('A1', '=12%+12%')
workbook.close()

When I open created file I see 0.24 result in the cell. And to apply autoformatting I need to select cell and press Enter.

So my question is how to write formula and apply excel auto number formatting if it is possible.

Upvotes: 1

Views: 815

Answers (1)

jmcnamara
jmcnamara

Reputation: 41554

Excel sets a number format automatically for percent results but it is also possible to do it manually, in Excel, using the 0% number format.

You can do the same in XlsxWriter using a cell format:

import xlsxwriter

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

percent_format = workbook.add_format({'num_format': '0%'})

worksheet.write_formula('A1', '=12%+12%', percent_format)

workbook.close()

Output:

enter image description here

Upvotes: 1

Related Questions