1##############################################################################
2#
3# An example of converting a Pandas dataframe with datetimes to an xlsx file
4# with a default datetime and date format using Pandas and XlsxWriter.
5#
6# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
7#
8
9import pandas as pd
10from datetime import datetime, date
11
12# Create a Pandas dataframe from some datetime data.
13df = pd.DataFrame({'Date and time': [datetime(2015, 1, 1, 11, 30, 55),
14                                     datetime(2015, 1, 2, 1,  20, 33),
15                                     datetime(2015, 1, 3, 11, 10    ),
16                                     datetime(2015, 1, 4, 16, 45, 35),
17                                     datetime(2015, 1, 5, 12, 10, 15)],
18                   'Dates only':    [date(2015, 2, 1),
19                                     date(2015, 2, 2),
20                                     date(2015, 2, 3),
21                                     date(2015, 2, 4),
22                                     date(2015, 2, 5)],
23                   })
24
25# Create a Pandas Excel writer using XlsxWriter as the engine.
26# Also set the default datetime and date formats.
27writer = pd.ExcelWriter("pandas_datetime.xlsx",
28                        engine='xlsxwriter',
29                        datetime_format='mmm d yyyy hh:mm:ss',
30                        date_format='mmmm dd yyyy')
31
32# Convert the dataframe to an XlsxWriter Excel object.
33df.to_excel(writer, sheet_name='Sheet1')
34
35# Get the xlsxwriter workbook and worksheet objects in order to set the column
36# widths, to make the dates clearer.
37workbook  = writer.book
38worksheet = writer.sheets['Sheet1']
39
40worksheet.set_column('B:C', 20)
41
42# Close the Pandas Excel writer and output the Excel file.
43writer.save()
44