multiple dataframes per sheet, multiple sheets per workbook

Question

I wrote a function so that I can write multiple dataframes onto a single excel sheet, then have multiple sheets within one excel workbook. I am able to achieve the first part, but not the second. When the script runs, it overwrites the existing tab and writes over it with the new one, meaning that every time I open the output file, it only has one sheet, the latest one.

I've always been a little puzzled by the writer.save() function and when to use it (i.e.: in or our of a loop). Any suggestions?

def dfs_to_sheet(dflist, bookname, sheetname, startrow = 0):
    writer = pd.ExcelWriter('{}.xlsx'.format(bookname), engine='openpyxl')
    numdfs = 0
    startrow = 0
    for i in dflist:
        if numdfs == 0:
            i.to_excel(writer, sheet_name=sheetname,  startrow = startrow)
        elif numdfs != 0:
            i.to_excel(writer, sheet_name = sheetname, startrow = startrow)
        startrow = startrow + len(i) + 2
        numdfs += 1
    writer.save()

score 2 · Accepted Answer · answered Nov 26 '18 at 20:11

The answer you are looking for is related to the solution here, but since you want to call this over multiple iterations, it's a little more complicated. My solution is below, but keep in mind, this gets messy if there are duplicate sheets (e.g., if Sheet1 exists, and you try to add Sheet1 again, it becomes Sheet11).

import pandas as pd
import os.path
from openpyxl import load_workbook

def dfs_to_sheet(dflist, bookname, sheetname, startrow = 0):
    book_file = '{}.xlsx'.format(bookname)
    writer = pd.ExcelWriter(book_file, engine='openpyxl')
    if os.path.isfile(book_file):
        writer.book = load_workbook(book_file)
    numdfs = 0
    startrow = 0
    for i in dflist:
        if numdfs == 0:
            i.to_excel(writer, sheet_name = sheetname,  startrow = startrow)
        elif numdfs != 0:
            i.to_excel(writer, sheet_name = sheetname, startrow = startrow)
        startrow = startrow + len(i) + 2
        numdfs += 1
    writer.save()
    writer.close()

score 0 · Answer 2 · answered Nov 26 '18 at 19:32

This is how I do it. New sheet should be added to a Excel workbook, and the workbook object also should get the new sheet.

writer = pd.ExcelWriter('example.xlsx', engine='xlsxwriter')
wb = writer.book
sh = wb.add_worksheet('Sheet1')
writer.sheets['Sheet1'] = sh
sh2 = wb.add_worksheet('Sheet2')
writer.sheets['Sheet2'] = sh2
........
sh.white(...

multiple dataframes per sheet, multiple sheets per workbook

2 Answers2

Linked

Related