xlsxappender.py

""" Appending DataFrame data to Excel Worksheets.

    This script appends the contents of a dataframe to an existing
    Excel (xlsx) file.  If the file doesn't exist, it will create
    a blank Excel file with the expected sheet names.

"""
import pandas as pd
from openpyxl import load_workbook, Workbook
import os


filename = "example.xlsx"  # Excel filename to append to.

# If the file doesn't exist, lets create a blank one with the right
# worksheet names so we have something to use for the rest of the process.

if not os.path.exists(filename):
    print("Creating blank XLSX file...")
    wb = Workbook()
    ws = wb.active
    print("Creating default sheet's")
    ws.title = "a"
    wb.create_sheet("b")
    wb.create_sheet("c")
    wb.save(filename)

book = load_workbook(filename)

# A few  of DataFrames to play with:

data_one = pd.DataFrame(
    [["a", "b"], ["c", "d"]], index=["row 1", "row 2"], columns=["col 1", "col 2"]
)
df_one = pd.DataFrame(data=data_one)

data_two = pd.DataFrame(
    [["e", "f"], ["g", "h"]], index=["row 1", "row 2"], columns=["col 1", "col 2"]
)
df_two = pd.DataFrame(data=data_two)

data_three = pd.DataFrame(
    [["i", "j"], ["k", "l"]], index=["row 1", "row 2"], columns=["col 1", "col 2"]
)
df_three = pd.DataFrame(data=data_three)

# Create a writer object, and create references in that object to the
# existing workbook we have open already (`book`):

writer = pd.ExcelWriter(filename, engine="openpyxl")
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

# Export to Excel
# Doing a few things here, we don't want the header appended every time, so `header=False`
# Nor do we really need the Pandas index, so `index=False`
# and we use the `max_row` to read the last row of df data in the sheet.

df_one.to_excel(
    writer,
    sheet_name="a",  # name of the sheet to write to.
    startrow=writer.sheets["a"].max_row,  # the row to start writing the data from
    index=False,  # don't export panda's index.
    header=False,  # dont't write the column headers.
)

df_two.to_excel(
    writer,
    sheet_name="b",
    startrow=writer.sheets["b"].max_row,
    index=False,
    header=False,
)

df_three.to_excel(
    writer,
    sheet_name="c",
    startrow=writer.sheets["c"].max_row,
    index=False,
    header=False,
)

writer.save()