wb = openpyxl.load_workbook(filename, data_only=True)
data_only 标志有所帮助。
wb2 = openpyxl.load_workbook(filename)。然后在读取相应的索引时，你从 wb 中得到值，从 wb2 中得到方程。 - Ajeeb.K.Pdata_only=True 并尝试读取带有公式的单元格时（对于静态单元格或未使用 data_only=True 的单元格正常工作），无论是 cell.value 还是 cell.internal_value 都返回 None。有什么想法吗？ - Jean-Francois T.openpyxl不会计算公式。当你使用openpyxl打开一个Excel文件时，你可以选择读取公式或最后计算的值。如果像你所指示的那样，公式依赖于插件，那么缓存的值永远无法准确。由于插件在文件规范之外，它们将永远不会得到支持。相反，你可能想要看一下类似xlwings这样的工具，它可以与Excel运行时进行交互。data_only=True参数实现的吗？(2)在现实世界中，“last calculated value”是什么意思？也就是说，如果在上次（手动/人为）更改后保存了XL文件，这是否意味着所有单元格都被重新计算/处于它们的“last calculated value”？通常何时重新计算单元格的值？（我知道这更多是一个Excel问题而不是OpenPyXL，但感谢您的澄清） - cssyphusdata_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)
如@Charlie Clark所提到的，如果你有MS Excel，你可以使用xlwings。这里是一个例子：
假设您有一个带有公式的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。
当处理具有非常复杂的公式和工作表之间的引用的电子表格时，我发现它非常有用。
pycel 进行评估；
4. 如果失败（由于 pycel 对公式的支持有限或出现某些错误），则发出警告并返回 None。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}")
pycel。多么棒的想法！ - Rockallitepycel仅支持有限的Excel函数集。但对于简单的情况，它可以很好地工作。 - krvkirfrom openpyxl.cell.cell import TYPE_FORMULA，gen_graph() 已经不再可用 - 直接使用 evaluate()。 - advance512import 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)
我希望这可以帮助您...
不要使用openpyxl，而是使用xlwings。
与其使用 Python 库执行 Excel 计算，我让 Excel 自己完成。
为什么？虽然不是纯 Python，但它最大程度地减少了涉及的 Python 量。我不使用 Python 来计算 Excel 公式，而是让 Excel 处理自己的功能。这避免了评估 Excel 公式的 Python 中可能存在的任何错误。 以下是该方法的概要：
下面是一个 Windows 上的测试程序，它创建一个新的工作簿，在单元格 E2 中放置 "=SUM(A1:C3)" 公式，在单元格 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 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()
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
openpyxl不能复制Excel的庞大公式计算子系统，因此我认为你无法让它自动重新计算所有公式。最好的情况是，你可以为该大型公式语言的子集实现自己的基于eval的解释器。 - Alex Martelli