Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- """ 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()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement