-4

I have several worksheets in one Excel Workbook called Platforms that I am appending into one dataframe in Jupyter notebook, I then want to put the consolidated dataframe back into the original workbook in one worksheet called 'allPlatforms'. I have tried setting mode to 'w' and to 'a'. Using if_sheet_exists='overlay' scrambles the data. I think I am missing something critical in my code.

I tried this first and it worked in that it wrote to the workbook but it overwrote the whole workbook:


    with pd.ExcelWriter('C:\excel\platforms.xlsm',
                   mode='w') as writer:  
     new_df.to_excel(writer, sheet_name='allPlatforms', index=False)

I tried this second, it works if I don't create the allPlatforms worksheet before I run it but I need the code to run and run as the original worksheets get updated so it needs to work if the worksheet already exists.


    with pd.ExcelWriter('C:\excel\platforms.xlsm',
                   mode='a') as writer:  
     new_df.to_excel(writer, sheet_name='allPlatforms', index=False)

Third attempt, scrambles data but I think it is the closest:


    with pd.ExcelWriter('C:\excel\platforms.xlsm',
                    mode='a', 
                    if_sheet_exists = 'overlay') as writer:  
     new_df.to_excel(writer, sheet_name='allPlatforms', index=False)

Edit 1: Fourth attempt, the third attempt works fine if I use a new dataset, the problem seems to arise when I use my original dataset as it contains references to another workbook. No obvious benefit to using: 'replace' over 'overlay' for if_sheets_exist at this time.

Edit 2: I mistakenly listed my working file as a .xlsx file instead of a macro enabled .xlsm file. The issue writing to sheets seems to be a Pandas issue with writing to a macro enabled file. I am still looking for a tidy work around for this.

RMC
  • 16
  • 5
  • 2
    if you meant to overwrite the sheet entirely, did you mean to do `if_sheet_exists = 'replace'` instead? According to [the docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html) that would be how you overwrite it. If not, can you elaborate on what you mean by overwriting it? – Shorn Apr 25 '23 at 08:16
  • Actually yes 'replace' might be better I'll try that now, thank you – RMC Apr 25 '23 at 08:28

1 Answers1

0

After some more research I have decided to export the new dataframe to an xlsx file and reference that new xlsx in my original Workbook to get the desired outcome. It's not as tidy as I would like but it works.

It is possible to write to an xlsm file using xlsxwriter as per this question:

Write pandas dataframe to xlsm file (Excel with Macros enabled)

But it doesn't suit my needs as I require access to the file through Excel and this method triggers 'wrong file type warnings'.

RMC
  • 16
  • 5