1"""
2Excel Formula Recalculation Script
3Recalculates all formulas in an Excel file using LibreOffice
4"""
5 
6import json
7import os
8import platform
9import subprocess
10import sys
11from pathlib import Path
12 
13from office.soffice import get_soffice_env
14 
15from openpyxl import load_workbook
16 
17MACRO_DIR_MACOS = "~/Library/Application Support/LibreOffice/4/user/basic/Standard"
18MACRO_DIR_LINUX = "~/.config/libreoffice/4/user/basic/Standard"
19MACRO_FILENAME = "Module1.xba"
20 
21RECALCULATE_MACRO = """<?xml version="1.0" encoding="UTF-8"?>
22<!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd">
23<script:module xmlns:script="http://openoffice.org/2000/script" script:name="Module1" script:language="StarBasic">
24    Sub RecalculateAndSave()
25      ThisComponent.calculateAll()
26      ThisComponent.store()
27      ThisComponent.close(True)
28    End Sub
29</script:module>"""
30 
31 
32def has_gtimeout():
33    try:
34        subprocess.run(
35            ["gtimeout", "--version"], capture_output=True, timeout=1, check=False
36        )
37        return True
38    except (FileNotFoundError, subprocess.TimeoutExpired):
39        return False
40 
41 
42def setup_libreoffice_macro():
43    macro_dir = os.path.expanduser(
44        MACRO_DIR_MACOS if platform.system() == "Darwin" else MACRO_DIR_LINUX
45    )
46    macro_file = os.path.join(macro_dir, MACRO_FILENAME)
47 
48    if (
49        os.path.exists(macro_file)
50        and "RecalculateAndSave" in Path(macro_file).read_text()
51    ):
52        return True
53 
54    if not os.path.exists(macro_dir):
55        subprocess.run(
56            ["soffice", "--headless", "--terminate_after_init"],
57            capture_output=True,
58            timeout=10,
59            env=get_soffice_env(),
60        )
61        os.makedirs(macro_dir, exist_ok=True)
62 
63    try:
64        Path(macro_file).write_text(RECALCULATE_MACRO)
65        return True
66    except Exception:
67        return False
68 
69 
70def recalc(filename, timeout=30):
71    if not Path(filename).exists():
72        return {"error": f"File {filename} does not exist"}
73 
74    abs_path = str(Path(filename).absolute())
75 
76    if not setup_libreoffice_macro():
77        return {"error": "Failed to setup LibreOffice macro"}
78 
79    cmd = [
80        "soffice",
81        "--headless",
82        "--norestore",
83        "vnd.sun.star.script:Standard.Module1.RecalculateAndSave?language=Basic&location=application",
84        abs_path,
85    ]
86 
87    if platform.system() == "Linux":
88        cmd = ["timeout", str(timeout)] + cmd
89    elif platform.system() == "Darwin" and has_gtimeout():
90        cmd = ["gtimeout", str(timeout)] + cmd
91 
92    result = subprocess.run(cmd, capture_output=True, text=True, env=get_soffice_env())
93 
94    if result.returncode != 0 and result.returncode != 124:  
95        error_msg = result.stderr or "Unknown error during recalculation"
96        if "Module1" in error_msg or "RecalculateAndSave" not in error_msg:
97            return {"error": "LibreOffice macro not configured properly"}
98        return {"error": error_msg}
99 
100    try:
101        wb = load_workbook(filename, data_only=True)
102 
103        excel_errors = [
104            "#VALUE!",
105            "#DIV/0!",
106            "#REF!",
107            "#NAME?",
108            "#NULL!",
109            "#NUM!",
110            "#N/A",
111        ]
112        error_details = {err: [] for err in excel_errors}
113        total_errors = 0
114 
115        for sheet_name in wb.sheetnames:
116            ws = wb[sheet_name]
117            for row in ws.iter_rows():
118                for cell in row:
119                    if cell.value is not None and isinstance(cell.value, str):
120                        for err in excel_errors:
121                            if err in cell.value:
122                                location = f"{sheet_name}!{cell.coordinate}"
123                                error_details[err].append(location)
124                                total_errors += 1
125                                break
126 
127        wb.close()
128 
129        result = {
130            "status": "success" if total_errors == 0 else "errors_found",
131            "total_errors": total_errors,
132            "error_summary": {},
133        }
134 
135        for err_type, locations in error_details.items():
136            if locations:
137                result["error_summary"][err_type] = {
138                    "count": len(locations),
139                    "locations": locations[:20],  
140                }
141 
142        wb_formulas = load_workbook(filename, data_only=False)
143        formula_count = 0
144        for sheet_name in wb_formulas.sheetnames:
145            ws = wb_formulas[sheet_name]
146            for row in ws.iter_rows():
147                for cell in row:
148                    if (
149                        cell.value
150                        and isinstance(cell.value, str)
151                        and cell.value.startswith("=")
152                    ):
153                        formula_count += 1
154        wb_formulas.close()
155 
156        result["total_formulas"] = formula_count
157 
158        return result
159 
160    except Exception as e:
161        return {"error": str(e)}
162 
163 
164def main():
165    if len(sys.argv) < 2:
166        print("Usage: python recalc.py <excel_file> [timeout_seconds]")
167        print("\nRecalculates all formulas in an Excel file using LibreOffice")
168        print("\nReturns JSON with error details:")
169        print("  - status: 'success' or 'errors_found'")
170        print("  - total_errors: Total number of Excel errors found")
171        print("  - total_formulas: Number of formulas in the file")
172        print("  - error_summary: Breakdown by error type with locations")
173        print("    - #VALUE!, #DIV/0!, #REF!, #NAME?, #NULL!, #NUM!, #N/A")
174        sys.exit(1)
175 
176    filename = sys.argv[1]
177    timeout = int(sys.argv[2]) if len(sys.argv) > 2 else 30
178 
179    result = recalc(filename, timeout)
180    print(json.dumps(result, indent=2))
181 
182 
183if __name__ == "__main__":
184    main()
185