1

I am trying to use this code to append a dataframe to an existing sheet in Excel, but instead of appending the new data to it, it creates a new sheet. Here is the code:

import pandas as pd
import openpyxl as op

df = ['normal_dataframe']

with pd.ExcelWriter('test.xlsx', engine='openpyxl', mode='a') as writer:
    df.to_excel(writer, sheet_name='Sheet1', header=False, index=False)

'test.xlsx' has a 'Sheet1', but when the file is appended, theres 2 sheets. 'Sheet1' and 'Sheet11'.

HoundDogSaw
  • 43
  • 5
  • this might help https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas – jen Apr 28 '21 at 20:32
  • I found that post as well. When i tried that method, it gives me a BadZipFile error that is a known bug with this type of work. The code above works great, it just creates a new sheet instead of appending to the one specified. – HoundDogSaw Apr 28 '21 at 20:42
  • You need to bind the workbook to the ExcelWriter object. – Charlie Clark Apr 29 '21 at 08:16
  • Can you please explain? I'm not familiar with that. – HoundDogSaw Apr 29 '21 at 14:23

1 Answers1

0

One approach with COM:

import win32com.client

xl = win32com.client.Dispatch("Excel.Application")

path = r'c:\Users\Alex20\Documents\test.xlsx'
wb = xl.Workbooks.Open(path)
ws = wb.Worksheets("Sheet1")
ws.Range("E9:F10").Value = [[9,9],[10,10]]
wb.Close(True)
xl.Quit()
Алексей Р
  • 7,507
  • 2
  • 7
  • 18