python解析excel公式_读取Excel单元格值，而不是计算它的公式-openpyx

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

这个策略非常简单：如果单元格不包含公式，则返回单元格的值

如果它是一个公式，试着得到它的预计算值

如果不能，请尝试使用^{}

如果失败(由于pycel对公式的有限支持或有一些错误)，则发出警告并返回None。

我创建了一个类，它隐藏了所有这些机器，并提供了读取单元格值的简单接口。

如果正确性优先于容错性，则很容易修改类，以便在步骤4中引发异常。

希望它能帮助别人。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}")