英文:
ExcelWriter using openpyxl engine ignoring date_format parameter
问题
我已经阅读了很多关于这个问题的答案，但当我运行我的代码时，我得到的结果不同。
我正在使用pandas 2.0.0和Python 3.9上的openpyxl 3.1.2。
这是我的问题的一个简化示例，即I无法让ExcelWriter遵守我的日期格式选择。我试图将一个新的工作表附加到现有的Excel .xlsx文件中。
import pandas as pdimport datetimefilePath = 'c:\\temp\\myfile.xlsx'writer = pd.ExcelWriter(filePath, mode='a', engine='openpyxl', if_sheet_exists='replace', date_format='DD/MM/YYYY')df = pd.DataFrame([datetime.date(2023, 4, 7)], columns=['Date'])df.to_excel(writer, sheet_name='Data')writer.close()
在Excel中的结果如下图所示：
我已经明确将数据框中的值的类型设置为datetime.date。我尝试使用datetime_format，或者两者一起使用，但都没有成功。
我还尝试了xlsxwriter，但似乎这个引擎不允许追加到现有的工作簿。
英文:
I have read quite a few answers on this, but when I run my code I don't get the same result.
I am using pandas 2.0.0 and openpyxl 3.1.2 on Python 3.9
This is a reduced example of my issue, which is that I can't get the ExcelWriter to respect my choice of date format. I am trying to append a new sheet to an existing Excel .xlsx file.
import pandas as pdimport datetimefilePath = 'c:\\temp\\myfile.xlsx'writer = pd.ExcelWriter(filePath,mode='a',engine='openpyxl',if_sheet_exists='replace',date_format='DD/MM/YYY')df = pd.DataFrame([datetime.date(2023,4,7)],columns=['Date'])df.to_excel(writer,sheet_name='Data')writer.close()
The result in Excel is this:
I have explicitly set the type of the value in the dataframe to be datetime.date. I have tried using datetime_format or indeed both together but to no avail.
I have also tried xlsxwriter but it seems this engine does not allow appending to an existing workbook.
答案1
得分: 2
这似乎是OpenpyxlWriter的实现中的一个错误：
class OpenpyxlWriter(ExcelWriter):_engine = "openpyxl"_supported_extensions = (".xlsx", ".xlsm")def __init__(self,path: FilePath | WriteExcelBuffer | ExcelWriter,engine: str | None = None,date_format: str | None = None,datetime_format: str | None = None,mode: str = "w",storage_options: StorageOptions = None,if_sheet_exists: str | None = None,engine_kwargs: dict[str, Any] | None = None,**kwargs,) -> None:# 使用 openpyxl 模块作为 Excel 写入器。from openpyxl.workbook import Workbookengine_kwargs = combine_kwargs(engine_kwargs, kwargs)super().__init__(path,mode=mode,storage_options=storage_options,if_sheet_exists=if_sheet_exists,engine_kwargs=engine_kwargs,)
要修复，将以下内容添加到 super().__init__() 调用中：
date_format=date_format,datetime_format=datetime_format,
英文:
This appears to be a bug in the implementation of OpenpyxlWriter:
class OpenpyxlWriter(ExcelWriter):_engine = "openpyxl"_supported_extensions = (".xlsx", ".xlsm")def __init__(self,path: FilePath | WriteExcelBuffer | ExcelWriter,engine: str | None = None,date_format: str | None = None,datetime_format: str | None = None,mode: str = "w",storage_options: StorageOptions = None,if_sheet_exists: str | None = None,engine_kwargs: dict[str, Any] | None = None,**kwargs,) -> None:# Use the openpyxl module as the Excel writer.from openpyxl.workbook import Workbookengine_kwargs = combine_kwargs(engine_kwargs, kwargs)super().__init__(path,mode=mode,storage_options=storage_options,if_sheet_exists=if_sheet_exists,engine_kwargs=engine_kwargs,)
To fix, add
date_format=date_format,datetime_format=datetime_format,
to the super().__init__() call:
super().__init__(path,date_format=date_format,datetime_format=datetime_format,mode=mode,storage_options=storage_options,if_sheet_exists=if_sheet_exists,engine_kwargs=engine_kwargs,)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论