validate_formula_syntax
Check Excel formula accuracy without execution by validating syntax in a specified cell and sheet. Ensures correct formula structure before application.
Instructions
Validate Excel formula syntax without applying it.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| cell | Yes | ||
| filepath | Yes | ||
| formula | Yes | ||
| sheet_name | Yes |
Implementation Reference
- Primary session-based handler for validating Excel formula syntax. Applies formula temporarily to a cell, checks for errors, and restores original content.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)}"}
- Legacy file-based handler for validating Excel formula syntax. Opens workbook, temporarily applies formula, checks validity, restores, and closes without saving.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}")
- src/xlwings_mcp/server.py:267-318 (registration)MCP tool registration for 'validate_formula_syntax'. Dispatches to session-based (preferred) or legacy file-based handlers based on parameters provided.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
- Alternative file-based formula validation handler in formatting module. Checks for specific Excel error values like #NAME? after temporary application.def validate_formula_syntax_xlw( filepath: str, sheet_name: str, cell: str, formula: str ) -> Dict[str, Any]: """ Validate Excel formula syntax using xlwings without applying it. Args: filepath: Path to Excel file sheet_name: Name of worksheet cell: Target cell for formula formula: Formula to validate Returns: Dict with validation result or error """ app = None wb = None # Initialize COM for thread safety (Windows) _com_initialize() try: logger.info(f"Validating formula syntax: {formula}") # Check if file exists if not os.path.exists(filepath): return {"error": f"File not found: {filepath}"} # Ensure formula starts with = if not formula.startswith('='): formula = '=' + formula # Open Excel app and workbook app = xw.App(visible=False, add_book=False) wb = app.books.open(filepath) # Check if sheet exists sheet_names = [s.name for s in wb.sheets] if sheet_name not in sheet_names: return {"error": f"Sheet '{sheet_name}' not found"} sheet = wb.sheets[sheet_name] # Try to apply the formula to a temporary cell to validate try: # Store original value target_cell = sheet.range(cell) original_value = target_cell.value original_formula = target_cell.formula # Try to set the formula target_cell.formula = formula # Check if Excel accepted the formula # If there's an error, Excel will show #NAME?, #VALUE!, etc. cell_value = target_cell.value # Check for common Excel errors excel_errors = ['#NULL!', '#DIV/0!', '#VALUE!', '#REF!', '#NAME?', '#NUM!', '#N/A'] formula_valid = True error_type = None if isinstance(cell_value, str) and cell_value in excel_errors: formula_valid = False error_type = cell_value # Restore original value/formula if original_formula: target_cell.formula = original_formula else: target_cell.value = original_value # Don't save - we were just validating if formula_valid: logger.info(f"✅ Formula syntax is valid: {formula}") return { "message": f"Formula syntax is valid", "formula": formula, "cell": cell, "valid": True } else: logger.warning(f"⚠️ Formula has error: {error_type}") return { "message": f"Formula contains error: {error_type}", "formula": formula, "cell": cell, "valid": False, "error_type": error_type } except Exception as e: # If we can't set the formula, it's invalid logger.error(f"❌ Invalid formula syntax: {str(e)}") return { "message": f"Invalid formula syntax: {str(e)}", "formula": formula, "cell": cell, "valid": False, "error": str(e) } except Exception as e: logger.error(f"❌ Error validating formula: {str(e)}") return {"error": str(e)} finally: if wb: wb.close() if app: app.quit()