Right now I'm working on combining Excel sheets into 1 new sheet, using pandas which is working. The only problem is that the value inside the new Excel sheet are plain numbers instead of the Formulas and I would like the Formulas.
Loading file
directory = os.path.dirname(__file__)
fname = os.path.join(directory, "Reisanalyze.xlsm")
print("Loading %s..." % fname)
sheet1 = pd.read_excel(fname, sheetname="Input")
sheet2 = pd.read_excel(fname, sheetname="Alternatieven")
Write to new sheet
writer = pd.ExcelWriter('first_sheet.xlsx', engine='xlsxwriter')`**
sheet1.to_excel(writer, sheet_name='Input', merge_cells=False, startrow=0, startcol=0)
sheet2.to_excel(writer, sheet_name='Input', merge_cells=False, startrow=0, startcol=21)
I originally tried to use the pycel project which worked until I needed to load multiple sheets, which didn't work. That's why I'm using pandas to write multiple sheets into 1 sheet.