如何写入一个现有的excel文件，而不用openpyxl打破公式？ [英] How to write to an existing excel file without breaking formulas with openpyxl?

问题描述

 从openpyxl导入导入pandas 
当您从Python编写Excel文件时， load_workbook 
 
 book = load_workbook（'Masterfile.xlsx'）
 writer = pandas.ExcelWriter（'Masterfile.xlsx'）
 writer.book = book 
 writer。 
 
 data_filtered.to_excel（作者，Main，cols = ['Diff1'，'Diff2']）$ b（wstitle，ws） 
 $ b writer.save（）
  

现有的图表的公式和链接

如何覆盖此行为以保留公式和链接到图表？

解决方案

在excel中：

 首页 - >查找&选择 - >替换
 
替换全部：=与垃圾邮件
  

在python ：

 运行python脚本来更新excel表
  

在excel中：

 替换全部：spamwith=
  

When you write to an excel file from Python in the following manner:

import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

Formulas and links to charts which are in the existing sheets, will be saved as values.

How to overwrite this behaviour in order to preserve formulas and links to charts?

解决方案

In excel:

  Home --> Find & Select --> Replace

  Replace All: "=" with "spam"

In python:

  Run python script to update excel sheets

In excel:

  Replace All: "spam" with "="

这篇关于如何写入一个现有的excel文件，而不用openpyxl打破公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！