Save List of Dataframes to Multisheet Excel Spreadsheet

Save list of DataFrames to multisheet Excel spreadsheet

You should be using pandas own ExcelWriter class:

from pandas import ExcelWriter
# from pandas.io.parsers import ExcelWriter

Then the save_xls function works as expected:

def save_xls(list_dfs, xls_path):
    with ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer,'sheet%s' % n)

Easy way to export multiple data.frame to multiple Excel worksheets

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx:
http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

How to write to an existing excel file without overwriting data (using pandas)?

Pandas docs says it uses openpyxl for xlsx files. Quick look through the code in ExcelWriter gives a clue that something like this might work out:

import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 
writer.book = book

## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

List of multiple dataframes to separate Excel sheets

I think you need:

writer = pd.ExcelWriter('output.xlsx')
for i, df in enumerate(dfs, 1):
    #python 3.6+
    df.to_excel(writer, index=False,sheet_name=f'sheetName_{i}')
    #below python 3.6   
    #df.to_excel(writer, index=False,sheet_name='sheetName_{}'.format(i))
writer.save()

---

Sample:

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,0],
         'E':[5,3,6,9,2,4],
         'F':list('aaabbb')
})

#sample list of DataFrames
dfs = [df[['A','B']], df[['A','B','C']],df[['A','F']]]

writer = pd.ExcelWriter('output.xlsx')
for i, df in enumerate(dfs, 1):
    #python 3.6+
    df.to_excel(writer, index=False,sheet_name=f'sheetName_{i}')
    #below python 3.6   
    #df.to_excel(writer, index=False,sheet_name='sheetName_{}'.format(i))
writer.save()

EDIT:

If need write custom names of sheetnames:

writer = pd.ExcelWriter('output.xlsx')
names = ['a','d','b']
for df, n in zip(dfs, names):
    #python 3.6+
    df.to_excel(writer, index=False,sheet_name=f'sheetName_{n}')
    #below python 3.6   
    #df.to_excel(writer, index=False,sheet_name='sheetName_{}'.format(n))
writer.save()

Putting many python pandas dataframes to one excel worksheet

To create the Worksheet in advance, you need to add the created sheet to the sheets dict:

writer.sheets['Validation'] = worksheet

Using your original code:

# Creating Excel Writer Object from Pandas  
writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')   
workbook=writer.book
worksheet=workbook.add_worksheet('Validation')
writer.sheets['Validation'] = worksheet
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)   
another_df.to_excel(writer,sheet_name='Validation',startrow=20, startcol=0) 

---

Explanation

If we look at the pandas function to_excel, it uses the writer's write_cells function:

excel_writer.write_cells(formatted_cells, sheet_name, startrow=startrow, startcol=startcol)

So looking at the write_cells function for xlsxwriter:

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
    # Write the frame cells using xlsxwriter.
    sheet_name = self._get_sheet_name(sheet_name)
    if sheet_name in self.sheets:
        wks = self.sheets[sheet_name]
    else:
        wks = self.book.add_worksheet(sheet_name)
        self.sheets[sheet_name] = wks

Here we can see that it checks for sheet_name in self.sheets, and so it needs to be added there as well.

Writing multiple pandas dataframes to multiple excel worksheets

try something like this:

import pandas as pd
#initialze the excel writer
writer = pd.ExcelWriter('MyFile.xlsx', engine='xlsxwriter')

#store your dataframes in a  dict, where the key is the sheet name you want
frames = {'sheetName_1': dataframe1, 'sheetName_2': dataframe2,
        'sheetName_3': dataframe3}

#now loop thru and put each on a specific sheet
for sheet, frame in  frames.iteritems(): # .use .items for python 3.X
    frame.to_excel(writer, sheet_name = sheet)

#critical last step
writer.save()

---

Related Topics