0

I have an xlsx template that contains a dashboard on the first page which reads in data from several sheets. My goal is to write into these sheets without replacing the content of the other sheets/dashboard. My code is as follows:

import os
import pandas as pd
import openpyxl

dataframe1 = pd.read_excel('df1.xlsx')
dataframe2 = pd.read_excel('df2.xlsx')

with pd.ExcelWriter('template.xlsx', mode='a', engine = 'openpyxl') as 
writer:
dataframe1.to_excel(writer, sheet_name='sheet10')
dataframe2.to_excel(writer, sheet_name='sheet11')
writer.save()
writer.close()

Unfortunately, this results in my template being replaced by only those two sheets, instead of them being appended to the file.

The_Dude
  • 39
  • 4
  • Did you check this post : https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas – J.K Sep 08 '19 at 06:04

1 Answers1

0
import os
import glob
import pandas as pd
import openpyxl

all_data = pd.DataFrame()

#Here you'll extract all .xlsx file and append it in all_data

for f in glob.glob("*.xlsx"):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

 #Now write data into output file

writeData = pd.ExcelWriter('template.xlsx')
all_data.to_excel(writeData,'sheet1')
writer.save()

writer.close()

May be this would help you.

Shubham Developer
  • 81
  • 4
  • I get the error 'name 'writer' is not defined', so I tried 'WriteData.save()' and 'WriteData.close()'. However, this still overwrites my templates, and also does this solution not copy all my dataframes into one sheet? I need them the dataframes to be appended into specific sheets in the template. – The_Dude Sep 08 '19 at 08:34