Python 读取 Excel 单元格值而不是计算它的公式 -openpyxl

Read Excel cell value and not the formula computing it -openpyxl

提问by user3411047

I am using openpyxl to read cell value (excel addin-webservice update this column. )

我正在使用 openpyxl 读取单元格值（excel addin-webservice 更新此列。）

I have used data_only = Truebut it is not showing the current cell value instead it is the value stored the last time Excel read the sheet.

我已经使用过，data_only = True但它没有显示当前单元格值，而是上次 Excel 读取工作表时存储的值。

wbFile = openpyxl.load_workbook(filename = xxxx,data_only=True)
wsFile = wbFile[c_sSheet]

How can i read the cell actual value ?

如何读取单元格实际值？

回答by Charlie Clark

As @alex-martelli says, openpyxl does not evaluate formulae. When you open an Excel file with openpyxl you have the choice either to read the formulae or the last calculated value. If, as you indicate, the formula is dependent upon add-ins then the cached value can never be accurate. As add-ins outside the file specification they will never be supported. Instead you might want to look at something like xlwingswhich can interact with the Excel runtime.

正如@alex-martelli 所说，openpyxl 不评估公式。当您使用 openpyxl 打开 Excel 文件时，您可以选择读取公式或最后计算的值。如果，如您所指出的，公式依赖于加载项，则缓存值永远不会准确。作为文件规范之外的加载项，它们将永远不会得到支持。相反，您可能想要查看可以与 Excel 运行时交互的xlwings 之类的东西。

回答by Marcin Kajzler

wb = openpyxl.load_workbook(filename, data_only=True)

The data_onlyflag helps.

该data_only标志帮助。

回答by krvkir

Faced the same problem. Needed to read cell values whatever those cells are: scalars, formulae with precomputed values or formulae without them, with fail-tolerance preferred over correctness.

面临同样的问题。无论这些单元格是什么，都需要读取单元格值：标量、带有预计算值的公式或没有它们的公式，容错性优于正确性。

The strategy is pretty straightforward:

该策略非常简单：

