Pandas Excel Writer using Openpyxl with existing workbook
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])
MattR
I am a Data Analyst who uses Python to solve various data-centered problems. I also create powerful programs using Python. I use SQL daily along side SSIS and Tableau
Updated on June 19, 2022Comments
-
MattR almost 2 years
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".
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()
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 thesheetname=
parameter indf1.to_excel
.i looked at
xlsxwriter
's documentation as well asopenpyxl
'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.