writing pandas data frame to existing workbook

Question

I want to save a data frame to the second sheet of a file but I can't do that and I don't know why.

yfile = openpyxl.load_workbook(new_file, data_only=True) 
ws = yfile.worksheets[0] 
sheet2 = yfile.create_sheet() 
ws2 = yfile.get_sheet_by_name("Sheet").title = "Analysis" 
writer = pd.ExcelWriter(yfile, engine='xlsxwriter') 
df3.to_excel(writer, sheet_name='Analysis') 
writer.save()
yfile.save(new_file)

I have created the sheet 'Analysis' but when I save in it I received the following response: "AttributeError: 'Workbook' object has no attribute 'write'"

What I have to modify?

One issue is that you are creating `yfile` as an `openpyxl` objectand then passing it to `pd.ExcelWriter` but telling it that it should use the `xlsxwriter` engine. These two objects are incompatible so that isn't going to work. However, I don't know if it will work even with an openpyxl object and engine created in this way. — jmcnamara, Apr 06 '16 at 10:36

MaxU - stand with Ukraine · Answer 1 · 2021-09-12T14:32:50.563

7

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

Demo:

In [127]: df = pd.DataFrame(np.random.rand(5, 3), columns=list('abc'))

In [128]: df
Out[128]:
          a         b         c
0  0.597353  0.770586  0.671231
1  0.628605  0.161283  0.397493
2  0.476206  0.701582  0.476809
3  0.045590  0.302834  0.857033
4  0.414820  0.478016  0.563258

In [129]: df.to_excel(filename)

In [130]: append_df_to_excel(filename, df, sheet_name="Sheet2", startrow=1, startcol=1)

In [131]: append_df_to_excel(filename, df, sheet_name="Sheet3", index=False)

In [132]: append_df_to_excel(filename, df, sheet_name="Sheet1", startcol=2, index=False)

Result:

Sheet1:

Sheet2:

Sheet3:

PS tested using the following versions:

python: 3.6.4
pandas: 0.22.0
openpyxl: 2.4.10

edited Sep 12 '21 at 14:32

answered Apr 06 '16 at 11:55

MaxU - stand with Ukraine

205,989
36
386
419

oh man, I was just replicating your code thinking you finally helped me resolve this issue but when I ran the thing, it creates new sheets adding a 1 at the end of the name instead of replacing the old ones with new data, what can I do to fix that? – bernando_vialli Mar 22 '18 at 14:22
@mkheifetz, do you want to replace existing sheet or add data to the end of it? – MaxU - stand with Ukraine Mar 22 '18 at 15:46
looks like I figured it out already based on another post, thank you regardless – bernando_vialli Mar 22 '18 at 16:33
Has anyone fixed The helper to replace Sheet_1 but no other sheet? Thanks – Tinkinc Mar 26 '18 at 17:38
@Tinkinc, i'm not sure i understand you... What exactly do you mean? – MaxU - stand with Ukraine Mar 26 '18 at 17:40
The helper inserts the df into sheet 1. I am looking to just replace sheet 1 not continuously append it. While preserving the actual excel file/tabs if they exist. thanks – Tinkinc Mar 26 '18 at 17:42
@Tinkinc, did you try to specify `startrow=0`? – MaxU - stand with Ukraine Mar 26 '18 at 17:44
@MaxU. Yes, but it seems it just slides down the data I am trying to reprocess-all from that tab... thoughts? and thank you for the help – Tinkinc Mar 26 '18 at 17:46
@Tinkinc, i've slightly modified the helper function, so it will start writing from the first line,.... BUT it will not delete data from existing cells - it'll simply overwrite first N lines where `N` - is the # of rows in your DataFrame – MaxU - stand with Ukraine Mar 26 '18 at 18:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167581/discussion-between-tinkinc-and-maxu). – Tinkinc Mar 26 '18 at 18:45
1

@Tinkinc, i've added `truncate_sheet` option ;-) – MaxU - stand with Ukraine Mar 27 '18 at 21:59
Well written method. Just one more thing. As i am trying to insert a plot created by matplotlib, can you please tell me how do I insert an image in the excel file? – Aashish Gulabani Aug 03 '18 at 15:49

score 3 · Answer 2 · answered Jun 04 '18 at 07:17

3

You can use such way as below. It writes dataframe to an excel-file row by row.

from openpyxl.utils.dataframe import dataframe_to_rows
filename = r'some_file.xlsx'
df = some_dataframe

wb = load_workbook(filename)
if 'sh2' in wb.sheetnames:#to check whether sheet you need already exists
    ws = wb['sh2']
else:
    ws = wb.active
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
wb.save(filename)

answered Jun 04 '18 at 07:17

Sergey Solod

695
7
15

I've searched all internet and only this solution worked! Other ones worked in Jupyter but not in Shell for me (corrupting excel file) and this one does. Thanks! – Marta Jan 29 '21 at 14:34

writing pandas data frame to existing workbook

2 Answers2

Linked