when writing the date to the excel file, the information is recognized as text. How to set the date format first? enter image description here

for d, dirs, files in os.walk(folder): for f in files: full_name = path.join(d, f) if path.isfile(full_name): time_info = [(fn(full_name)) for fn in (path.getatime, path.getmtime, path.getctime)] dict1 = [datetime.fromtimestamp(time_info[0]).strftime('%d.%m.%Y %H:%M:%S'), datetime.fromtimestamp(time_info[0]).strftime('%d.%m.%Y %H:%M:%S')] dict2.append(dict1) df = pd.DataFrame(dict2, columns=('время последнего доступа', 'время последнего доступа')) df writer = pd.ExcelWriter('listdir1.xlsx', engine='xlsxwriter', datetime_format='dd.mm.yyyy', date_format='dd.mm.yyyy') df.to_excel(writer, index=False) writer.save()

the result is the same as in my picture, i.e. excel sees information as text

  • Give a test example of your DataFrame. What are the types of your DF? - MaxU
  • And in what format would you like to write in Excel? - MaxU
  • as in the example from 2nd to 4th line. they are converted only after editing, and by default the text is written to excel as in the example in the other cells - Alexander Kudryavtsev

1 answer 1

Here is an example:

 In [28]: df = pd.DataFrame({'Date':pd.date_range('2017-01-01', freq='99999S', periods=10)}) In [29]: df Out[29]: Date 0 2017-01-01 00:00:00 1 2017-01-02 03:46:39 2 2017-01-03 07:33:18 3 2017-01-04 11:19:57 4 2017-01-05 15:06:36 5 2017-01-06 18:53:15 6 2017-01-07 22:39:54 7 2017-01-09 02:26:33 8 2017-01-10 06:13:12 9 2017-01-11 09:59:51 In [30]: writer = pd.ExcelWriter('c:/temp/test_date.xlsx', ...: engine='xlsxwriter', ...: datetime_format='dd.mm.yyyy', ...: date_format='dd.mm.yyyy') ...: In [31]: df.to_excel(writer, index=False) In [32]: writer.save()

Result ( c:/temp/test_date.xlsx ):

enter image description here

I also recommend that you read Pandas and XlsxWriter by Working with Python by XlsxWriter

  • what am i doing wrong - Alexander Kudryavtsev
  • @Alexander Kudryavtsev , I have not yet learned to read thoughts, although I am working in this direction ;-) - MaxU
  • I corrected the code of your example (added in the question). still excel sees everything as text - Alexander Kudryavtsev
  • @Alexander Kudryavtsev , strange, everything works out correctly for me - see the updated picture ... - MaxU
  • @Alexander Kudryavtsev, what are your versions of Pandas and XlsxWriter? print(pd.show_versions()) - MaxU