CODEWITHSUNDEEP
pythonxlsxwriter
mancal
mancal

Reputation: 95

Using xlsx writer to write custom date format

I am writing a dataframe into excel and using xlsx writer to format my date columns to a custom format but the excel always contains a datetime value and ignores the custom formatting specified in my code. Here is the code:

writer = ExcelWriter(path+'test.xlsx', engine='xlsxwriter')
workbook = writer.book
df.to_excel(writer,sheet_name='sheet1', index=False, startrow = 1, header=False)
worksheet1 = writer.sheets['sheet1']
fmt = workbook.add_format({'num_format':'d-mmm-yy'})
worksheet1.set_column('C:C', None, fmt)

# Adjusting column width
worksheet1.set_column(0, 20, 12)
# Adding back the header row
column_list = df.columns
for idx, val in enumerate(column_list):
    worksheet1.write(0, idx, val)
writer.save()

Here I want 'd-mmm-yy' format for column C but the exported excel contains datetime values. I also don't want to use strftime to convert my columns to strings to ensure easy date filtering in excel.

Excel output: enter image description here

Upvotes: 1

Views: 1547

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

The reason this doesn't work as expected is because Pandas uses a default datetime format with datetime objects and it applies this format at the cell level. In XlsxWriter, and Excel, a cell format overrides a column format so you column format has no effect.

The easiest way to handle this is to specify the Pandas date (or datetime) format as a parameter in pd.ExcelWriter():

import pandas as pd
from datetime import date

df = pd.DataFrame({'Dates': [date(2020, 2, 1),
                             date(2020, 2, 2),
                             date(2020, 2, 3),
                             date(2020, 2, 4),
                             date(2020, 2, 5)]})

writer = pd.ExcelWriter('pandas_datetime.xlsx',
                        engine='xlsxwriter',
                        date_format='d-mmm-yy')

df.to_excel(writer, sheet_name='Sheet1')

writer.save()

Output:

enter image description here

See also this Pandas Datetime example from the XlsxWriter docs.

Upvotes: 2

Related Questions