How to append a dataframe to existing excel sheet?

Question

The function gets called several times. I have kept a count so that if its called for the first time, A workbook is created. Then I write to that workbook using pd.ExcelWrite(). Next time else: gets executed and the same workbook is opened. Its first sheet is selected, its last row is found. And DataFrame is written on that row. This is my code:

def WriteFile (df):
  if count1 == 1:
      workbook = xlsxwriter.Workbook('pandas_simple.xlsx')
      writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
      df.to_excel(writer, index=False )
      workbook.close()
  else:
      book = open_workbook('pandas_simple.xlsx')
      sheet_names = book.sheet_names()
      sheet = book.sheet_by_name(sheet_names[0])
      row = sheet.nrows
      writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
      df.to_excel(writer, index=False, header=False, startrow = row )

I get this exception:

Exception Exception: Exception('Exception caught in workbook destructor. Explicit close() 
may be required for workbook.',) in <bound method Workbook.__del__ of <xlsxwriter.workbook.Workbook 
object at 0x000000000A143860>> ignored Exception

And my pandas_simple.xlsx is also empty after execution of code. What am I doing wrong?

Possible duplicate of [How to write to an existing excel file without overwriting data (using pandas)?](http://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) — Merlin, Aug 21 '16 at 20:53
The exception is most likely caused by not calling the XlsxWriter workbook destructor via `close()` or Pandas `save()`. The error message tries to hint at this. It may not fix your overall issue but, as a first step, you should add `close()` or `save()` to resolve the exception. — jmcnamara, Aug 21 '16 at 22:50
Also, you cannot rewrite or append to a file using XlsxWriter, you will just end up with a new file. — jmcnamara, Aug 21 '16 at 22:53

score 3 · Answer 1 · edited May 23 '17 at 11:33

3

Thanks to @ski.

Please refer his ans on the same question

How to write to an existing excel file without overwriting data (using pandas)?

import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

edited May 23 '17 at 11:33

Community

1
1

answered Aug 21 '16 at 19:39

Shijo

9,313
3
19
31

2

This approach will not work. I have a dataframe which I need to write in an xlsx file. This work is being done in a loop. Dataframe gets reloaded and now needs to be appended in the same xlsx file. – el323 Aug 21 '16 at 19:58

MaxU - stand with Ukraine · Answer 2 · 2016-08-21T20:55:21.837

2

you can do it this way:

df = pd.DataFrame(np.arange(1, 31), columns=['val'])
fn = 'd:/temp/test.xlsx'
count = 0

writer = pd.ExcelWriter(fn)

for chunk in np.split(df, 3):
    chunk.to_excel(writer, index=False, header=(count==0), startrow=count+(count!=0))
    count += len(chunk)

writer.save()

Result:

edited Aug 21 '16 at 20:55

answered Aug 21 '16 at 20:49

MaxU - stand with Ukraine

205,989
36
386
419

How to append a dataframe to existing excel sheet?

2 Answers2