Чтение значения ячейки Excel, а не формулы, вычисляющей его -openpyxl

Ответ 1

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

Флаг data_only помогает.

Ответ 2

Как говорит @alex-martelli, openpyxl не оценивает формулы. Когда вы открываете файл Excel с помощью openpyxl, у вас есть выбор либо прочитать формулы, либо последнее рассчитанное значение. Если, как вы указываете, формула зависит от надстроек, тогда кешированное значение никогда не может быть точным. В качестве надстроек вне спецификации файла они никогда не будут поддерживаться. Вместо этого вы можете посмотреть на что-то вроде xlwings, которые могут взаимодействовать со средой выполнения Excel.

Ответ 3

Столкнулся с такой же проблемой. Необходим для чтения значений ячеек, какими бы ни были эти ячейки: скаляры, формулы с предварительно вычисленными значениями или формулы без них, отказоустойчивость предпочтительнее правильности.

Стратегия довольно проста:

1. если ячейка не содержит формулу, вернуть значение ячейки;
2. если это формула, попробуйте получить ее предварительно вычисленное значение;
3. если не смог, попробуйте оценить его с помощью pycel;
4. если pycel сбой (из-за ограниченной поддержки формул 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 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}")

Ответ 4

Как сказал @Charlie Clark, вы можете использовать xlwings (если у вас MS Excel). Вот пример

скажем, у вас есть лист 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')

Как уже упоминалось, если мы снова openpyxl Excel с openpyxl, мы не получим openpyxl формулу

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

Вы можете использовать xlwings для вычисления формулы Excel:

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

который возвращает 3, ожидаемое значение.

Я нашел это очень полезным при работе с электронными таблицами с очень сложными формулами и ссылками между листами.