get_data_validation_info
Retrieve and analyze data validation rules in an Excel worksheet. Identify cell ranges and validation types by providing the file path and sheet name, returning results in JSON format.
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:
filepath: Path to Excel file
sheet_name: Name of worksheet
Returns:
JSON string containing all validation rules in the worksheet
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| filepath | Yes | ||
| sheet_name | Yes |
Input Schema (JSON Schema)
{
"properties": {
"filepath": {
"title": "Filepath",
"type": "string"
},
"sheet_name": {
"title": "Sheet Name",
"type": "string"
}
},
"required": [
"filepath",
"sheet_name"
],
"title": "get_data_validation_infoArguments",
"type": "object"
}
Implementation Reference
- src/xlwings_mcp/server.py:1301-1358 (registration)Registration of the 'get_data_validation_info' MCP tool. Dispatches to xlwings_impl handlers based on session_id or filepath.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 function for get_data_validation_info (legacy filepath mode). Opens workbook, scans used range for cells with data validation using xlwings COM API, extracts rule details like type, operator, formulas, messages.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-based handler for get_data_validation_info. Uses existing open workbook object, same validation scanning logic as primary handler.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 functions to convert Excel validation Type/Operator constants to human-readable names and expand single-cell validation to full range (simplified). Used by both handlers.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})") 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})") def expand_validation_range(sheet, start_row: int, start_col: int, validation) -> str: """ Try to find the full range that has the same validation rule. This is a simplified version - checks a limited area around the found cell. """ try: # For simplicity, we'll just return the single cell # In a production version, you'd want to check adjacent cells # to find the full range with the same validation cell = sheet.range((start_row, start_col)) return cell.address.replace('$', '') except: return f"{chr(64+start_col)}{start_row}"