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

Question

我有一个模板文件Template.xlsx。它有 2 张：“每月保费”和“每月损失”

然后我创建数据框 df。我怎样才能简单地将 df 保存到“每月保费”表中？

我认为这样的东西会起作用，但它会创建一个新的工作表名称。

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'C:\My\Path\Template1.xlsx'

book = load_workbook(path)

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

writer.book = book

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

writer.save()

writer.close()

Answer 1

要将数据框保存到现有工作表，您必须在 Excel 编写器中设置工作表集合。试试这个代码：###### create test file ######from openpyxl import load_workbook, Workbookwb = Workbook()ws = wb.activews.title='Monthly Premium'  # rename default sheetws = wb.create_sheet('Monthly Losses')  # add sheetwb.save(r'Template1.xlsx')   ######### main script #########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'Template1.xlsx'book = load_workbook(path)writer = pd.ExcelWriter(path, engine='openpyxl')writer.book = bookwriter.sheets = dict((ws.title, ws) for ws in book.worksheets)  # prevent new sheetdf.to_excel(writer, sheet_name='Monthly Premium')writer.save()writer.close()输出