Get displayed string for cell value with openpyxl

Question

I'm using openpyxl to deal with excel .xlsx files. Some of the columns in the files contain cells of different number types. I can get the number_type and value from the Cell object, but cannot find an easy way to get a string. I've take a few hours on the docs and sources of openpyxl but found nothing.

For example, I get this output from ipython:

In [6]: ws['D4'].value
Out[6]: 0.02

In [7]: ws['D4'].data_type
Out[7]: 'n'

In [8]: ws['D4'].number_format
Out[8]: '0.0%'

In [9]: ws['D2'].number_format
Out[9]: u'yyyy"\u5e74"m"\u6708"d"\u65e5";@'

In [10]: ws['D2'].value
Out[10]: 42370L

How can I get a string like '2%' and u'2016\u5e741\u67081\u65e5'? The cells may be of some custom cell formats so I prefer a general way.

Answer 1

The value of any cell is the Python equivalent of the Excel type. openpyxl is a library for the file format and not an application like Excel. Therefore, it only supports the ability to set and read number formats but not apply them. That said, you could easily write your own function to do what you want (create a printf type format) based on the specification.