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屋！