example data:
| A | B |
|---|---|
| 1 | 2020/10/01 |
| 2 | 2021/10/01 |
Im using pandas.to_excel like so:
df = pd.DataFrame(list(data))
writer = pd.ExcelWriter("excel.xlsx", engine='xlsxwriter', date_format="dd/mm/yyyy;@")
df.to_excel(writer_head, sheet_name='Sheet 1', index=False, startrow=4)
then i create the formatting like this:
df.to_excel(writer, sheet_name='Sheet 1', index=False, startrow=1)
workbook = writer.book
worksheet = writer.sheets['Sheet 1']
# format
date_align = workbook.add_format({
'align': 'center',
'valign': 'vcenter',
'num_format': 'dd/mm/yyyy;@',
})
So i tried to apply the formatting like this:
worksheet.set_column('B:B', 13, date_align)
writer.save()
But it didn't work, the date being created from pd.to_excel() doesn't change in alignment nor number format, but if i tried to write the data manually it worked like so:
worksheet.write('B', datetime.now().today())
worksheet.set_column('B:B', 13, date_align)
writer.save()
Now that's worked, but i want the data from pd.to_excel() to be formatted, and i checked the type from the list is indeed datetime.date and the excel output has category of 'Date' not custom or anything else. Oh and the alignment worked fine using pd.to_excel() as long as it is not date or datetime
