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

Question

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

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

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

How can i read the cell actual value ?

Answer 1

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

The data_only flag helps.

Answer 2

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 xlwings which can interact with the Excel runtime.

Answer 3

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

say you have an excel sheet with formulas, for the example I define one with 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

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

you can use xlwings to get the formula evaluated by excel:

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

which returns 3, the expected value.

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

Answer 4

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.

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.

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}")

Answer 5

data_only : read values for even for the formula cells.

keep_vba: it's used only if you are using macroenabled excel

file_location = 'C:\Arpan Saini\Monsters\Project_Testing\SecCardGrad\SecCardGrad_Latest_docs\Derived_Test_Cases_Secure_Card_Graduate.xlsm'
wb = load_workbook(file_location, keep_vba=True, data_only=True)

Answer 6

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...

Answer 7

Xlcalculator has the ability to evaluate a cell.

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

Answer 8

I found data_only option is not working properly if there is an "REF!" error cell in a worksheet. Openpyxl returns None for each cell value in my tiny test xlsx file. For me, after opening Excel and fixing the cell, data_only works perfectly. I use openpyxl 3.0.3