get_data_validation_info
Extract and analyze data validation rules from an Excel worksheet to identify cell ranges and applied validation types. Returns results in JSON format for detailed insights.
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
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| filepath | Yes | ||
| sheet_name | Yes |
Implementation Reference
- src/excel_mcp/server.py:555-598 (handler)The handler function decorated with @mcp.tool(), implementing the core logic to load the workbook, retrieve validation rules using get_all_validation_ranges helper, and return JSON formatted results. This decorator also serves as the tool registration.@mcp.tool() def get_data_validation_info( filepath: str, sheet_name: str ) -> 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: filepath: Path to Excel file sheet_name: Name of worksheet Returns: JSON string containing all validation rules in the worksheet """ try: full_path = get_excel_path(filepath) from openpyxl import load_workbook from excel_mcp.cell_validation import get_all_validation_ranges wb = load_workbook(full_path, read_only=False) if sheet_name not in wb.sheetnames: return f"Error: Sheet '{sheet_name}' not found" ws = wb[sheet_name] validations = get_all_validation_ranges(ws) wb.close() if not validations: return "No data validation rules found in this worksheet" import json return json.dumps({ "sheet_name": sheet_name, "validation_rules": validations }, indent=2, default=str) except Exception as e: logger.error(f"Error getting validation info: {e}") raise
- Helper function that iterates through all data validations in the worksheet, extracts key metadata including ranges, type, and for list validations the allowed values, returning a list of validation info dictionaries.def get_all_validation_ranges(worksheet: Worksheet) -> List[Dict[str, Any]]: """Get all data validation ranges in a worksheet. Returns: List of dictionaries containing validation range information """ validations = [] try: for dv in worksheet.data_validations.dataValidation: validation_info = { "ranges": str(dv.sqref), "validation_type": dv.type, "allow_blank": dv.allowBlank, } if dv.type == "list" and dv.formula1: validation_info["allowed_values"] = _extract_list_values(dv.formula1, worksheet) validations.append(validation_info) except Exception as e: logger.warning(f"Failed to get validation ranges: {e}") return validations