如何使用 openpyxl 读取 Excel 单元格值而不是计算它的公式？-IT科技

摘要：问题描述：我正在用来openpyxl读取单元格值（excel addin-webservice 更新了此列）。我已经使用过data_only = True，但它没有显示当前单元格值，而是显示 Excel 上次读取工作表时存储的值。wbFile = openpyxl.load_workbook(filename...

问题描述：

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

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

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

我如何读取实际的单元格值？

解决方案 1：

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

这面data_only旗帜很有帮助。

解决方案 2：

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

解决方案 3：

data_only ：读取公式单元格的值。

keep_vba：仅当您使用启用宏的 Excel 时才使用它

file_location = 'C:Arpan SainiMonstersProject_TestingSecCardGradSecCardGrad_Latest_docsDerived_Test_Cases_Secure_Card_Graduate.xlsm'
wb = load_workbook(file_location, keep_vba=True, data_only=True)

解决方案 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')

如上所述，如果我们再次使用加载 excel openpyxl，我们将无法获得评估公式

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

您可以使用xlwingsExcel 评估公式：

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

返回预期值 3。

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

解决方案 5：

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

该策略非常简单：

如果单元格不包含公式，则返回单元格的值；
如果它是一个公式，则尝试获取其预先计算的值；
如果不能，请尝试使用来评估它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'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}")

解决方案 6：

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

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)

我希望这可以帮助您...

解决方案 7：

在 openpyxl 上，请使用 xlwings。

解决方案 8：

我发现，如果工作表中有一个“REF!”错误单元格，data_only 选项将无法正常工作。Openpyxl 为我微小的测试 xlsx 文件中的每个单元格值返回 None。对我来说，打开 Excel 并修复单元格后，data_only 就可以正常工作。我使用 openpyxl 3.0.3

解决方案 9：

我没有使用 Python 库来进行 Excel 计算，而是让 Excel 来执行。

为什么？它不是纯 Python，但它最大限度地减少了涉及的 Python 数量。我没有使用 Python 来评估 Excel 公式，而是让 Excel 处理自己的功能。这避免了评估 Excel 公式的 Python 中可能出现的任何错误。以下是此方法的工作原理概述：

使用 data_only=False 调用 openpyxl 进行编辑，然后保存电子表格。
使用 subprocess.Popen 在 Excel 中打开新的电子表格，并让 Excel 评估电子表格公式。
使用 pynput.keyboard 保存更新的电子表格并退出 Excel。
使用带有 data_only=True 的 openpyxl 打开更新的电子表格并获取公式的值。

这是一个适用于 Windows 的测试程序，它创建一个新的工作簿，将公式“=SUM(Al:C3)”放入单元格 E2 中，将数据放入单元格 A1-C3 中，然后评估该公式。

from openpyxl import load_workbook, Workbook
from pynput.keyboard import Key, Controller
import subprocess
import time
import os

excel_prog = r'C:Program FilesMicrosoft Office
ootOffice16EXCEL.EXE'

# Create test Excel workbook, get default worksheet.
wb = Workbook()
ws = wb.active

# Put data and a formula into worksheet.
for row_index in range(1,4):
    for column_index in range(1,4):
        ws.cell(row = row_index, column = column_index).value = row_index + column_index
ws['E1'].value = 'Sum of cells in range A1:C3:'
ws['E2'].value = '=SUM(A1:C3)'

# Try to get value of formula.  We'll see the formula instead.
print('E2:', ws['E2'].value)

# Save and close workbook.
wb.save(filename = 'test.xlsx')
wb.close()

# Pause to give workbook time to close.
time.sleep(5)

# Open the workbook in Excel.  I specify folder, otherwise Excel will
# open in "Protected View", interfering with using pynput.
subprocess.Popen([excel_prog, os.path.join(os.getcwd(), 'test.xlsx')])

# Pause to give workbook time to open and for formulas to update.
time.sleep(5)

# Save workbook using pynput.
keyboard = Controller()
with keyboard.pressed(Key.ctrl):
    keyboard.press('s')
    keyboard.release('s')

# Pause to give workbook time to save.
time.sleep(5)

# Close workbook.
with keyboard.pressed(Key.alt):
    keyboard.press(Key.f4)
    keyboard.release(Key.f4)

# Pause to give workbook time to fully close.
time.sleep(5)

# Open Excel workbook and worksheet in openpyxl, data-only.
wb = load_workbook(filename = 'test.xlsx', data_only = True)
ws = wb.active

# Get value of the cell containing the formula.
print('E2:', ws['E2'].value)

# Close workbook.
wb.close()

解决方案 10：

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)

输出为：

First!A2 的“评估”值：0.1