How can I stop Excel from displaying numbers in sc

Question

I'm exporting data to XLSX using pandas, which in turn is using XlsxWriter. I've experimented with the formatting options I've found (e.g. float_format, despite the fact that it seemed like the wrong answer based on the source), but I can't find anything that prevents Excel from interpreting numbers in scientific notation.

For example, if I write 0.000000000001, the underlying cell value in Excel is 0.000000000001, but the display value is 1E-12. I know that I can write the number as a string, but I'd like the value to be numeric in Excel.

Answer 1

Just format the number when you specify in the write() method. Eg:

import xlsxwriter

wb = xlsxwriter.Workbook('myfile.xlsx')
ws = wb.add_worksheet('testme')
ws.write('A1', '%s' %123232323232323232435454546565)
wb.close()

Answer 2

You can set the length of number and then use code like the following:

import xlsxwriter

wb = xlsxwriter.Workbook('Test.xlsx')
ws = wb.add_worksheet('Num')

cell_format = wb.add_format({'num_format': '0.00000000000000'})
ws.write('A1', 0.000000000001, cell_format)

wb.close()

Answer 3

You cannot stop Excel from displaying an unformatted number in whatever format it thinks is suitable. The only way to guarantee the format is to specify a numerical format for the data.

Unfortunately there is currently no way to do this in XlsxWriter and or, as a result, in Pandas.

A feature such as this is on the TODO list however it probably won't be available for a while.

As a workaround I recommend getting a reference to the underlying workbook and worksheet and overwriting any cells that you wish to be formatted with the same data from the Pandas dataframe and a XlsxWriter format. I'll update later with an example of how to do that.

Disclaimer: I wrote XlsxWriter and the Pandas/XlsxWriter integration code. The issue here is really to do with XlsxWriter limitations and not Pandas.