创建Excel文件时如何保留日期格式? [英] How to preserve date format when creating an Excel file?

问题描述

我有一个 .xlsx 文件，我将其导入 Python 并创建了一个 Pandas 数据框..xlsx 文件中的一列被格式化为日期，mm-dd-yyyy 并像这样导入.然后我从该数据框中删除一些不需要的列，并使用 xlsxwriter 引擎导出该数据框以创建另一个 Excel 文件.

I have an .xlsx file that I import into Python and create a Pandas dataframe. One of the columns in the .xlsx file is formatted as a date, mm-dd-yyyy and gets imported like that. I then delete some unneeded columns from that dataframe and export that dataframe using the xlsxwriter engine to create another Excel file.

writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', date_format='mm/dd/yyyy')

当我这样做时，日期列的格式会发生变化，并且时间会自动添加到日期列中.列中的单元格遵循此格式，yyyy-mm-dd hh:mm:ss.

When I do that, the date column's format changes and time automatically gets added to the date column. The cells in the column then follow this format, yyyy-mm-dd hh:mm:ss.

我找到了一个潜在的解决方案 但是当我尝试下面的代码时，

I found a potential solution but when I tried the code below,

df.set_index(df.index.map(lambda x: x.strftime('%m/%d/%Y'))).to_excel()

我收到错误

AttributeError: 'int' object has no attribute 'strftime'

我还阅读无法格式化任何已经有应用默认格式.

I also read that it isn't possible to format any cells that already have a default format applied.

这是一个失败的原因吗?如何在导出为 .xlsx 文件时保留预先存在的日期格式?

Is this a lost cause? How can I preserve the pre-existing date format upon export as an .xlsx file?

推荐答案

试试这个:

writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', 
                        date_format='mm/dd/yyyy', datetime_format='mm/dd/yyyy')

这篇关于创建Excel文件时如何保留日期格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！