Openpyxl setting number format

Question

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 to do that in openpyxl.

I tried in several ways but they are all reporting errors when saving the workbook.

for example:

    import openpyxl as oxl

    wb = oxl.Workbook()
    ws = wb.create_sheet(title='testSheet')
    _cell = ws.cell('A1')
    _cell.style.number_format = '0.00E+00'

or this (here I'm trying to use some of the predefined number formats, I have also seen there is engineering format in builtins but don't know how to access it:

    nf = oxl.style.NumberFormat.FORMAT_NUMBER_00
    _cell.style.number_format = nf

In both cases I get the same error:

C:\Python27\openpyxl\cell.pyc in is_date(self)
    408         """
    409         return (self.has_style
--> 410                 and self.style.number_format.is_date_format()
    411                 and isinstance(self._value, NUMERIC_TYPES))

AttributeError: 'str' object has no attribute 'is_date_format'

I have seen this question: Setting styles in Openpyxl but it doesn't help because I don't have to change any other formatting settings.

score 58 · Accepted Answer · edited Oct 03 '18 at 09:05

58

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 import Workbook

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

edited Oct 03 '18 at 09:05

Jean-François Corbett

37,420
30
139
188

answered Oct 13 '14 at 07:35

qryckbosch

847
1
9
10

4

Actually, the following worked for me: `_cell.number_format = '0'` `_cell.value = int(x)` – Jason Aug 09 '17 at 09:31
1

Do you have any links to docs with details on this? – Samuel O.D. Oct 05 '21 at 08:02

score 9 · Answer 2 · edited Oct 03 '18 at 09:07

9

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'

edited Oct 03 '18 at 09:07

Jean-François Corbett

37,420
30
139
188

answered Sep 13 '12 at 07:37

Renesis

881
2
9
16

Brad Allen · Answer 3 · 2021-10-09T02:52:27.070

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

from openpyxl import Workbook

wb = 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

Bijou Smith · Answer 4 · 2019-04-17T07:11:02.603

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.

Openpyxl setting number format

4 Answers4

Linked