如何将数据加载到Excel模板到特定的工作表名称

Question

我有一个模板文件Template.xlsx。它有 2 张：“每月保费”和“每月损失”然后我创建数据框 df。我怎样才能简单地将 df 保存到“每月保费”表中？我认为这样的东西会起作用，但它会创建一个新的工作表名称。import pandas as pdfrom openpyxl import load_workbook#sample dfdf = pd.DataFrame()df["A"] = [1,2,3,4,5]df["B"] = ["A", "B", "C", "D", "E"]#print(df)path = r'C:\My\Path\Template1.xlsx'book = load_workbook(path)writer = pd.ExcelWriter(path, engine='openpyxl')writer.book = bookdf.to_excel(writer, sheet_name='Monthly Premium')writer.save()writer.close()

Answer 1

要将数据框保存到现有工作表，您必须在 Excel 编写器中设置工作表集合。

试试这个代码：

###### create test file ######

from openpyxl import load_workbook, Workbook

wb = Workbook()

ws = wb.active

ws.title='Monthly Premium'  # rename default sheet

ws = wb.create_sheet('Monthly Losses')  # add sheet

wb.save(r'Template1.xlsx') 

  

######### main script #########

import pandas as pd

from openpyxl import load_workbook

#sample df

df = pd.DataFrame()

df["A"] = [1,2,3,4,5]

df["B"] = ["A", "B", "C", "D", "E"]

#print(df)

path = r'Template1.xlsx'

book = load_workbook(path)

writer = pd.ExcelWriter(path, engine='openpyxl')

writer.book = book

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # prevent new sheet

df.to_excel(writer, sheet_name='Monthly Premium')

writer.save()

writer.close()

输出