Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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.

like image 803
Renesis Avatar asked Sep 12 '12 11:09

Renesis


People also ask

How do I change the number format in Excel using Python?

Adjust Excel Number Format using Python Then for the exp/income ratio, we'll format it as a percentage. It's simple to change the format, just set the cell. number_format attribute to whatever format we need. That's it!

Can openpyxl write XLS files?

Introduction. openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

How do I get cell value using openpyxl?

Program to read cell value using openpyxl Library in Python Step1: Import the openpyxl library to the Python program. Step2: Load/Connect the Excel workbook to the program. Step3: Get the title of the default first worksheet of the Workbook. Step4: Create variables and initialize them with the cell names.


2 Answers

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' 
like image 53
qryckbosch Avatar answered Oct 06 '22 07:10

qryckbosch


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'  
like image 36
Renesis Avatar answered Oct 06 '22 07:10

Renesis