CODEWITHSUNDEEP
pythonpandas.excelwriter
JohnnyD
JohnnyD

Reputation: 403

python to excel append in rows

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.)

Upvotes: 0

Views: 1005

Answers (1)

user5386938
user5386938

Reputation:

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()

Upvotes: 2

Related Questions