load_workbook(writer.path) 的writer.path 为None，pandas写入excel

目录

问题描述

 官方文档

操作

---

问题描述

先贴代码

这段代码功能主要是覆盖excel中某一个sheet页，保留其他sheet页，代码在开发环境一直跑的好好的，今天突然被前端反馈出现了bug:“except str ,bytes or os.PathLike object,not NoneType”

 从头到尾检查，发现是 writer.path的值为None，openpyxl无法读取。

但是该段代码在本地是可以正常执行的，唯独到了服务器上出现了问题，通过在网上各种查资料也并没有解决，但是有些博主的文章还是给了些启发，有可能是版本的问题，通过对比发现openpyxl的版本都是3.0.9，服务器上的pandas是1.4.3版本，但是本地的是1.1.5，难道真的是版本问题！？尝试将服务器上pandas版本降级，重新执行上述代码，成功跑通。

但是纠结了很久，高版本不应该向下兼容吗？于是赶紧查了官方文档,不但找到了原因，还发现了更好的操作方法，下边贴一下官方文档内容

官方文档

pandas documentation — pandas 1.4.4 documentation

pandas.ExcelWriter
class pandas.ExcelWriter(path, engine=None, date_format=None, datetime_format=None, mode='w',
storage_options=None, if_sheet_exists=None, engine_kwargs=None, **kwargs)

Class for writing DataFrame objects into excel sheets.
Default is to use : * xlwt for xls * xlsxwriter for xlsx if xlsxwriter is installed otherwise openpyxl * odf for ods.
See DataFrame.to_excel for typical usage.
The writer should be used as a context manager. Otherwise, call close() to save and close any opened file handles.

ExcelWriter当中主要包括path,engine,date_format,datetime_format,mode,storage_options,if_sheet_exists,engine_kwargs等参数，如下对参数进行说明

Parameters
path [str or typing.BinaryIO] Path to xls or xlsx or ods file.

engine [str (optional)] Engine to use for writing. If None, defaults to io.excel.<extension>.
    writer. NOTE: can only be passed as a keyword argument.
    Deprecated since version 1.2.0: As the xlwt package is no longer maintained,             the xlwt engine
    will be removed in a future version of pandas.

date_format [str, default None] Format string for dates written into Excel files (e.g. ‘YYYYMM-DD’).

datetime_format [str, default None] Format string for datetime objects written     into Excel files.(e.g. ‘YYYY-MM-DD HH:MM:SS’).

mode [{‘w’, ‘a’}, default ‘w’] File mode to use (write or append). Append does not work with
    fsspec URLs.

storage_options [dict, optional] Extra options that make sense for a particular     storage connection, e.g. host, port, username, password, etc., if using a URL that     will be parsed by fsspec,
    e.g., starting “s3://”, “gcs://”.
    New in version 1.2.0.

if_sheet_exists [{‘error’, ‘new’, ‘replace’, ‘overlay’}, default ‘error’] How to behave when trying
to write to a sheet that already exists (append mode only).
    • error: raise a ValueError.
    • new: Create a new sheet, with a name determined by the engine.
    • replace: Delete the contents of the sheet before writing to it.
    • overlay: Write contents to the existing sheet without removing the old contents.
New in version 1.3.0.
Changed in version 1.4.0: Added overlay option

engine_kwargs [dict, optional] Keyword arguments to be passed into the engine.         These will be
    passed to the following functions of the respective engines:
    • xlsxwriter: xlsxwriter.Workbook(file, **engine_kwargs)
    • openpyxl (write mode): openpyxl.Workbook(**engine_kwargs)
    • openpyxl (append mode): openpyxl.load_workbook(file, **engine_kwargs)
    • odswriter: odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)
New in version 1.3.0.

**kwargs [dict, optional] Keyword arguments to be passed into the engine.
Deprecated since version 1.3.0: Use engine_kwargs instead.

path：就是我们需要读取文件的路径

engine：这里需要格外注意，他是用于写作的引擎，如果为None，默认为io.excel.<extension>.writer，并且只能作为关键字参数传递。1.2.0版后已移除:由于不再维护xlwt包，xlwt引擎将在未来版本的pandas中移除。

date_format、datetime_format:设置文件中日期的格式。

mode:要使用的文件模式(写入或追加)，默认是'w',当文件不存在的时候会直接创建该文件，当文件存在的时候会被清空，然后写入。使用的时候需要注意

storage_options:对于特定的存储连接有意义的额外选项，例如主机、端口、用户名、密码等，如果使用的URL将被fsspec解析.

if_sheet_exists ：试图写入已经存在的工作表时如何操作(仅当mode参数设置为"a"时)，默认是"error"，参数如下

        • error: 返回报错信息.
        • new: 创建一个新的sheet页，名称由引擎决定.
        • replace: 删除sheet页的内容，再进行写入.
        • overlay: 写入内容到现有的sheet页，而不删除旧的内容.（1.4版本之后才有）

engine_kwargs：传递给引擎的关键字参数。

操作

1.一般写入

df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
with pd.ExcelWriter("path_to_file.xlsx") as writer:
    df.to_excel(writer)

2.同一个文件中写入不同的sheet页

df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
with pd.ExcelWriter("path_to_file.xlsx") as writer:
    df1.to_excel(writer, sheet_name="Sheet1")
    df2.to_excel(writer, sheet_name="Sheet2")

3.设置文件中日期的格式

from datetime import date, datetime
df = pd.DataFrame(
    [
    [date(2014, 1, 31), date(1999, 9, 24)],
    [datetime(1998, 5, 26, 23, 33, 4), datetime(2014, 2, 28, 13, 5, 13)],
    ],
    index=["Date", "Datetime"],
    columns=["X", "Y"],)
with pd.ExcelWriter(
    "path_to_file.xlsx",
    date_format="YYYY-MM-DD",
    datetime_format="YYYY-MM-DD HH:MM:SS"
    ) as writer:
    df.to_excel(writer)

4.对已存在的excel进行追加操作

        新增sheet页

with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Sheet3")

        覆盖现有sheet页

with pd.ExcelWriter(
    "path_to_file.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="replace",
    ) as writer:
    df.to_excel(writer, sheet_name="Sheet1")

        将多个DataFrames写入单个sheet

with pd.ExcelWriter("path_to_file.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="overlay",
    ) as writer:
    df1.to_excel(writer, sheet_name="Sheet1")
    df2.to_excel(writer, sheet_name="Sheet1", startcol=3)

目前想到的就这么多，后续再遇到问题的话慢慢更新。

各位看官，点个赞再走哈