2

I have an excel file of 10 sheets named like A1, A2, ... A10. I would like to replace the contents of sheet A2 by a new pandas dataframe. I dont find any function for such a transformation.

Is there any workaround available for this?

eiram_mahera
  • 950
  • 9
  • 25
  • pandas ExcelFile and ExcelWriter provides all you need – luigigi Nov 01 '19 at 10:17
  • Does this answer your question? [Can Pandas read and modify a single Excel file worksheet (tab) without modifying the rest of the file?](https://stackoverflow.com/questions/28142420/can-pandas-read-and-modify-a-single-excel-file-worksheet-tab-without-modifying) – Henry James Nov 01 '19 at 10:23

1 Answers1

2
import pandas
from openpyxl import load_workbook

df = pandas.DataFrame() # your dataframe

book = load_workbook('your_excel')
writer = pandas.ExcelWriter('your_excel', engine='openpyxl')
writer.book = book

idx=book.sheetnames.index('A2')
book.remove(book.worksheets[idx])
book.create_sheet('A2',idx)

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "A2",index=0,startrow=0,startcol=0)
writer.save()

Try this code

Kaibo
  • 111
  • 1
  • 4
  • your code doesn't replace the entire sheet contents. For example, if the sheet has 100 rows and 20 columns and new dataframe has 30 rows and 5 columns, only 30*5 data is replaced, rest of the cells have old data! – eiram_mahera Nov 04 '19 at 04:17
  • I have updated my answer, the code removes the old sheet and create a new one with your dataframe. – Kaibo Nov 05 '19 at 08:28
  • Ah. The sheet is named `"A2"` – that is a terrible name for a sheet, imo – Yaakov Bressler Nov 05 '19 at 08:29