0

Sometimes we open multi sheet excel file, do some operations in one sheet and then save it back in the same file or make a new file while saving. Given the operations are done in pandas dataframe, how can I copy back the result to the target sheet?

import openpyxl as op
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

wbk=op.load_workbook("fileName.xlsx")
wsht=wbk['verbList']

#create dataframe with sheet data and operate
df = pd.read_excel("fileName.xlsx", sheet_name="verbList")
df.insert(0,"newCol2","")  #sample operation

dataframe_to_rows(df, index=False, header=True) #dataframe converted to rows

#for loop from dataframe_to_rows moves back rows to excel file
#trying to avoid loops here

wsht["B1"].value="verbs"

wbk.save(basePath + "fileName-update.xlsx")

Any idea anyone? If any other python excel library does the job, please let know.

user3471438
  • 333
  • 1
  • 2
  • 12
  • Could you please check this and know if it helped [python: update dataframe to existing excel sheet without overwriting contents on the same sheet and other sheets](https://stackoverflow.com/questions/39049148/python-update-dataframe-to-existing-excel-sheet-without-overwriting-contents-on) – Manjunath K Mayya Feb 27 '22 at 04:48
  • What is the end result of operations done in pandas? Are you trying to add a new column or row or modify the existing data? Most of these can be handled using openpyxl – Destiny Feb 27 '22 at 06:03
  • @Destiny -- I am trying to do all basic excel operations on multisheet file - insert/delete column/row, using formula to manipulate data, clear data from entire sheet and so on. I am trying to avoid loops to do these things. From what I understand, openpyxl works directly on excel sheet and sometimes formula can take a long time to run. So I am trying to do such operation in dataframe and paste back the result and save the entire file with other sheets as well. – user3471438 Feb 27 '22 at 06:13
  • See if this helps - https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas – Destiny Feb 27 '22 at 06:37
  • Why are you trying to avoid loops? It is **impossible** to avoid loops when going data frames and Excel files? – Charlie Clark Feb 28 '22 at 11:21
  • Seems not Charlie. Destiny's answer here totally works. Although a new sheet is added for that. – user3471438 Mar 01 '22 at 12:03

0 Answers0