英文:
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 pd
import datetime
filePath = '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 pd
import datetime
filePath = '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 Workbook
engine_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 Workbook
engine_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,
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论