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?
如何覆盖这种行为以保存公式和图表链接?
5 个解决方案
#1
4
Openpyxl 1.7 contains several improvements for handling formulae so that they are preserved when reading. Use guess_types=False to prevent openpyxl from trying to guess the type for a cell and 1.8 includes the data_only=True option 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系列。
#2
3
In excel:
在excel中:
Home --> Find & Select --> Replace
Replace All: "=" with "spam"
In python:
在python中:
Run python script to update excel sheets
In excel:
在excel中:
Replace All: "spam" with "="
#3
3
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。工作簿对象。我已经成功地使用该模块创建了新的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 question to 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应对这个问题讨论的记号赋予器解析公式,这是最难的部分置换他们(当然你要做的,如果你想推断出共享的公式将会是什么样子在另一宿主细胞)。
#4
1
The formula issue has been resolved here
公式问题已经解决了。
run this to get the latest version
运行此命令获取最新版本
hg clone https://bitbucket.org/ericgazoni/openpyxl
cd openpyxl/
hg up 1.8
python setup.py develop
#5
0
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()
#1
4
Openpyxl 1.7 contains several improvements for handling formulae so that they are preserved when reading. Use guess_types=False to prevent openpyxl from trying to guess the type for a cell and 1.8 includes the data_only=True option 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系列。
#2
3
In excel:
在excel中:
Home --> Find & Select --> Replace
Replace All: "=" with "spam"
In python:
在python中:
Run python script to update excel sheets
In excel:
在excel中:
Replace All: "spam" with "="
#3
3
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。工作簿对象。我已经成功地使用该模块创建了新的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 question to 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应对这个问题讨论的记号赋予器解析公式,这是最难的部分置换他们(当然你要做的,如果你想推断出共享的公式将会是什么样子在另一宿主细胞)。
#4
1
The formula issue has been resolved here
公式问题已经解决了。
run this to get the latest version
运行此命令获取最新版本
hg clone https://bitbucket.org/ericgazoni/openpyxl
cd openpyxl/
hg up 1.8
python setup.py develop
#5
0
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()