import pandas as pd
import numpy as np
from openpyxl import load_workbook

book = load_workbook(r'C:\Users\zhimin.liu\Desktop\数据存储test.xlsx')
try:
    ws = book["insert_test"]         
    book.remove(ws)         #删除需要替换的sheet
except:
    print('无需删除的sheet')
writer = pd.ExcelWriter(r'C:\Users\zhimin.liu\Desktop\数据存储test.xlsx', engine='openpyxl')
writer.book = book
data3 = pd.DataFrame(np.random.randn(10, 5))
data3.to_excel(writer, sheet_name='insert_test', index = False)
# writer.save()         --save和closey一起写就会提示文件损坏修复，原因是重复保存
writer.close()   

坑点一：
book = load_workbook
writer = pd.ExcelWriter
两个语句顺序不能错，如果ExcelWriter写在了上面，则会报错“File is not a zip file”
坑点二：
writer.save()
writer.close()
两个不能同时写，其他写法，先“保存”后“关闭”，如果同时写，则excel文件将会报错：文件损坏，是否选择修复。