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

我使用 openpyxl 来读取单元格值(excel addin-webservice update this column。 )

我使用了 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 ?

151946 次浏览

正如@alex-martelli 所说,openpyxl 不计算公式。使用 openpyxl 打开 Excel 文件时,可以选择读取公式或最后一个计算值。如果正如您所指出的,公式依赖于外接程序,则缓存的值永远不会准确。作为文件规范之外的外接程序,它们永远不会得到支持。相反,您可能希望看到类似于 飞机的东西,它可以与 Excel 运行时进行交互。

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

data_only标志有所帮助。

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

策略相当简单:

  1. 如果单元格不包含公式,返回单元格的值;
  2. 如果它是一个公式,尝试得到它的预计算值;
  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.

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

如果正确性优于容错性,那么可以很容易地修改类,以便在步骤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}")

正如@Charlie Clark 提到的,你可以使用 xlwings(如果你有 MSExcel 的话)

假设你有一个带有公式的 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,我们将不会得到求值公式

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。

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

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)

我希望这能帮到你。

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)

输出结果是:

A2:0.1

我发现,如果工作表中有一个“ REF!”错误单元格,data _ only 选项就不能正常工作。 Openpyxl 为我的小测试 xlsx 文件中的每个单元格值返回 Nothing。 对我来说,在打开 Excel 并修复单元格之后,data _ only 工作得非常完美。 I use openpyxl 3.0.3

Data _ only: 甚至公式单元格的读取值。

Keep _ vba: 只有在使用宏启用的 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)

我没有使用 Python 库进行 Excel 计算,而是使用 Excel 进行计算。

为什么?它不是纯 Python,但是它最小化了涉及到的 Python 数量。我没有使用 Python 来计算 Excel 公式,而是让 Excel 处理自己的功能。这样可以避免 Python 中计算 Excel 公式的任何可能的错误。 Here's an outline of how this approach works:

  1. 使用 data _ only = False 调用 openpyxl 进行编辑,然后保存电子表格。
  2. 使用 subprocess。打开以在 Excel 中打开新的电子表格,并让 Excel 计算电子表格公式。
  3. 使用 pynput.key 保存更新的电子表格并退出 Excel。
  4. 使用 openpyxl 和 data _ only = True 打开更新的电子表格并获取公式的值。

Here is a test program for Windows that creates a new workbook, puts the formula "=SUM(Al:C3)" in cell E2, puts data into cells A1-C3, and evaluates the formula.

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


excel_prog = r'C:\Program Files\Microsoft Office\root\Office16\EXCEL.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()

在 openpyxl 上,使用 xlwing。