validate_formula_syntax
Check Excel formula syntax for errors before applying it to a worksheet cell, preventing calculation issues.
Instructions
Validate Excel formula syntax without applying it.
Args:
sheet_name: Name of worksheet
cell: Cell address (e.g., "A1")
formula: Excel formula to validate
session_id: Session ID from open_workbook (preferred)
filepath: Path to Excel file (legacy, deprecated)
Note: Use session_id for better performance. filepath parameter is deprecated.Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sheet_name | Yes | ||
| cell | Yes | ||
| formula | Yes | ||
| session_id | No | ||
| filepath | No |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |
Implementation Reference
- src/xlwings_mcp/server.py:267-318 (registration)Registration and primary handler for the 'validate_formula_syntax' MCP tool. Dispatches to session-based or file-based implementations based on input parameters.
def validate_formula_syntax( sheet_name: str, cell: str, formula: str, session_id: Optional[str] = None, filepath: Optional[str] = None ) -> str: """ Validate Excel formula syntax without applying it. Args: sheet_name: Name of worksheet cell: Cell address (e.g., "A1") formula: Excel formula to validate session_id: Session ID from open_workbook (preferred) filepath: Path to Excel file (legacy, deprecated) Note: Use session_id for better performance. filepath parameter is deprecated. """ try: # Support both new (session_id) and old (filepath) API if session_id: # New API: use session session = SESSION_MANAGER.get_session(session_id) if not session: return ERROR_TEMPLATES['SESSION_NOT_FOUND'].format( session_id=session_id, ttl=10 # Default TTL is 10 minutes (600 seconds) ) with session.lock: from xlwings_mcp.xlwings_impl.calculations_xlw import validate_formula_syntax_xlw_with_wb result = validate_formula_syntax_xlw_with_wb(session.workbook, sheet_name, cell, formula) elif filepath: # Legacy API: backwards compatibility logger.warning("Using deprecated filepath parameter. Please use session_id instead.") full_path = get_excel_path(filepath) from xlwings_mcp.xlwings_impl.calculations_xlw import validate_formula_syntax_xlw result = validate_formula_syntax_xlw(full_path, sheet_name, cell, formula) else: return ERROR_TEMPLATES['PARAMETER_MISSING'].format( param1='session_id', param2='filepath' ) return result.get("message", "Formula validation completed") if "error" not in result else f"Error: {result['error']}" except (ValidationError, CalculationError) as e: return f"Error: {str(e)}" except Exception as e: logger.error(f"Error validating formula: {e}") raise - Core handler implementation for session-based (with_wb) formula syntax validation. Temporarily applies formula to check syntax and previews value.
def validate_formula_syntax_xlw_with_wb( wb, sheet_name: str, cell: str, formula: str ) -> Dict[str, Any]: """Session-based formula syntax validation using existing workbook object. Args: wb: Workbook object from session sheet_name: Sheet name cell: Target cell formula: Formula to validate Returns: Validation result dictionary """ try: # Check sheet exists if sheet_name not in [s.name for s in wb.sheets]: return {"error": f"Sheet '{sheet_name}' not found"} ws = wb.sheets[sheet_name] # Normalize formula if not formula.startswith('='): formula = f'={formula}' # Backup original values cell_range = ws.range(cell) original_value = cell_range.value original_formula = cell_range.formula try: # Temporarily apply formula for validation cell_range.formula = formula # Check calculated result preview_value = cell_range.value # Restore original values if original_formula and original_formula.startswith('='): cell_range.formula = original_formula else: cell_range.value = original_value return { "valid": True, "message": "Formula syntax is valid", "formula": formula, "preview_value": preview_value } except Exception as e: # Try to restore original values try: if original_formula and original_formula.startswith('='): cell_range.formula = original_formula else: cell_range.value = original_value except Exception: pass return { "valid": False, "message": f"Invalid formula syntax: {str(e)}", "formula": formula } except Exception as e: logger.error(f"xlwings formula validation failed: {e}") return {"error": f"Failed to validate formula: {str(e)}"} - Helper implementation for file-based (without session) formula syntax validation. Opens workbook temporarily to validate.
def validate_formula_syntax_xlw( filepath: str, sheet_name: str, cell: str, formula: str ) -> Dict[str, Any]: """xlwings를 사용한 수식 문법 검증 Args: filepath: Excel 파일 경로 sheet_name: 시트명 cell: 대상 셀 formula: 검증할 수식 Returns: 검증 결과 딕셔너리 """ app = None wb = None # Initialize COM for thread safety (Windows) _com_initialize() try: # 파일 경로 검증 if not os.path.exists(filepath): return {"error": f"File not found: {filepath}"} # Excel 앱 시작 app = xw.App(visible=False, add_book=False) # 워크북 열기 wb = app.books.open(filepath) # 시트 존재 확인 if sheet_name not in [s.name for s in wb.sheets]: return {"error": f"Sheet '{sheet_name}' not found"} ws = wb.sheets[sheet_name] # 수식 정규화 if not formula.startswith('='): formula = f'={formula}' # 백업용 원본 값 저장 cell_range = ws.range(cell) original_value = cell_range.value original_formula = cell_range.formula try: # 임시로 수식 적용해서 검증 cell_range.formula = formula # 계산 결과 확인 preview_value = cell_range.value # 원래 값으로 복원 if original_formula and original_formula.startswith('='): cell_range.formula = original_formula else: cell_range.value = original_value return { "valid": True, "message": "Formula syntax is valid", "formula": formula, "preview_value": preview_value } except Exception as e: # 원래 값으로 복원 시도 try: if original_formula and original_formula.startswith('='): cell_range.formula = original_formula else: cell_range.value = original_value except Exception: pass return { "valid": False, "message": f"Invalid formula syntax: {str(e)}", "formula": formula } except Exception as e: logger.error(f"xlwings 수식 검증 실패: {e}") return {"error": f"Failed to validate formula: {str(e)}"} finally: # 리소스 정리 (저장하지 않음 - 검증만) if wb: try: wb.close() except Exception as e: logger.warning(f"워크북 닫기 실패: {e}") if app: try: app.quit() except Exception as e: logger.warning(f"Excel 앱 종료 실패: {e}")