CODEWITHSUNDEEP
pythonexceldataframe
ahqian123
ahqian123

Reputation: 1

original sheet was removed when new data table writed to excel file

my propose:

  1. if excel file not exist, create it and copy data table to it;
  2. if excel file exist, copy to data table to new sheet. but following code running, only copy to data to new sheet, original sheet in excel file was removed.

import os import pandas as pd import openpyxl

f_name = "123.xlsx" #target excel file

if os.path.exists(f_name): """if excel file exist, added table to another sheet""" wb = openpyxl.load_workbook(f_name) #load excel file writer = pd.ExcelWriter(f_name, engine="openpyxl")

writer.wb = wb                                          
df = pd.DataFrame(pd.read_excel("table_2.xlsx"))      #get table to be added excel file
df.to_excel(writer, sheet_name="sheet2",index=False)  #write to another sheet
writer.save()
writer.close()

else: """if excel file not exit, create it""" df_1 = pd.DataFrame() # create excel file df_1.to_excel(f_name)

writer = pd.ExcelWriter(f_name)       
df_2 = pd.DataFrame(pd.read_excel("table_1.xlsx"))      # get table_1  
df_2.to_excel(writer, sheet_name="sheet1",index=False)  # write table_1 into excel file  
writer.save()
writer.close()

Upvotes: 0

Views: 97

Answers (1)

ahqian123
ahqian123

Reputation: 1

import os import pandas as pd import openpyxl

f_name = "123.xlsx" #target excel file

if os.path.exists(f_name):
"""if excel file exist, added table to another sheet""" wb = openpyxl.load_workbook(f_name) #load excel file writer = pd.ExcelWriter(f_name, engine="openpyxl") #assign engine writer.book = wb #overwrite if no this

df = pd.DataFrame(pd.read_excel("table_2.xlsx"))         #get table to be added excel file
df.to_excel(writer, sheet_name="table_2",index=False)    #write to another sheet
writer.save()
writer.close()

else:
"""if excel file not exit, create it""" df_1 = pd.DataFrame() # create excel file df_1.to_excel(f_name)

writer = pd.ExcelWriter(f_name)       
df_2 = pd.DataFrame(pd.read_excel("table_1.xlsx"))      # get table_1  
df_2.to_excel(writer, sheet_name="table_1",index=False)  # write table_1 into excel file  
writer.save()
writer.close()

Upvotes: 0

Related Questions