Edit excel file with python

Question

I tried to edit an existing excel file. My file is test.xlsx, with two sheets are All and Summary. Following step:

 import pandas as pd

 df = pd.read_csv('abc.csv') 
 number_rows = len(df.index) 
 writer = pd.ExcelWriter('test.xlsx') 
 df.to_excel(writer, sheet_name = 'All',startrow = number_rows) 
 writer.save()

I want to edit(append data to sheet name All) but when run this code, it seem to be the sheet name Summary and All deleted and it create a new sheet name All and write my new data to it. So, how to append data to excel sheet without delete existing data? Thank you.

You need to read your excel data in first. All you're doing is blowing away your existing excel file with an entirely new one. This is covered by the panda docs of these calls. — pvg
– pvg, Commented Mar 29, 2017 at 2:19
@pvg the same result if i am not read dataframe from csv, read dataframe from excel file itself ` df = pd.read_excel('test.xlsx')` replace line 2 — AlphaWolf
– AlphaWolf, Commented Mar 29, 2017 at 2:36
You should read the docs and/or google for some examples of this, it's not going to work if you randomly change the code in the hopes it magically works. — pvg
– pvg, Commented Mar 29, 2017 at 2:38

Anil_M · Accepted Answer · 2017-03-29 02:57:26Z

2

You can use openpyxl engine along-with startrow parameter. You also need to ;

read csv to df first
open xlsx as workbook using openpyxl
create writer object using openpyxl as engine
Add sheets to writer object
Add df to writer object

Your Code (modified):

import pandas as pd
from openpyxl import load_workbook

df = pd.read_csv('abc.csv')
number_rows = len(df.index) 
book = load_workbook('test.xlsx')
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name = 'All',startrow = number_rows) 
writer.save()

answered Mar 29, 2017 at 2:57

Anil_M

11.5k6 gold badges56 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Edit excel file with python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related