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.bookworksheet=workbook.add_worksheet('Validation')writer.sheets['Validation'] = worksheetdf.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.

user3817518: "Please also share if there is another way to put many dataframes into excel using the built-in df.to_excel functionality !!"

Here's my attempt:

Easy way to put together a lot of dataframes on just one sheet or across multiple tabs. Let me know if this works!

-- To test, just run the sample dataframes and the second and third portion of code.

Sample dataframes

import pandas as pdimport numpy as np# Sample dataframes    randn = np.random.randndf = pd.DataFrame(randn(15, 20))df1 = pd.DataFrame(randn(10, 5))df2 = pd.DataFrame(randn(5, 10))

Put multiple dataframes into one xlsx sheet

# funtiondef multiple_dfs(df_list, sheets, file_name, spaces):    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')       row = 0    for dataframe in df_list:        dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0)           row = row + len(dataframe.index) + spaces + 1    writer.save()# list of dataframesdfs = [df,df1,df2]# run functionmultiple_dfs(dfs, 'Validation', 'test1.xlsx', 1)

Put multiple dataframes across separate tabs/sheets

# functiondef dfs_tabs(df_list, sheet_list, file_name):    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')       for dataframe, sheet in zip(df_list, sheet_list):        dataframe.to_excel(writer, sheet_name=sheet, startrow=0 , startcol=0)       writer.save()# list of dataframes and sheet namesdfs = [df, df1, df2]sheets = ['df','df1','df2']    # run functiondfs_tabs(dfs, sheets, 'multi-test.xlsx')

The answer by Adrian can be simplified as follows

writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)
another_df.to_excel(writer,sheet_name='Validation',startrow=20, startcol=0)

Works for pandas 0.25.3 with python 3.7.6