使用pandas的 df.to_excel(file_path)想要对一个有多张sheet的workbook操作时会清空其他sheet数据

1、使用pandas

import pandas as pd
import openpyxl 
# import xlwings as xw
book = openpyxl.load_workbook(path)   #读取你要写入的workbook
#和pd.read_excel() 用于将Dataframe写入excel。xls用xlwt。xlsx用openpyxl
writer = pd.ExcelWriter(path, engine='openpyxl')   
##此时的writer里还只是读写器. 然后将上面读取的book复制给writer  
writer.book = book
#转化为字典的形式，sheet名和sheet对象组成的键值对
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
#将data写入writer
groupby_ddff_top1.to_excel(writer,sheet_name="再写一次",index=False)
writer.save()

或者是

def write_sheet(path,df):
    df = pd.read_excel(r"新创建的表格.xls")
    excelWriter=pd.ExcelWriter(r"新创建的表格.xls",engine='openpyxl',mode='a')#这里指定engine和mode很关键
    df.to_excel(excel_writer=excelWriter,sheet_name='test3')
    df.to_excel(excel_writer=excelWriter,sheet_name='test4')
    excelWriter.save()
    excelWriter.close()

2.使用xlwings、openpyxl等包

先用pandas读取dataframe，然后将df写入使用xlwings新建的sheet中

import xlwings as xw

#读取df
df = pd.read_excel(path)
wb = xw.Book(path)
#在wb中新建一张新的sheet.可以指定位置
sht = wb.sheets.add(name="写第二次",before=None,after=None)
#df.values 不然会插入df的索引
sht.range("A1").value = df.values
wb.save()
wb.close()