Last 4 digits are getting converted to 0 while writing to excel using Panda and ExcelWriter

Question

I am using xlsxwriter with Panda to write an excel. Doing so 19-character long value is getting changed from 9223781998151429879 to 9223781998151420000. Excel handling of long numbers might be the reason.

I tried to remove the formatting using the below code. I tried various combinations of formats. But nothing worked.

writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Result 1')
workbook = writer.book
worksheet = writer.sheets['Result 1']
format1 = workbook.add_format({'num_format': '#,##0.00'})
worksheet.set_column('M:M', 20, format1)
writer.close()

Answer 1

One possible reason can be that Excel supports long numbers up to 15 digits precision

A work around is to write numbers as text to excel:

df = pd.DataFrame(data=[[9223781998151429879]], columns=['num'])

with pd.ExcelWriter('pandas_column_formats.xlsx', engine='xlsxwriter') as writer:
    df['num'] = df['num'].apply(str)
    df.to_excel(writer, sheet_name='Result 1')

This data format works as a number with numeric calculations:

Update:

Also refer answers on this post