Skip to content

Instantly share code, notes, and snippets.

@Ze1598
Last active January 25, 2024 02:15
Show Gist options
  • Save Ze1598/eb4b4610e37c22be76fa644eccdd515a to your computer and use it in GitHub Desktop.
Save Ze1598/eb4b4610e37c22be76fa644eccdd515a to your computer and use it in GitHub Desktop.
Given an already existing Excel workbook, create and append new worksheets to the file using Pandas
import pandas as pd
import openpyxl as pxl
# DataFrame to be inserted in the first worksheet
firstMockData = {
'a': [1,2],
'b': [3,4]
}
firstMockDF = pd.DataFrame(firstMockData)
# Name of the workbook we'll be using
filename = 'test_wb.xlsx'
# This creates a new workbook, which will contain only one sheet: sheetA
firstMockDF.to_excel(filename, 'sheetA', index=False)
# This recreates the workbook test_wb, again with a single sheet: sheetB
# firstMockDF.to_excel(filename, 'sheetB')
# --------------------------------------------------------
# Properly load the workbook
excel_book = pxl.load_workbook(filename)
# Inside this context manager, handle everything related to writing new data to the file\
# without overwriting existing data
with pd.ExcelWriter(filename, engine='openpyxl') as writer:
# Your loaded workbook is set as the "base of work"
writer.book = excel_book
# Loop through the existing worksheets in the workbook and map each title to\
# the corresponding worksheet (that is, a dictionary where the keys are the\
# existing worksheets' names and the values are the actual worksheets)
writer.sheets = {worksheet.title: worksheet for worksheet in excel_book.worksheets}
secondMockData = {
'c': [10,20],
'd': [30,40]
}
# This is the new data to be written to the workbook
secondMockDF = pd.DataFrame(secondMockData)
# Write the new data to the file without overwriting what already exists
secondMockDF.to_excel(writer, 'sheetB', index=False)
# Save the file
writer.save()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment