Spaces:
Sleeping
Sleeping
| """Formula utilities — Excel-compatible formula evaluation using the formulas library.""" | |
| from __future__ import annotations | |
| from typing import Any, Optional | |
| import openpyxl | |
| def evaluate_formula(wb: openpyxl.Workbook, sheet_name: str, cell_ref: str) -> Optional[Any]: | |
| """Evaluate an Excel formula in-memory using the formulas library. | |
| Falls back to openpyxl data_only reload if formulas library fails. | |
| Returns the computed value or None on failure. | |
| """ | |
| try: | |
| import formulas | |
| except ImportError: | |
| return _fallback_evaluate(wb, sheet_name, cell_ref) | |
| try: | |
| xl_model = formulas.ExcelModel().loads(wb.path).finish() | |
| solution = xl_model.calculate() | |
| key = f"'{sheet_name}'!{cell_ref.upper()}" | |
| return solution.get(key) | |
| except Exception: | |
| return _fallback_evaluate(wb, sheet_name, cell_ref) | |
| def _fallback_evaluate(wb: openpyxl.Workbook, sheet_name: str, cell_ref: str) -> Optional[Any]: | |
| """Fallback: reload workbook with data_only=True to get cached values.""" | |
| if not wb.path: | |
| return None | |
| try: | |
| wb_data = openpyxl.load_workbook(wb.path, data_only=True) | |
| ws = wb_data[sheet_name] | |
| return ws[cell_ref].value | |
| except Exception: | |
| return None | |