[Python] The value written in Openpyxl is displayed in exponential notation (E).
Problems that occurred
The value written in the Python library openpyxl is displayed in exponential notation using E.
sample_code.py
import openpyxl
wb = openpyxl.open('./Book1.xlsx')
sh = wb['Sheet1']
sh.cell(row=1, column=1).value = 308985489249593041
wb.save('./result.xlsx')
result
Even if the display with the exponential function removed, some of the endings are replaced with 0s, which is a problem
Cause
Excel spec issue instead of openpyxl
If you enter a number greater than 15 digits in an Excel cell, all numbers greater than 15 digits will be changed to 0.
[Entering a long number in an Excel cell changes the last number to 0](https://docs.microsoft.com/en-us/office/troubleshoot/excel/last-digits-changed-to- from zeros)
For numerical calculations, it does not seem to be a problem if the accuracy is slightly reduced with this number of digits. It's a problem if you're writing something like an id, a credit card number, or even a single digit.
solution
Just write a number as a character string
sample_code.py
import openpyxl
wb = openpyxl.open('./Book1.xlsx')
sh = wb['Sheet1']
#I will make the numerical value to be written here str type
sh.cell(row=1, column=1).value = str(308985489249593041)
wb.save('./result.xlsx')
I got angry because this is a string or a number that starts with an apostrophe, but I could write it
By the way, the calculation in Excel can be done normally even if this error occurs.
How can I get the value written as a character string in openpyxl?
sample_code.py
import openpyxl
wb = openpyxl.open('./result.xlsx')
sh = wb['Sheet1']
A1_value = sh.cell(row=1, column=1).value
print(A1_value)
print(type(A1_value))
'''
> 308985489249593041
> <class 'str'>
It seems to be obtained as a character string I have to replace it with int again
Postscript: I will specify the format
The following articles were introduced in the comments. Specify the display format of numbers with openpyxl
That's why
I passed some formats to sh.cell (row =, colum =). number_format =.
The number to write is the same as above 308985489249593041
In the format given by column A, column B is the result
Oh, it's amazing. The format has changed properly.
But unfortunately the end can be changed to 0 ...
It's a B2 or character string format, but it seems impossible if it's an int type at the time of writing ...
Afterword
Is there a smarter workaround? You can release the upper limit with an Excel plug-in w If you know, please let me know
Recommended Posts