I am trying to set the startrow of a df.to_excel() as a variable.
I have several df (9) that I would like to put in an excel one after another in the same excel sheet, but I will do this task every week and I do not know the size of the df, so I need to set the startrow as a variable.
In VBA it is something like this:
startrow = startrow + len(df_3) + 4 #ADD 4 rows blanks
in Python the df.to_excel( …. , startrow = … ) only allows numbers.
do you have an idea 🙂
thx
>Solution :
You can achieve this in python the same way you use to in VBA. Try the following:
import pandas as pd
# Create some sample DataFrames
df_1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
df_2 = pd.DataFrame({'A': [7, 8, 9], 'B': [10, 11, 12]})
df_3 = pd.DataFrame({'A': [13, 14, 15], 'B': [16, 17, 18]})
# Initialize the start row
start_row = 0
# Create a new Excel file
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
# Write each DataFrame to the same sheet, starting from the next available row
df_1.to_excel(writer, sheet_name='Sheet1', startrow=start_row, index=False)
start_row += len(df_1) + 4 # Add 4 blank rows
df_2.to_excel(writer, sheet_name='Sheet1', startrow=start_row, index=False)
start_row += len(df_2) + 4 # Add 4 blank rows
df_3.to_excel(writer, sheet_name='Sheet1', startrow=start_row, index=False)
# Save the Excel file
writer.close()
This would save all your dfs to one sheet with 4 blank rows in between.