recalc.py 2.7 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788
  1. #!/usr/bin/env python3
  2. """Recalculate an .xlsx file's formulas using LibreOffice headless.
  3. Usage: python recalc.py <path.xlsx> [timeout_seconds]
  4. openpyxl writes formula strings but does not compute them. Downstream scripts
  5. that open the file with data_only=True get None for every formula cell until
  6. something has actually calculated the workbook. Excel does this on open;
  7. headless pipelines need LibreOffice (or similar) to do it explicitly.
  8. Exits 0 on success (workbook recomputed and resaved in place), non-zero on
  9. failure. Writes status JSON to stdout either way.
  10. """
  11. import json
  12. import shutil
  13. import subprocess
  14. import sys
  15. import tempfile
  16. from pathlib import Path
  17. def find_libreoffice() -> str | None:
  18. for cmd in ("libreoffice", "soffice"):
  19. path = shutil.which(cmd)
  20. if path:
  21. return path
  22. return None
  23. def recalc(xlsx_path: str, timeout: int = 60) -> dict:
  24. src = Path(xlsx_path).resolve()
  25. if not src.exists():
  26. return {"status": "error", "error": f"File not found: {src}"}
  27. lo = find_libreoffice()
  28. if lo is None:
  29. return {
  30. "status": "error",
  31. "error": "libreoffice not found on PATH — install it or recalc in a real Excel session",
  32. }
  33. with tempfile.TemporaryDirectory() as td:
  34. try:
  35. subprocess.run(
  36. [
  37. lo,
  38. "--headless",
  39. "--calc",
  40. "--convert-to",
  41. "xlsx",
  42. str(src),
  43. "--outdir",
  44. td,
  45. ],
  46. stdin=subprocess.DEVNULL, check=True,
  47. capture_output=True,
  48. timeout=timeout,
  49. )
  50. except subprocess.TimeoutExpired:
  51. return {"status": "error", "error": f"libreoffice timed out after {timeout}s"}
  52. except subprocess.CalledProcessError as e:
  53. return {
  54. "status": "error",
  55. "error": f"libreoffice exited {e.returncode}: {e.stderr.decode(errors='replace')[:500]}",
  56. }
  57. produced = Path(td) / src.name
  58. if not produced.exists():
  59. return {"status": "error", "error": "libreoffice did not produce output file"}
  60. shutil.copy(produced, src)
  61. return {"status": "success", "file": str(src)}
  62. def main():
  63. if len(sys.argv) < 2:
  64. print("Usage: python recalc.py <path.xlsx> [timeout_seconds]", file=sys.stderr)
  65. sys.exit(2)
  66. timeout = int(sys.argv[2]) if len(sys.argv) > 2 else 60
  67. result = recalc(sys.argv[1], timeout=timeout)
  68. print(json.dumps(result, indent=2))
  69. sys.exit(0 if result["status"] == "success" else 1)
  70. if __name__ == "__main__":
  71. main()