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.