KeyInfo Bot
ee2b89a64b
Constraint: Public skills are published only by explicit administrator action unless they are tracked third-party market sources. Confidence: high Scope-risk: narrow Directive: Keep private/internal skills out of the public marketplace and preserve normal incremental market Git history. Tested: Marketplace validation passed.
92 lines
4.0 KiB
Python
92 lines
4.0 KiB
Python
"""Recalculate an XLSX through a temporary LibreOffice import/export, without macros."""
|
|
|
|
import argparse
|
|
import json
|
|
import os
|
|
import shutil
|
|
import subprocess
|
|
import tempfile
|
|
import zipfile
|
|
from pathlib import Path
|
|
|
|
from openpyxl import load_workbook
|
|
from office.soffice import run_soffice
|
|
|
|
|
|
def inspect_workbook(path):
|
|
formulas = load_workbook(path, data_only=False)
|
|
values = load_workbook(path, data_only=True)
|
|
try:
|
|
formula_cells = set()
|
|
errors = {}
|
|
for sheet in formulas:
|
|
if not hasattr(sheet, "iter_rows"):
|
|
continue
|
|
for row in sheet.iter_rows():
|
|
for cell in row:
|
|
location = f"{sheet.title}!{cell.coordinate}"
|
|
if cell.data_type == "f":
|
|
formula_cells.add(location)
|
|
cached = values[sheet.title][cell.coordinate]
|
|
if cached.data_type == "e":
|
|
errors.setdefault(str(cached.value), []).append(location)
|
|
elif cell.data_type == "f" and cached.value is None:
|
|
errors.setdefault("MISSING_FORMULA_CACHE", []).append(location)
|
|
return formula_cells, errors
|
|
finally:
|
|
formulas.close()
|
|
values.close()
|
|
|
|
|
|
def recalc(filename, timeout=120):
|
|
source = Path(filename).expanduser().resolve()
|
|
if not source.is_file() or source.suffix.lower() != ".xlsx":
|
|
return {"error": "Input must be an existing .xlsx file; use Excel for macro-enabled workbooks"}
|
|
try:
|
|
with zipfile.ZipFile(source) as archive:
|
|
if any(name.startswith("xl/externalLinks/") for name in archive.namelist()):
|
|
return {"error": "External workbook links require validation in Excel; original file was not modified"}
|
|
original_formulas, _ = inspect_workbook(source)
|
|
with tempfile.TemporaryDirectory(prefix="xlsx-recalc-", dir=source.parent, ignore_cleanup_errors=True) as temporary:
|
|
root = Path(temporary)
|
|
input_dir, output_dir = root / "input", root / "output"
|
|
input_dir.mkdir()
|
|
output_dir.mkdir()
|
|
candidate = input_dir / source.name
|
|
shutil.copy2(source, candidate)
|
|
result = run_soffice([
|
|
"--headless", "--norestore", "--convert-to", "xlsx:Calc MS Excel 2007 XML",
|
|
"--outdir", str(output_dir), str(candidate),
|
|
], capture_output=True, text=True, encoding="utf-8", errors="replace", timeout=timeout)
|
|
recalculated = output_dir / source.name
|
|
if result.returncode or not recalculated.is_file():
|
|
return {"error": "LibreOffice did not export a recalculated workbook", "details": result.stderr or result.stdout}
|
|
formula_cells, errors = inspect_workbook(recalculated)
|
|
if original_formulas != formula_cells:
|
|
return {"error": "Formula locations changed during conversion; original file was not modified"}
|
|
error_count = sum(len(locations) for locations in errors.values())
|
|
report = {
|
|
"status": "errors_found" if error_count else "success",
|
|
"total_formulas": len(formula_cells),
|
|
"total_errors": error_count,
|
|
"error_summary": {
|
|
error: {"count": len(locations), "locations": locations[:100]}
|
|
for error, locations in errors.items()
|
|
},
|
|
}
|
|
if not errors:
|
|
os.replace(recalculated, source)
|
|
return report
|
|
except (OSError, subprocess.SubprocessError, zipfile.BadZipFile, ValueError) as exc:
|
|
return {"error": str(exc)}
|
|
|
|
|
|
if __name__ == "__main__":
|
|
parser = argparse.ArgumentParser(description=__doc__)
|
|
parser.add_argument("input")
|
|
parser.add_argument("timeout", nargs="?", type=int, default=120)
|
|
args = parser.parse_args()
|
|
report = recalc(args.input, args.timeout)
|
|
print(json.dumps(report, ensure_ascii=False, indent=2))
|
|
raise SystemExit(1 if "error" in report else 0)
|