Python - openpyxl 在使用公式写入现有 xlsx 后读取 xlsx 数据 [英] Python - openpyxl read xlsx data after writing on existing xlsx with formula
问题描述
在使用 openpyxl 写入现有的 xlsx excel 文件后,我正在尝试读取 xlsx 文件.
I am trying to read a xlsx file after writing on the existing xlsx excel file using openpyxl.
我的 Excel 文件 file1.xlsx
在 A1
上具有值 1
,在 A2 上具有值
2
和 A3
上的值 A1 + A2
,此时是 3
.
My Excel file file1.xlsx
having value 1
on A1
, value 2
on A2
and value A1 + A2
on A3
, which is 3
at this point.
def updateFile(a):
wb = load_workbook('file1.xlsx')
ws = wb.active
#Update specific column
ws['A1'] = a
wb.save('file1.xlsx')
def readFile():
wb = load_workbook('file1.xlsx')
sheet = wb['Sheet1']
print(sheet['A3'].value)
我的程序将更新 file1.xlsx
上的 A1
并读取 A3
上的数据.例如,调用 updateFile(5)
会将 A1
更新为 5
,并可能在 上给我
.7
>A3
My program is going to update A1
on file1.xlsx
and read the data on A3
. For example, calling updateFile(5)
will update A1
to 5
, and perhaps giving me 7
on A3
.
不幸的是,在调用 updateFile(5)
之后,readFile()
将给出 = A1 + A2
作为输出,而不是 7
.
Unfortunately, after updateFile(5)
is called, readFile()
will gives = A1 + A2
as output, instead of 7
.
这主要是因为 Excel 文件中的数据已更新但未保存.如果我想让 readFile()
输出 7
,我必须手动打开 file1.xlsx
,保存并关闭它.
This is mainly because the data on Excel file is updated but not saved. And if I want to let readFile()
to output 7
, I have to open file1.xlsx
manually, Save it, and Close it.
无论如何还是我在 openpyxl 上滥用读/写来解决这个问题?我相信我没有正确保存文件,或者我必须想办法以编程方式打开、保存、关闭 Excel 文件.
Are there anyway or am I misusing read/write on openpyxl to resolve this issues? I believe I had not Save the file properly or I have to figure a way to open, save, close an Excel file programmatically.
推荐答案
您所看到的是预期行为.当单元格有公式时,Excel 会将公式的结果保存为缓存值.openpyxl 从不评估公式,因此从不维护缓存并使任何现有缓存无效.相反,如果您想要公式的结果,则可以使用 data_only=True
参数加载文件.虽然这会用值替换公式.
What you are seeing is expected behaviour. When cells have formulae then Excel saves the result of the formula as a cached value. openpyxl never evaluates formulae so never maintains a cache and invalidates any existing cache. Instead, if you want the results of a formula then you can load the file with the data_only=True
parameter. Though this will replace the formula with the value.
文档中对此进行了介绍:http:///openpyxl.readthedocs.org/en/stable/usage.html#read-an-existing-workbook
This is covered in the documentation: http://openpyxl.readthedocs.org/en/stable/usage.html#read-an-existing-workbook
这篇关于Python - openpyxl 在使用公式写入现有 xlsx 后读取 xlsx 数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!