SPB Git

Repository Public

lo_recalc.py 6.5 KB 150 lines python

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
Files main
Menu

Theme