Excel store date column called Today and data type is date
| Today |
|---|
| 13/12/2021 |
| 14/12/2021 |
| 15/12/2021 |
When I import the Excel to another Excel using Python, how to change the date type to text type?
Below is my code and try to convert, but not work. Could anyone give me advice? Thanks
data.insert(0, {'Date':datetime.strptime(str(Today),'%d/%m/%Y').strftime('%d/%m/%Y')})
Excel try to recognize date format even if you cast your column Today as string.
Suppose the following dataframe:
df = pd.DataFrame({'ID': [123, 456, 789], 'Name': ['Louis', 'Paul', 'Alexandre'],
'Today': pd.date_range('2021-12-14', periods=3, freq='D')})
print(df)
# Output:
ID Name Today
0 123 Louis 2021-12-14
1 456 Paul 2021-12-15
2 789 Alexandre 2021-12-16
If you export your dataframe with df.to_excel('data.xlsx', index=False), you got:
Here, the trick:
with pd.ExcelWriter('data.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, index=False)
wb = writer.book
ws = wb.active
# For the third column only (Today, Col C)
for col in ws.iter_cols(min_col=3, max_col=3):
# For all rows below header (C2, C3, C4, ...)
for cell in col[1:]:
cell.number_format = 'DD/MM/YYYY'
Now, you got:
openpyxl not xlsxwriter.