CODEWITHSUNDEEP
pythonexcelxlsxwriter
S Andrew
S Andrew

Reputation: 7198

How to remove "The number in the cell is formatted as text" using xlsxwrite python

I am working on python script where I am saving some data in excel sheet. For this I am using xlsxwriter. I have below code:

out_xls_path = os.path.join(dir_path, 'moprime', 'output.xlsx')
workbook = xlsxwriter.Workbook(out_xls_path)
io_sheet = workbook.add_worksheet("Input-Output ()")

io_sheet.set_column(1, 3, 25)
style = workbook.add_format({'bold': True, 'font_name': 'Calibri', 'align': 'center', 'border': 1})
io_sheet.write('C1', "ch/sl spread", style)
io_sheet.write('D1', "{}%".format(chsl_spread), style)
io_sheet.write('C2', "ch/sl spread", style)
io_sheet.write('D2', 23, style)

Doing above, below is the excel sheet:

enter image description here

I want to understand in D1 how can I remove that small point where it says The number in the cell is formatted as text. Thanks

Upvotes: 1

Views: 1035

Answers (1)

jmcnamara
jmcnamara

Reputation: 41554

To create a percentage number in Excel you need to divide by 100 and apply a number format such as "0.00%". If you format it as a string you will get the warning that you see.

Here is a working example based on yours:


import xlsxwriter

out_xls_path = 'output.xlsx'
workbook = xlsxwriter.Workbook(out_xls_path)
io_sheet = workbook.add_worksheet("Input-Output ()")

io_sheet.set_column(1, 3, 25)

style = workbook.add_format({'bold': True,
                             'font_name': 'Calibri',
                             'align':
                             'center', 'border': 1})

percent = workbook.add_format({'bold': True,
                               'font_name': 'Calibri',
                               'align':
                               'center', 'border': 1,
                               'num_format': '0.00%'})

chsl_spread = 29.82

io_sheet.write('C1', "ch/sl spread", style)
io_sheet.write('D1', chsl_spread / 100.0, percent)
io_sheet.write('C2', "ch/sl spread", style)
io_sheet.write('D2', 23, style)

workbook.close()

Output:

enter image description here

Upvotes: 1

Related Questions