get_data_validation_info
Retrieve all data validation rules from an Excel worksheet to identify which cells have validation and what types are applied.
Instructions
Get all data validation rules in a worksheet.
This tool helps identify which cell ranges have validation rules
and what types of validation are applied.
Args:
sheet_name: Name of worksheet
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.
Returns:
JSON string containing all validation rules in the worksheetInput Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sheet_name | Yes | ||
| session_id | No | ||
| filepath | No |
Implementation Reference
- src/xlwings_mcp/server.py:1300-1358 (registration)MCP tool registration and handler wrapper. Dispatches to appropriate implementation based on session_id or filepath, supports legacy API.
@mcp.tool() def get_data_validation_info( sheet_name: str, session_id: Optional[str] = None, filepath: Optional[str] = None ) -> str: """ Get all data validation rules in a worksheet. This tool helps identify which cell ranges have validation rules and what types of validation are applied. Args: sheet_name: Name of worksheet 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. Returns: JSON string containing all validation rules in the worksheet """ 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.validation_xlw import get_data_validation_info_xlw_with_wb result = get_data_validation_info_xlw_with_wb(session.workbook, sheet_name) 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.validation_xlw import get_data_validation_info_xlw result = get_data_validation_info_xlw(full_path, sheet_name) else: return ERROR_TEMPLATES['PARAMETER_MISSING'].format( param1='session_id', param2='filepath' ) if "error" in result: return f"Error: {result['error']}" import json return json.dumps(result, indent=2, default=str) except (ValidationError, DataError) as e: return f"Error: {str(e)}" except Exception as e: logger.error(f"Error getting validation info: {e}") raise - Primary handler implementation for file-path based access. Opens workbook, scans used range for data validation rules using Excel COM API, extracts details like type, operator, formulas.
def get_data_validation_info_xlw( filepath: str, sheet_name: str ) -> Dict[str, Any]: """ Get all data validation rules in a worksheet using xlwings. This tool helps identify which cell ranges have validation rules and what types of validation are applied. Args: filepath: Path to Excel file sheet_name: Name of worksheet Returns: Dict containing all validation rules in the worksheet """ app = None wb = None # Initialize COM for thread safety (Windows) _com_initialize() try: logger.info(f"Getting data validation info for {sheet_name}") # Check if file exists if not os.path.exists(filepath): return {"error": f"File not found: {filepath}"} # 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] # Access worksheet COM object for validation ws_com = sheet.api validation_rules = [] processed_ranges = set() # Get used range to scan for validation try: used_range = sheet.used_range if used_range: # Iterate through cells to find validation rules # Note: This is more efficient than checking every cell # We'll check representative cells and expand to find full ranges max_row = used_range.last_cell.row max_col = used_range.last_cell.column # Sample cells to check (every 5th cell for efficiency) for row in range(1, max_row + 1, 5): for col in range(1, max_col + 1, 5): try: cell = sheet.range((row, col)) cell_address = cell.address.replace('$', '') # Skip if already processed if cell_address in processed_ranges: continue # Check if cell has validation using COM API cell_com = cell.api validation = cell_com.Validation # Check if validation exists (Type > 0 means validation is present) if hasattr(validation, 'Type') and validation.Type > 0: # Found validation, now find the full range validation_info = { "range": cell_address, "type": get_validation_type_name(validation.Type), "operator": None, "formula1": None, "formula2": None, "error_message": None, "input_message": None, "show_error": True, "show_input": True } # Get validation details try: if hasattr(validation, 'Operator'): validation_info["operator"] = get_operator_name(validation.Operator) except: pass try: if hasattr(validation, 'Formula1'): validation_info["formula1"] = str(validation.Formula1) except: pass try: if hasattr(validation, 'Formula2'): validation_info["formula2"] = str(validation.Formula2) except: pass try: if hasattr(validation, 'ErrorMessage'): validation_info["error_message"] = validation.ErrorMessage except: pass try: if hasattr(validation, 'InputMessage'): validation_info["input_message"] = validation.InputMessage except: pass try: if hasattr(validation, 'ShowError'): validation_info["show_error"] = bool(validation.ShowError) except: pass try: if hasattr(validation, 'ShowInput'): validation_info["show_input"] = bool(validation.ShowInput) except: pass # Try to find the full range with this validation # by checking adjacent cells full_range = expand_validation_range(sheet, row, col, validation) validation_info["range"] = full_range # Mark cells as processed for r in range(row, row + 10): for c in range(col, col + 10): processed_ranges.add(f"{chr(64+c)}{r}") validation_rules.append(validation_info) except Exception as e: # Cell might not have validation, continue continue except Exception as e: logger.warning(f"Error scanning for validation rules: {e}") # Return validation information result = { "sheet": sheet_name, "validation_count": len(validation_rules), "validation_rules": validation_rules } logger.info(f"✅ Found {len(validation_rules)} validation rules in {sheet_name}") return result except Exception as e: logger.error(f"Error getting validation info: {e}") return {"error": str(e)} finally: if wb: wb.close() if app: app.quit() - Session-optimized handler using pre-opened workbook. Identical scanning logic, avoids reopening file.
def get_data_validation_info_xlw_with_wb( wb, sheet_name: str ) -> Dict[str, Any]: """ Session-based data validation info retrieval using existing workbook object. Args: wb: Workbook object from session sheet_name: Name of worksheet Returns: Dict containing all validation rules in the worksheet """ try: logger.info(f"🔍 Getting data validation info for {sheet_name}") # 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] # Access worksheet COM object for validation ws_com = sheet.api validation_rules = [] processed_ranges = set() # Get used range to scan for validation try: used_range = sheet.used_range if used_range: # Iterate through cells to find validation rules # Note: This is more efficient than checking every cell # We'll check representative cells and expand to find full ranges max_row = used_range.last_cell.row max_col = used_range.last_cell.column # Sample cells to check (every 5th cell for efficiency) for row in range(1, max_row + 1, 5): for col in range(1, max_col + 1, 5): try: cell = sheet.range((row, col)) cell_address = cell.address.replace('$', '') # Skip if already processed if cell_address in processed_ranges: continue # Check if cell has validation using COM API cell_com = cell.api validation = cell_com.Validation # Check if validation exists (Type > 0 means validation is present) if hasattr(validation, 'Type') and validation.Type > 0: # Found validation, now find the full range validation_info = { "range": cell_address, "type": get_validation_type_name(validation.Type), "operator": None, "formula1": None, "formula2": None, "error_message": None, "input_message": None, "show_error": True, "show_input": True } # Get validation details try: if hasattr(validation, 'Operator'): validation_info["operator"] = get_operator_name(validation.Operator) except: pass try: if hasattr(validation, 'Formula1'): validation_info["formula1"] = str(validation.Formula1) except: pass try: if hasattr(validation, 'Formula2'): validation_info["formula2"] = str(validation.Formula2) except: pass try: if hasattr(validation, 'ErrorMessage'): validation_info["error_message"] = validation.ErrorMessage except: pass try: if hasattr(validation, 'InputMessage'): validation_info["input_message"] = validation.InputMessage except: pass try: if hasattr(validation, 'ShowError'): validation_info["show_error"] = bool(validation.ShowError) except: pass try: if hasattr(validation, 'ShowInput'): validation_info["show_input"] = bool(validation.ShowInput) except: pass # Try to find the full range with this validation # by checking adjacent cells full_range = expand_validation_range(sheet, row, col, validation) validation_info["range"] = full_range # Mark cells as processed for r in range(row, row + 10): for c in range(col, col + 10): processed_ranges.add(f"{chr(64+c)}{r}") validation_rules.append(validation_info) except Exception as e: # Cell might not have validation, continue continue except Exception as e: logger.warning(f"Error scanning for validation rules: {e}") # Return validation information result = { "sheet": sheet_name, "validation_count": len(validation_rules), "validation_rules": validation_rules } logger.info(f"✅ Found {len(validation_rules)} validation rules in {sheet_name}") return result except Exception as e: logger.error(f"Error getting validation info: {e}") return {"error": str(e)} - Helper function to map Excel validation type integers to human-readable names.
def get_validation_type_name(type_value: int) -> str: """Convert Excel validation type constant to readable name.""" validation_types = { 0: "None", 1: "Whole Number", 2: "Decimal", 3: "List", 4: "Date", 5: "Time", 6: "Text Length", 7: "Custom" } return validation_types.get(type_value, f"Unknown ({type_value})") - Helper function to map Excel validation operator integers to human-readable names.
def get_operator_name(operator_value: int) -> str: """Convert Excel validation operator constant to readable name.""" operators = { 1: "Between", 2: "Not Between", 3: "Equal", 4: "Not Equal", 5: "Greater", 6: "Less", 7: "Greater or Equal", 8: "Less or Equal" } return operators.get(operator_value, f"Unknown ({operator_value})")