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