3

I want to import the values from a Pandas dataframe into an existing Excel sheet. I want to insert the data inside the sheet without deleting what is already there in the other cells (like formulas using those datas etc).

I tried using data.to_excel like:

writer = pd.ExcelWriter(r'path\TestBook.xlsm')    
data.to_excel(writer, 'Sheet1', startrow=1, startcol=11, index = False)    
writer.save()

The problem is that this way i overwrite the entire sheet. Is there a way to only add the dataframe? It would be perfect if I could also keep the format of the destination cells. Thanks

micric
  • 621
  • 4
  • 15
  • 1
    Maybe you will find the answer here: possible duplicate of https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas – Cribber Jan 10 '19 at 10:23
  • My bad, I tried that solution and it didn't work. But I just noticed my file is .xslm, and it gets corrupted when I write on it like that. I'll correct my question. – micric Jan 10 '19 at 10:34
  • 1
    Ok, I found that it's sufficient to add keep_vba=True (thanks to https://stackoverflow.com/questions/49470871/how-write-to-xlsm-using-openpyxl ). – micric Jan 10 '19 at 10:43
  • seems like it is not possible to write to `xlsm` files with `pandas` –  Jan 10 '19 at 10:51
  • It looks like it breaks the macros.. – micric Jan 10 '19 at 11:42
  • Maybe try this solution: "You can workaround this with recent versions of XlsxWriter by extracting the VbaProject.bin macro file from a real xlsm file and inserting it into the new file" https://stackoverflow.com/questions/28169731/write-pandas-dataframe-to-xlsm-file-excel-with-macros-enabled – Cribber Jan 10 '19 at 12:14
  • In my head it could work like this: get contents, get macros, add your df to contents and then add the VbaProject.bin macro file to the new file. But then again, I'm by no means an expert. – Cribber Jan 10 '19 at 12:15
  • Thanks for the suggestions! They made me understand how Excel actually creates the xlsm file, and I found out that the macros are actually there (because the vba file was mantained), but i can't activate them using the buttons because the link is broken (just like the plots are lost). In the developer tab the macros are actually there and workin.. Now I only have to understand how to keep the graphical inteface alive... – micric Jan 10 '19 at 12:55

2 Answers2

0

I found a good solution for it. Xlwings natuarally supports pandas dataframe: https://docs.xlwings.org/en/stable/datastructures.html#pandas-dataframes

micric
  • 621
  • 4
  • 15
0

The to_excel function provides a mode parameter to insert (w) of append (a) a data frame into an excel sheet, see below example:

with pd.ExcelWriter(p_file_name, mode='a') as writer:
    df.to_excel(writer, sheet_name='Data', startrow=2, startcol=2)
wfolkerts
  • 99
  • 1
  • 4