3

I have a dataframe containing large number of records (more than 300,000 rows and 100 columns) . I want to write this dataframe into an pre exsiting excel file (say Output.xlsx).

I tried this using openpyexcel as below-

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

This is inefficient as for 1000 records it was taking around 10 seconds .

I see that PyExcelerate performance is much faster around 2 minutes for 300,000 records.

However, I was able to add a sheet to new excel file but how can I append it to existing one.

values = [df.columns] + list(df.values)
wbk = Workbook()
ws = wbk.new_sheet('mysht1', data=values)
wbk.save('out.xlsx')
#wbk.save('Output.xlsx') just override my Output.xlsx with this new tab.
Rahul
  • 31
  • 2

1 Answers1

3

PyExcelerate doesn't support reading Excel files therefore it can't easily do this. Reading is also out of scope for the library so it's unlikely to be added unfortunately. A possible, faster workaround could be to write the sheets to be appended to a new Excel file and use another script to merge the two files.

kevmo314
  • 4,223
  • 4
  • 32
  • 43