6

I have the following code, and I am trying to write a data frame into an "existing" worksheet of an Excel file (referred here as test.xlsx). Sheet3 is the targeted sheet where I want to place the data, and I don't want to replace the entire sheet with a new one.

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
book = load_workbook('test.xlsx')
writer = pd.ExcelWriter('test.xlsx')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets) # *I am not sure what is happening in this line*
df.to_excel(writer,"Sheet3",startcol=0, startrow=20)

When I am running the code line by line, I am getting this error for the last line:

AttributeError: 'Workbook' object has no attribute 'add_worksheet'. Now why am I seeing this error when I am not trying to add worksheet ?

Note: I am aware of this similar issue Python How to use ExcelWriter to write into an existing worksheet but its not working for me and I can't comment on that post either.

Leland Hepworth
  • 876
  • 9
  • 16
singularity2047
  • 951
  • 4
  • 18
  • 28
  • it is most likely due to the piece of code that you do not know what is happening. Why do you need that in there? – MattR Mar 27 '18 at 18:43
  • we need book and sheet attributes to add sheets or save to excel. 'writer.book = book' sets the result of load_book as the writer's book attribute so you can manipulate it. 'writer.sheets = dict((ws.title, ws) for ws in book.worksheets)' - this line generate the sheets attribute. I think it is needed so that we can edit the targeted sheet. – singularity2047 Mar 27 '18 at 19:21
  • have you tried looping one sheet at a time? I've done that in the past with success – MattR Mar 27 '18 at 19:38
  • I have not. How do you propose to do that ? I am quite new to python - if you could elaborate little bit with code that'd be helpful – singularity2047 Mar 27 '18 at 19:41
  • 1
    Pandas defaults to using xlsxwriter. You need explicitly to set the engine to openpyxl. – Charlie Clark Mar 28 '18 at 13:09

4 Answers4

5

You can use the append_df_to_excel() helper function, which is defined in this answer:

Usage:

append_df_to_excel('test.xlsx', df, sheet_name="Sheet3", startcol=0, startrow=20)

Some details:

**to_excel_kwargs - used in order to pass additional named parameters to df.to_excel() like i did in the example above - parameter startcol is unknown to append_df_to_excel() so it will be treated as a part of **to_excel_kwargs parameter (dictionary).

writer.sheets = {ws.title:ws for ws in writer.book.worksheets} is used in order to copy existing sheets to writer openpyxl object. I can't explain why it's not done automatically when reading writer = pd.ExcelWriter(filename, engine='openpyxl') - you should ask authors of openpyxl module about that...

MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
  • Hey, thanks a lot, this works. Two big differences I am seeing here in your code: 1. df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs) will you plz explain little about this parameter **to_excel_kwargs - I was not using this ? 2. writer = pd.ExcelWriter(filename, engine='openpyxl') use of openpyxl as an engine . Also if you can explain this line too - that will be big help - writer.sheets = dict( (ws.title, ws) for ws in writer.book.worksheets) – singularity2047 Mar 27 '18 at 20:20
3

You can use openpyxl as the engine when you are creating an instance of pd.ExcelWriter.

import pandas as pd
import openpyxl

df1 = pd.DataFrame({'A':[1, 2, -3],'B':[1,2,6]})
book = openpyxl.load_workbook('examples/ex1.xlsx') #Already existing workbook
writer = pd.ExcelWriter('examples/ex1.xlsx', engine='openpyxl') #Using openpyxl

#Migrating the already existing worksheets to writer
writer.book = book
writer.sheets = {x.title: x for x in book.worksheets}

df1.to_excel(writer, sheet_name='sheet4')
writer.save()

Hope this works for you.

iDrwish
  • 3,085
  • 1
  • 15
  • 24
  • Hi, Thanks for your help. I tried this code. Instead of adding to 'Sheet3' it creates 'Sheet31' and puts the dataframe there - 'df1.to_excel(writer, sheet_name='sheet3', startcol=0, startrow=20, index = 0)' . But I want it to write within Sheet3 not create a new one. – singularity2047 Mar 27 '18 at 20:01
  • Doesn't work with latest pandas and openpyxl – Pavneet Singh Sep 19 '22 at 19:41
0

openpyxl has support for Pandas dataframes so you're best off using it directly. See http://openpyxl.readthedocs.io/en/latest/pandas.html for more details.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
0

Based on https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html

This did work for me (pandas version 1.3.5)

import pandas as pd
df1 = pd.DataFrame({'a':[0,1,2], 'b':[1,2,3],'c':[2,3,4]})
df2 = pd.DataFrame({'aa':[10,11,12], 'bb':[11,12,13],'cc':[12,13,14]})

with pd.ExcelWriter('test.xlsx') as writer:
    for i, df in enumerate([df1, df2]):
        df.to_excel(writer,sheet_name=f'sheet_{i}', index=False)
Markus Kaukonen
  • 334
  • 4
  • 10