How to format a cell for date format in excel with openpyxl-CodePudding

I'm trying create a program that read a .log files, and extract yours information and write a .xlsx file, and i maked this, but when i write the dates informations, the excel interpret as text, and not as date, how can i solve it?

I tried make this:

page.cell(line, 2).number_format = '22'

after of i maked this:

time = contents[12:] # extracts the date in .log file

Because of this documentation: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html

But not solve to

CodePudding user response：

Excel dates are binary values, not text translated based on a format. Same for numbers. If you want to store dates or numbers in Excel, store the actual Python objects, not their string representation. number_formatspecifies how those binary values will be displayed to the end user

This is shown in the Using Number Formats section of the Simple Usage doc page:

>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'

In this code ws['A1'] = datetime.datetime(2010, 7, 21) stores a Python datetime object to cell A1. Then it specifies that this cell will be displayed using the yyyy-mm-dd h:mm:ss format.

A quirk/feature of Excel is that some of the built-in formats are culture-aware. That includes the datetime format m/d/yyyy h:mm. Specifying that format will display single-digit months and two-digit years in the user's locale. A user in the US will see 7/21/2010 while a user in any other country will see 21/7/2010. I'm not sure if / is also localized, which means Germans and Russians see 21.7.2010.

It's also possible to specify built-in formats using their NumberFormatId number, which is where 22 comes from. In the Excel document, the format will appear as a numFmtId attribute. In the source code you linked to, the NumberFormat class has both a numFmtId and a formatCode attribute.

Another quirk/feature is that patterns matching a built-in format are replaced by that format's Format ID. Or the format ID takes precedence over the patter, I'm not sure. This means you can't force the US format with m/d/y h:mm. Excel will treat this as the built-in short datetime.