如何在保留所有其他工作表的同时覆盖现有Excel工作表上的数据？ [英] How to overwrite data on an existing excel sheet while preserving all other sheets?

问题描述

我有一个熊猫数据框 df ，我想在保存时将其覆盖到excel文件的表 Data 所有其他工作表，因为其他工作表具有链接到工作表 Data

I have a pandas dataframe df which I want to overwrite to a sheet Data of an excel file while preserving all the other sheets since other sheets have formulas linked to sheet Data

的公式，但我没有使用以下代码覆盖现有工作表，它只是创建一个名称为 Data 1

I used the following code but it does not overwrite an existing sheet, it just creates a new sheet with the name Data 1

with pd.ExcelWriter(filename, engine="openpyxl", mode="a") as writer:
     df.to_excel(writer, sheet_name="Data")

是否可以覆盖现有工作表？

Is there a way to overwrite on an existing sheet?

推荐答案

您可以使用 openpyxl ：

import pandas as pd
from openpyxl import load_workbook

book = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl') 
writer.book = book

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "Data")

writer.save()

您需要初始化 writer .sheets ，以便让 ExcelWriter 知道工作表。否则，它将使用您传递的名称创建一个新工作表。

You need to initialize writer.sheets in order to let ExcelWriter know about the sheets. Otherwise, it will create a new sheet with the name that you pass.

这篇关于如何在保留所有其他工作表的同时覆盖现有Excel工作表上的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！