使用Openpyxl和现有工作簿的Pandas Excel Writer [英] Pandas Excel Writer using Openpyxl with existing workbook

问题描述

我有一阵子的代码，我正在重复使用它来完成一项新任务.任务是将新的DataFrame写入新的工作表中，并写入现有的excel文件中.但是我不了解其中一部分代码，但这只是使代码起作用".

I have code from a while ago that I am re-using for a new task. The task is to write a new DataFrame into a new sheet, into an existing excel file. But there is one part of the code that I do not understand, but it just makes the code "work".

工作:

from openpyxl import load_workbook
import pandas as pd
file = r'YOUR_PATH_TO_EXCEL_HERE'

df1 = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
book = load_workbook(file)
writer = pd.ExcelWriter(file, engine='openpyxl')
writer.book = book # <---------------------------- piece i do not understand
df1.to_excel(writer, sheet_name='New', index=None)
writer.save()

writer.book=book的一小部分让我难过.没有该代码段，Excel文件将删除所有其他工作表，但df1.to_excel中sheetname=参数中使用的工作表除外.

The little line of writer.book=book has me stumped. Without that piece of code, the Excel file will delete all other sheets, except the sheet used in the sheetname= parameter in df1.to_excel.

我查看了 xlsxwriter的文档以及

i looked at xlsxwriter's documentation as well as openpyxl's, but cannot seem to figure out why that line gives me my expected output. Any ideas?

我相信这篇文章是我从中得到最初想法的地方.

edit: i believe this post is where i got the original idea from.

推荐答案

在ExcelWriter的源代码中，使用openpyxl初始化空工作簿并删除所有工作表.这就是为什么您需要显式添加

In the source code of ExcelWriter, with openpyxl, it initializes empty workbook and delete all sheets. That's why you need to add it explicitly

class _OpenpyxlWriter(ExcelWriter):
    engine = 'openpyxl'
    supported_extensions = ('.xlsx', '.xlsm')

    def __init__(self, path, engine=None, **engine_kwargs):
        # Use the openpyxl module as the Excel writer.
        from openpyxl.workbook import Workbook

        super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)

        # Create workbook object with default optimized_write=True.
        self.book = Workbook()

        # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
        if self.book.worksheets:
            try:
                self.book.remove(self.book.worksheets[0])
            except AttributeError:

                # compat
                self.book.remove_sheet(self.book.worksheets[0])

这篇关于使用Openpyxl和现有工作簿的Pandas Excel Writer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！