Repository Public
1# =============================================================================2# SP BOUCHER | SOFTWARE & RESEARCH3# ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4# =============================================================================5#6# PROJECT METADATA7# -----------------------------------------------------------------------------8# Project : ORIX Sheet9# File : Packages/SheetFormat/Tests/SheetFormatTests/Generators/lo_recalc.py10# Description : Recalculates a formula workbook with headless LibreOffice11# and returns the cached results.12# Module : SheetFormatTests13# Repository : https://git.spboucher.ai/orix-sheet14# Created : 2026-11-2115# Last Modified : 2026-11-2116#17# =============================================================================18# AUTHOR & PROFESSIONAL AFFILIATION19# =============================================================================20#21# Author : Simon-Pierre Boucher22# Position : Professor of Finance23# Institution : Université du Québec en Outaouais (UQO)24# Department : Département des sciences administratives25# Organization : SP Boucher26#27# Website : https://spboucher.ai28# Git : https://git.spboucher.ai29#30# =============================================================================31# OFFICIAL CONTACT DIRECTORY32# =============================================================================33#34# General : info@spboucher.ai35# Professional : contact@spboucher.ai36# Direct : simon@spboucher.ai37# Research : research@spboucher.ai38# Development : dev@spboucher.ai39# Support : support@spboucher.ai40# Security/Admin : admin@spboucher.ai41# Notifications : notifications@spboucher.ai42#43# Preferred Contact: support@spboucher.ai44#45# =============================================================================46# COPYRIGHT & INTELLECTUAL PROPERTY47# =============================================================================48#49# Copyright (c) 202650# Simon-Pierre Boucher51#52# All rights reserved where applicable.53#54# This file is governed by the applicable project license,55# copyright notices, institutional agreements and relevant law.56#57# Unless expressly authorized by the applicable license or58# rights holder, copying, redistribution, sublicensing,59# publication and commercial exploitation are not permitted.60#61# Existing third-party rights and notices remain unaffected.62#63# =============================================================================64# CONFIDENTIALITY & SECURITY65# =============================================================================66#67# Classification : Public68# Distribution : Public repository; use is governed by the project license69# Security Contact : admin@spboucher.ai70#71# Access to this source does not independently grant permission72# to redistribute or disclose confidential materials.73#74# Do not embed credentials, private keys, access tokens,75# certificates or confidential personal information.76#77# =============================================================================78# LICENSE & MAINTENANCE79# =============================================================================80#81# License : None specified (no license file in this repository)82# Maintainer : Simon-Pierre Boucher83# Technical Contact: dev@spboucher.ai84#85# =============================================================================86# END OF SP BOUCHER SOURCE HEADER87# =============================================================================8889"""Recalculates a formula workbook with headless LibreOffice and returns the cached results.9091LibreOffice runs with a private profile forced to the en-US locale and "always recalculate on load",92so results do not depend on the machine's settings.93"""94import os95import shutil96import subprocess97import tempfile9899import openpyxl100101SOFFICE = os.environ.get("SOFFICE", "/opt/homebrew/bin/soffice")102103REGISTRY = """<?xml version="1.0" encoding="UTF-8"?>104<oor:items xmlns:oor="http://openoffice.org/2001/registry" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">105<item oor:path="/org.openoffice.Setup/L10N"><prop oor:name="ooSetupSystemLocale" oor:op="fuse"><value>en-US</value></prop></item>106<item oor:path="/org.openoffice.Setup/L10N"><prop oor:name="ooLocale" oor:op="fuse"><value>en-US</value></prop></item>107<item oor:path="/org.openoffice.Office.Linguistic/General"><prop oor:name="UILocale" oor:op="fuse"><value>en-US</value></prop></item>108<item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="OOXMLRecalcMode" oor:op="fuse"><value>0</value></prop></item>109<item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="ODFRecalcMode" oor:op="fuse"><value>0</value></prop></item>110</oor:items>111"""112113114def recalc(rows, profile="/tmp/lo_profile_format", prepare=None):115 """rows: list of lists of cell values (formulas start with '='). Returns the same grid with computed values.116117 `prepare(ws)` may add extra cells (helper data) before saving.118 """119 work = tempfile.mkdtemp(prefix="orix_lo_")120 try:121 src = os.path.join(work, "in.xlsx")122 wb = openpyxl.Workbook()123 ws = wb.active124 ws.title = "Sheet1"125 for r, row in enumerate(rows, start=1):126 for c, v in enumerate(row, start=1):127 if v is not None:128 cell = ws.cell(row=r, column=c, value=v)129 if isinstance(v, str) and not v.startswith("="):130 cell.data_type = "s"131 if prepare:132 prepare(wb)133 wb.save(src)134 user = os.path.join(profile, "user")135 os.makedirs(user, exist_ok=True)136 with open(os.path.join(user, "registrymodifications.xcu"), "w") as f:137 f.write(REGISTRY)138 outdir = os.path.join(work, "out")139 subprocess.run([SOFFICE, f"-env:UserInstallation=file://{profile}", "--headless", "--norestore",140 "--convert-to", "xlsx:Calc MS Excel 2007 XML", "--outdir", outdir, src],141 check=True, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL, timeout=600)142 out = openpyxl.load_workbook(os.path.join(outdir, "in.xlsx"), data_only=True)143 ws2 = out["Sheet1"]144 result = []145 for r, row in enumerate(rows, start=1):146 result.append([ws2.cell(row=r, column=c).value for c in range(1, len(row) + 1)])147 return result148 finally:149 shutil.rmtree(work, ignore_errors=True)150