覆盖原有 sheet 数据并更新与之相关的 sheet 数据

from openpyxl import load_workbook
import time
import pandas as pd
from win32com.client import Dispatch

#覆盖原 sheet 数据
file_1 = ‘D:/deal_1.xls’
file_2 = ‘D:/deal_2.xls’
df_1 = pd.read_excel(file_1, header = None, index = None)
df_2 = pd.read_excel(file_2, header = None, index = None)

#保存新的数据
book = load_workbook(‘D:/testxlsx’)
writer = pd.ExcelWriter(‘D:/test.xlsx’,engine=‘openpyxl’)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df_1.to_excel(writer, “sheet1”, header = None, index = None)
df_2.to_excel(writer, “sheet2”, header = None, index = None)
writer.save()
print(“加入成功”)
time.sleep(60)

#与两表数据相关 sheet 的更新
xlApp = Dispatch(“Excel.Application”)
xlApp.Visible = False
filename = “D:/test.xlsx”
xlBook = xlApp.Workbooks.Open(filename)
xlBook.Save()
xlBook.Close()
print(“更新成功”)