python to excel append in rows

Question

I have written a program and I am trying to export the results in excel. I have this inside the function (although I kinda think now that it should be outside the main function).

 minas=minas_ex.get()


data = pd.DataFrame(data =all_together)
data=data.transpose()
##book= load_workbook('FromPython.xlsx')
writer= pd.ExcelWriter('FromPython.xlsx',engine='openpyxl')
##writer.book = book
writer.sheets={ws.title: ws for ws in book.worksheets}
data.to_excel(writer,sheet_name=month,startrow=0,startcol=0,header=minas, index = False)

writer.save()

It seems to be overriding the results. I am trying to print the results (all_together) to the month-sheet that is selected earlier. But I want them to append not override. In a previous version I managed to append the results but it seems that the next saved result didn't append to the next row it just continued to write on transpose for example:

I want it to show:

item | price | tax | etc
something| 123| 132| etc
sth_else| 132| 231| etc

I forgot, I want it to select the worksheet to append (which is given via a button earlier in the program.)

Try writer= pd.ExcelWriter('FromPython.xlsx',engine='openpyxl', mode='a') — user5386938
– user5386938, Commented Jun 9, 2020 at 16:32
I did try that before but it didn't work. It still overrides the previous results — JohnnyD
– JohnnyD, Commented Jun 9, 2020 at 16:35

user5386938 · Accepted Answer · 2020-06-09 16:59:49Z

2

Apparently, it's not as simple as I thought. Stolen from https://medium.com/better-programming/using-python-pandas-with-excel-d5082102ca27#9cd6

import pandas as pd
from openpyxl import load_workbook
# new dataframe with same columns
df = pd.DataFrame({'Name': ['E','F','G','H'],
                   'Age': [100,70,40,60]})
writer = pd.ExcelWriter('demo.xlsx', engine='openpyxl')
# try to open an existing workbook
writer.book = load_workbook('demo.xlsx')
# copy existing sheets
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
# read existing file
reader = pd.read_excel(r'demo.xlsx')
# write out the new sheet
df.to_excel(writer,index=False,header=False,startrow=len(reader)+1)

writer.close()

answered Jun 9, 2020 at 16:59

user5386938

Sign up to request clarification or add additional context in comments.

4 Comments

JohnnyD Over a year ago

Great mate this works like a charm, the only issue is that it does show the index on the columns

user5386938 Over a year ago

What do you mean by "show the index on the columns"

JohnnyD Over a year ago

It works, I am not sure how I missed it, the only thing remaining is that it's adding index on the columns do it's actually 0|1|2|3 '\n' sth|12|123|32 '\n' 0|1|2|3 '\n' sth|43|23|423 '\n' sorry for the late reply

JohnnyD Over a year ago

Also it seems that when it's changing sheets the results go to other rows below for some reason (edit ok I fixed this one, I just needed to add the ,sheet_name=month to the reader.)

Collectives™ on Stack Overflow

python to excel append in rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related