pandas 如何在不破坏 openpyxl 公式的情况下写入现有的 excel 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20262448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write to an existing excel file without breaking formulas with openpyxl?
提问by BP_
When you write to an excel file from Python in the following manner:
当您以下列方式从 Python 写入 excel 文件时：
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?
如何覆盖此行为以保留公式和图表链接？
回答by Charlie Clark
Openpyxl 1.7 contains several improvements for handling formulae so that they are preserved when reading. Use guess_types=Falseto prevent openpyxl from trying to guess the type for a cell and 1.8 includes the data_only=Trueoption if you want the values but not the formula.
Openpyxl 1.7 包含一些处理公式的改进，以便在阅读时保留它们。使用guess_types=False防止openpyxl从试图猜测类型的细胞和1.8包括的data_only=True选择，如果你想要的值而不是公式。
Want to preserve charts in the 2.x series.
想要保留 2.x 系列中的图表。
回答by HaPsantran
Here I address the "preserve the formulas" part of the question only.
在这里，我仅解决问题的“保留公式”部分。
I tried using openpyxl 1.8, which did successfully read the formulas, but when I tried to save a copy it broke. (The breakage appeared to be related to the styles, not the formulas.)
我尝试使用 openpyxl 1.8，它确实成功读取了公式，但是当我尝试保存副本时它坏了。（破损似乎与款式有关，与配方无关。）
In any event, what I recommend (until openpxyl comes a tad further) is to map the formulas to a new xlsxwriter.Workbook object. I've had success using that module to create new xlsx workbooks (with formatting and formulas), and without knowing how well the formats will translate from the openpyxl object to the xlsxwriter one, I believe it will be a viable solution for preserving at least the formulas.
无论如何，我建议（直到 openpxyl 更进一步）是将公式映射到新的 xlsxwriter.Workbook 对象。我已经成功地使用该模块创建了新的 xlsx 工作簿（带有格式和公式），并且不知道这些格式从 openpyxl 对象转换为 xlsxwriter 对象的效果如何，我相信这将是一个可行的解决方案，至少可以保留公式。
Now, doing this (which I wanted to and did myself) is NOT super simple because of shared formulas. I had to write a tool that 'de-shares' these shared formulas, transposes them, and applies them to each cell that refers to it.
现在，由于共享公式，这样做（我想做并且自己做了）并不是非常简单。我必须编写一个工具来“取消共享”这些共享公式，将它们转置，并将它们应用于引用它的每个单元格。
One might first think that this approach creates inefficiencies by adding a bunch of formulas where previously there were just references to an existing formula. However, I tried writing these 'redundant' formulas with xlsxwriter and then reading that sheet back in with openpyxl again. I discovered that the formulas again were read in as shared, so either xlsxwriter or the Excel application itself is doing this optimization. (One could easily figure out which, of course; I just haven't yet.)
人们可能首先认为，这种方法通过添加一堆公式而导致效率低下，而以前只是对现有公式的引用。但是，我尝试使用 xlsxwriter 编写这些“冗余”公式，然后再次使用 openpyxl 重新读取该表格。我发现公式再次被读取为共享，因此 xlsxwriter 或 Excel 应用程序本身正在执行此优化。（当然，人们可以很容易地找出哪个；我只是还没有。）
I'd be happy to post my solution for desharing and transposing if it would be helpful iff there's demand; currently it's integrated into a larger module and I'd have to create a standalone version. Generally speaking though, I used the shunting yard tool in the tokenizer discussed in ecatmur's response to this questionto parse the formula, which is the hardest part of transposing them (which of course you have to do if you want to infer what the shared formula will look like in another 'host cell').
如果有需求，我很乐意发布我的解共享和转置解决方案；目前它已集成到一个更大的模块中，我必须创建一个独立版本。不过一般来说，我在 ecatmur 对这个问题的回答中讨论的分词器中使用了分流器工具来解析公式，这是转置它们的最难的部分（当然，如果你想推断共享公式是什么，你必须这样做将看起来像在另一个“宿主细胞”中）。
回答by JimJokester
In excel:
在 Excel 中：
Home --> Find & Select --> Replace
Replace All: "=" with "spam"
In python:
在蟒蛇中：
Run python script to update excel sheets
In excel:
在 Excel 中：
Replace All: "spam" with "="
回答by flyingmeatball
I know this is an older thread, but it took me a while to find a solution - xlwings allows you to write to one tab and retain charts on another.
我知道这是一个较旧的线程，但我花了一段时间才找到解决方案 - xlwings 允许您写入一个选项卡并在另一个选项卡上保留图表。
The follow example opens an existing workbook, updates the data a chart is based on, and saves as a new version.
以下示例打开现有工作簿，更新图表所基于的数据，并另存为新版本。
import xlwings as xw
import pandas as pd
#create DF
months = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
value1 = [x * 5+5 for x in range(len(months))]
df = pd.DataFrame(value1, index = months, columns = ['value1'])
df['value2'] = df['value1']+5
df['value3'] = df['value2']+5
#load workbook that has a chart in it
wb = xw.Book('C:\data\bookwithChart.xlsx')
ws = wb.sheets['chartData']
ws.range('A1').options(index=False).value = df
wb = xw.Book('C:\data\bookwithChart_updated.xlsx')
xw.apps[0].quit()
