Pandas Excel Writer using Openpyxl with an existing workbook

Question

I have code from the time that I am reusing for a new task. The challenge is to write a new DataFrame to a new sheet into an existing excel file. But there is one part of the code that I don’t understand, but that just makes the code “work”.

working:

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()

A small line writer.book=bookpuzzled me. Without this piece of code, the Excel file will delete all other sheets, with the exception of the sheet used in parameter sheetname=c df1.to_excel.

I looked through the documentation xlsxwriteras well openpyxl, but it may not seem like why this line gives me the expected result. Any ideas?

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

Answer 1

ExcelWriter openpyxl .

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])