1. if a cell doesn't contain formula, return cell's value;
2. if it's a formula, try to get its precomputed value;
3. if couldn't, try to evaluate it using pycel;
4. if failed (due to pycel's limited support of formulae or with some error), warn and return None.

1. 如果单元格不包含公式，则返回单元格的值；
2. 如果是公式，则尝试获取其预先计算的值；
3. 如果不能，请尝试使用pycel;
4. 如果失败（由于对pycel公式的支持有限或有一些错误），警告并返回 None。

I made a class which hides all this machinery and provides simple interface for reading cell values.

我创建了一个类，它隐藏了所有这些机制，并提供了用于读取单元格值的简单界面。

It's easy to modify the class so that it will raise an exception on step 4, if correctness is preferred over fail-tolerance.

如果正确性优于容错性，那么修改类很容易，这样它就会在第 4 步引发异常。

Hope it will help someone.

希望它会帮助某人。

from traceback import format_exc
from pathlib import Path
from openpyxl import load_workbook
from pycel.excelcompiler import ExcelCompiler
import logging


class MESSAGES:
    CANT_EVALUATE_CELL = ("Couldn't evaluate cell {address}."
                          " Try to load and save xlsx file.")


class XLSXReader:
    """
    Provides (almost) universal interface to read xlsx file cell values.

    For formulae, tries to get their precomputed values or, if none,
    to evaluate them.
    """

    # Interface.

    def __init__(self, path: Path):
        self.__path = path
        self.__book = load_workbook(self.__path, data_only=False)

    def get_cell_value(self, address: str, sheet: str = None):
        # If no sheet given, work with active one.
        if sheet is None:
            sheet = self.__book.active.title

        # If cell doesn't contain a formula, return cell value.
        if not self.__cell_contains_formula(address, sheet):
            return self.__get_as_is(address, sheet)

        # If cell contains formula:
        # If there's precomputed value of the cell, return it.
        precomputed_value = self.__get_precomputed(address, sheet)
        if precomputed_value is not None:
            return precomputed_value

        # If not, try to compute its value from the formula and return it.
        # If failed, report an error and return empty value.
        try:
            computed_value = self.__compute(address, sheet)
        except:
            logging.warning(MESSAGES.CANT_EVALUATE_CELL
                            .format(address=address))
            logging.debug(format_exc())
            return None
        return computed_value                

    # Private part.

    def __cell_contains_formula(self, address, sheet):
        cell = self.__book[sheet][address]
        return cell.data_type is cell.TYPE_FORMULA

    def __get_as_is(self, address, sheet):
        # Return cell value.
        return self.__book[sheet][address].value

    def __get_precomputed(self, address, sheet):
        # If the sheet is not loaded yet, load it.
        if not hasattr(self, '__book_with_precomputed_values'):
            self.__book_with_precomputed_values = load_workbook(
                self.__path, data_only=True)
        # Return precomputed value.
        return self.__book_with_precomputed_values[sheet][address].value

    def __compute(self, address, sheet):
        # If the computation engine is not created yet, create it.
        if not hasattr(self, '__formulae_calculator'):
            self.__formulae_calculator = ExcelCompiler(self.__path)
        # Compute cell value.
        computation_graph = self.__formulae_calculator.gen_graph(
            address, sheet=sheet)
        return computation_graph.evaluate(f"{sheet}!{address}")

回答by Nabla

As @Charlie Clark mentioned you could use xlwings(if you have MS Excel). Here an example

正如@Charlie Clark 提到的，您可以使用xlwings（如果您有 MS Excel）。这里有一个例子

say you have an excel sheet with formulas, for the example I define one with openpyxl

假设您有一个带有公式的 Excel 表，例如我定义了一个 openpyxl

from openpyxl import Workbook, load_workbook
wb=Workbook()

ws1=wb['Sheet']

ws1['A1']='a'
ws1['A2']='b'
ws1['A3']='c'

ws1['B1']=1
ws1['B2']=2
ws1['B3']='=B1+B2'

wb.save('to_erase.xlsx')

As mentioned, if we load the excel again with openpyxl, we will not get the evaluated formula

如前所述，如果我们再次加载 excel openpyxl，我们将不会得到评估公式

wb2 = load_workbook(filename='to_erase.xlsx',data_only=True)
wb2['Sheet']['B3'].value

you can use xlwingsto get the formula evaluated by excel:

您可以使用xlwings来获取由 excel 评估的公式：

import xlwings as xw
wbxl=xw.Book('to_erase.xlsx')
wbxl.sheets['Sheet'].range('B3').value

which returns 3, the expected value.

它返回 3，即预期值。

I found it quite useful when working with spreadsheets with very complicated formulas and references between sheets.

我发现它在处理具有非常复杂的公式和工作表之间的引用的电子表格时非常有用。

回答by Alexey Korolkov

I solved this problem by the following way:

我通过以下方式解决了这个问题：

import xlwings
from openpyxl import load_workbook

data = load_workbook('PATH_TO_YOUR_XLSX_FILE')
data['sheet_name']['A1'].value = 1
data.save('PATH_TO_YOUR_XLSX_FILE')

excel_app = xlwings.App(visible=False)
excel_book = excel_app.books.open('PATH_TO_YOUR_XLSX_FILE')
excel_book.save()
excel_book.close()
excel_app.quit()

data = load_workbook('PATH_TO_YOUR_XLSX_FILE', data_only=True)

I hope, this can help You...

我希望，这可以帮助你...

回答by bradbase

Xlcalculatorhas the ability to evaluate a cell.

Xlcalculator具有评估单元格的能力。

from xlcalculator import ModelCompiler
from xlcalculator import Model
from xlcalculator import Evaluator

filename = r'xxxx.xlsm'
compiler = ModelCompiler()
new_model = compiler.read_and_parse_archive(filename)
evaluator = Evaluator(new_model)
val1 = evaluator.evaluate('First!A2')
print("value 'evaluated' for First!A2:", val1)

The output is:

输出是：

value 'evaluated' for First!A2: 0.1

First!A2 的“评估”值：0.1