Openpyxl Setting Number Format

Could please someone show an example of applying the number format to the cell. For example, I need scientific format, form would be like '2.45E+05' but I couldn't figure a way how

Solution 1:

This answer works with openpyxl 2.0. (The previously accepted answer does not.)

The number_format can be changed directly.

The given example becomes:

from openpyxl importWorkbookwb= Workbook()
ws = wb.create_sheet(title='testSheet')
_cell = ws.cell('A1')
_cell.number_format = '0.00E+00'

Solution 2:

Note: this answer worked with earlier versions of openpyxl, but does not work with openpyxl 2.0

This is how to do it:

_cell.style.number_format.format_code = '0.00E+00'

Solution 3:

To directly format cells using openpyxl version 3 (v3.0.9), the following code was required:

from openpyxl importWorkbookwb= Workbook()
ws = wb.create_sheet(title='testSheet')

_cell = ws['A1']
_cell.number_format = '0.00E+00'

The cell can be formatted directly as follows:

ws['A1'].number_format = '0.00E+00'

The documents show the various number formats available, and there are quite a few: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html

Solution 4:

For openpyxl version 2.6.2: note the float display depends on the magnitude when in a python shell, or idle, or ipython session,

>>>wb = load_workbook('tmp.xlsx')>>>ws = wb[wb.sheetnames[0]]>>>c21 = ws.cell(2,1)>>>c21.value
'43546'
>>>c21.value = int(c21.value)>>>c21.value
43546
>>>c21.value = 134352345235253235.235235>>>c21.number_format = '0.000E+00'>>>c21.value
1.3435234523525323e+17
>>>c21.value = 534164134.6643>>>c21.value
534164134.6643
>>>wb.save('tmp_a.xlsx')>>>wb.close()

But do not be dismayed, because the '0.000E+00' format will be displayed correctly when you re-open the spreadsheet with MS-Excel or LibreOffice-Calc or Gnumeric. Just remember to save the workbook.