Displayed value: value formatted according to number_format

Issue #1097 invalid
Manel Clos
created an issue


I'd like to know how to use openpyxl to get the displayed value of a cell. I'm using 2.5.6 to read a xlsx file that contains a cell with a date, using the data_only=True option I'm successfully getting:

value: 2018-01-12 00:00:00 (datetime object)
number format: yyyy"-"mm"-"dd

The displayed value in LibreOffice is '2018-01-12'. How to get it?

I'm willing to code if the issue can be fixed in openpyxl.

On the search previous to opening this issue, I found that the formatting functionality might not fit in the openpyxl library, in that case, is there any ongoing effort to have an external library that will do the job?

Comments (4)

  1. CharlieC

    Excel doesn't distinguish clearly between dates and datetimes and relies on formatting but the two are equivalent. You can easily convert the value to a date if you want.

  2. Manel Clos reporter

    Hi, I agree. I'm asking if this could be addressed in the openpyxl library, i.e. having the displayed value as a string, formatted using number_format. If I have to do this on my own code, do you know of any ongoing effort to provide a solution for number_format -> python format equivalent?

  3. CharlieC

    Well, you could take the number format and try an map it to something using strftime. OOXML uses its own number format specification using an EBNF but for most cases a lookup dictionary should suffice. data_only refers only to the returning the results of formulae as opposed to the formulae themselves. Anything else is outside the scope of the library.

  4. Log in to comment