CODEWITHSUNDEEP
python-3.xpandasxlsxwriter
SKP
SKP

Reputation: 161

Unable to achieve custom dateformat for df.to_excel using xlsxwriter

I am not able to achieve the dateformat using xlsxwriter. I have tried the sample code of date formatting to excel in pandas using xlsxwriter, but with slight variation by adding Sqldf step to filter out the df. here I have used only the simple filter in sqldf step for test puropose ,but have complex in real scenario.

can anybody suggest me , whats going worng here . I have checked the dtypes , it is object in both the working(actual example) and non-working(sqldf step added) case.

import pandas as pd
from datetime import datetime, date
from pandasql import sqldf

# Create a Pandas dataframe from some datetime data.
df = pd.DataFrame({'Date and time': [datetime(2015, 1, 1, 11, 30, 55),
                                     datetime(2015, 1, 2, 1,  20, 33),
                                     datetime(2015, 1, 3, 11, 10    ),
                                     datetime(2015, 1, 4, 16, 45, 35),
                                     datetime(2015, 1, 5, 12, 10, 15)],
                   'Dates only':    [date(2015, 2, 1),
                                     date(2015, 2, 2),
                                     date(2015, 2, 3),
                                     date(2015, 2, 4),
                                     date(2015, 2, 5)],
                   })

# Create a Pandas Excel writer using XlsxWriter as the engine.
# Also set the default datetime and date formats.
s_df =  sqldf('select * from df',locals())
writer = pd.ExcelWriter("pandas_datetime.xlsx",
                        engine='xlsxwriter',
                        datetime_format='mmm d yyyy hh:mm:ss',
                        date_format='mmmm dd yyyy')

# Convert the dataframe to an XlsxWriter Excel object.
s_df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects in order to set the column
# widths, to make the dates clearer.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

worksheet.set_column('B:C', 20)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

output :

enter image description here

Upvotes: 2

Views: 554

Answers (1)

jmcnamara
jmcnamara

Reputation: 41554

The sqldf() function seems to have converted the datetime objects to general objects which are then stringified when written with to_excel(). You can see this from your screenshot where the "Date and time" and "Dates only" values are aligned to the left of the cell (which indicates that they are strings) instead of to the right of the cell which would indicate numbers/dates.

Upvotes: 2

Related Questions