Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbb537b3

Browse files
feat(cell validation): add comprehensive data validation capabilities (#37)
- Add cell_validation.py module for Excel data validation metadata extraction- Implement get_data_validation_for_cell() and get_all_validation_ranges()- Include validation metadata in read_data_from_excel responses automatically- Add get_data_validation_info MCP tool for validation rule summaries- Resolve range references in list validations to actual cell values- Support all validation types: list, whole, decimal, date, time, textLength- Include operators (between, notBetween, equal, greaterThan, etc.) in metadataThis allows LLMs to understand Excel validation constraints includingdropdown options, numeric ranges, date constraints, and text length limits.
1 parentb2c9ce8 commitbb537b3

File tree

5 files changed

+393
-19
lines changed

5 files changed

+393
-19
lines changed

‎TOOLS.md

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -337,3 +337,23 @@ validate_excel_range(
337337
-`start_cell`: Starting cell of range
338338
-`end_cell`: Optional ending cell of range
339339
- Returns: Validation result message
340+
341+
###get_data_validation_info
342+
343+
Get data validation rules and metadata for a worksheet.
344+
345+
```python
346+
get_data_validation_info(filepath:str, sheet_name:str)->str
347+
```
348+
349+
-`filepath`: Path to Excel file
350+
-`sheet_name`: Target worksheet name
351+
- Returns: JSON string containing all data validation rules with metadata including:
352+
- Validation type (list, whole, decimal, date, time, textLength)
353+
- Operator (between, notBetween, equal, greaterThan, lessThan, etc.)
354+
- Allowed values for list validations (resolved from ranges)
355+
- Formula constraints for numeric/date validations
356+
- Cell ranges where validation applies
357+
- Prompt and error messages
358+
359+
**Note**: The`read_data_from_excel` tool automatically includes validation metadata for individual cells when available.

‎src/excel_mcp/cell_validation.py

Lines changed: 179 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,179 @@
1+
importlogging
2+
fromtypingimportAny,Dict,List,Optional
3+
4+
fromopenpyxl.worksheet.worksheetimportWorksheet
5+
fromopenpyxl.utils.cellimportcoordinate_from_string,column_index_from_string
6+
7+
logger=logging.getLogger(__name__)
8+
9+
defget_data_validation_for_cell(worksheet:Worksheet,cell_address:str)->Optional[Dict[str,Any]]:
10+
"""Get data validation metadata for a specific cell.
11+
12+
Args:
13+
worksheet: The openpyxl worksheet object
14+
cell_address: Cell address like 'A1', 'B2', etc.
15+
16+
Returns:
17+
Dictionary with validation metadata or None if no validation exists
18+
"""
19+
try:
20+
# Convert cell address to row/col coordinates
21+
col_letter,row=coordinate_from_string(cell_address)
22+
col_idx=column_index_from_string(col_letter)
23+
24+
# Check each data validation rule in the worksheet
25+
fordvinworksheet.data_validations.dataValidation:
26+
# Check if this cell is covered by the validation rule
27+
if_cell_in_validation_range(row,col_idx,dv):
28+
return_extract_validation_metadata(dv,cell_address,worksheet)
29+
30+
returnNone
31+
32+
exceptExceptionase:
33+
logger.warning(f"Failed to get validation for cell{cell_address}:{e}")
34+
returnNone
35+
36+
def_cell_in_validation_range(row:int,col:int,data_validation)->bool:
37+
"""Check if a cell is within a data validation range."""
38+
try:
39+
# data_validation.sqref contains the cell ranges this validation applies to
40+
forcell_rangeindata_validation.sqref.ranges:
41+
if (cell_range.min_row<=row<=cell_range.max_rowand
42+
cell_range.min_col<=col<=cell_range.max_col):
43+
returnTrue
44+
returnFalse
45+
exceptExceptionase:
46+
logger.warning(f"Error checking if cell ({row},{col}) is in validation range for DV sqref '{getattr(data_validation,'sqref','N/A')}':{e}")
47+
returnFalse
48+
49+
def_extract_validation_metadata(data_validation,cell_address:str,worksheet:Optional[Worksheet]=None)->Dict[str,Any]:
50+
"""Extract metadata from a DataValidation object."""
51+
try:
52+
validation_info= {
53+
"cell":cell_address,
54+
"has_validation":True,
55+
"validation_type":data_validation.type,
56+
"allow_blank":data_validation.allowBlank,
57+
}
58+
59+
# Add operator for validation types that use it
60+
ifdata_validation.operator:
61+
validation_info["operator"]=data_validation.operator
62+
63+
# Add optional fields if they exist
64+
ifdata_validation.prompt:
65+
validation_info["prompt"]=data_validation.prompt
66+
ifdata_validation.promptTitle:
67+
validation_info["prompt_title"]=data_validation.promptTitle
68+
ifdata_validation.error:
69+
validation_info["error_message"]=data_validation.error
70+
ifdata_validation.errorTitle:
71+
validation_info["error_title"]=data_validation.errorTitle
72+
73+
# For list type validations (dropdown lists), extract allowed values
74+
ifdata_validation.type=="list"anddata_validation.formula1:
75+
allowed_values=_extract_list_values(data_validation.formula1,worksheet)
76+
validation_info["allowed_values"]=allowed_values
77+
78+
# For other validation types, include the formulas
79+
elifdata_validation.formula1:
80+
validation_info["formula1"]=data_validation.formula1
81+
ifdata_validation.formula2:
82+
validation_info["formula2"]=data_validation.formula2
83+
84+
returnvalidation_info
85+
86+
exceptExceptionase:
87+
logger.warning(f"Failed to extract validation metadata:{e}")
88+
return {
89+
"cell":cell_address,
90+
"has_validation":True,
91+
"validation_type":"unknown",
92+
"error":f"Failed to parse validation:{e}"
93+
}
94+
95+
def_extract_list_values(formula:str,worksheet:Optional[Worksheet]=None)->List[str]:
96+
"""Extract allowed values from a list validation formula."""
97+
try:
98+
# Remove quotes if present
99+
formula=formula.strip('"')
100+
101+
# Handle comma-separated list
102+
if','informula:
103+
# Split by comma and clean up each value
104+
values= [val.strip().strip('"')forvalinformula.split(',')]
105+
return [valforvalinvaluesifval]# Remove empty values
106+
107+
# Handle range reference (e.g., "$A$1:$A$5" or "Sheet1!$A$1:$A$5")
108+
elif (':'informulaorformula.startswith('$'))andworksheet:
109+
try:
110+
# Remove potential leading '=' if it's a formula like '=Sheet1!$A$1:$A$5'
111+
range_ref=formula
112+
ifformula.startswith('='):
113+
range_ref=formula[1:]
114+
115+
actual_values= []
116+
# worksheet[range_ref] can resolve ranges like "A1:A5" or "SheetName!A1:A5"
117+
# It returns a tuple of tuples of cells for ranges, or a single cell
118+
range_cells=worksheet[range_ref]
119+
120+
# Handle single cell or range
121+
ifhasattr(range_cells,'value'):# Single cell
122+
ifrange_cells.valueisnotNone:
123+
actual_values.append(str(range_cells.value))
124+
else:# Range of cells
125+
forrow_of_cellsinrange_cells:
126+
# Handle case where row_of_cells might be a single cell
127+
ifhasattr(row_of_cells,'value'):
128+
ifrow_of_cells.valueisnotNone:
129+
actual_values.append(str(row_of_cells.value))
130+
else:
131+
forcellinrow_of_cells:
132+
ifcell.valueisnotNone:
133+
actual_values.append(str(cell.value))
134+
135+
ifactual_values:
136+
returnactual_values
137+
return [f"Range:{formula} (empty or unresolvable)"]
138+
139+
exceptExceptionase:
140+
logger.warning(f"Could not resolve range '{formula}' for list validation:{e}")
141+
return [f"Range:{formula} (resolution error)"]
142+
143+
# Handle range reference when worksheet not available
144+
elif':'informulaorformula.startswith('$'):
145+
return [f"Range:{formula}"]
146+
147+
# Single value
148+
else:
149+
return [formula.strip('"')]
150+
151+
exceptExceptionase:
152+
logger.warning(f"Failed to parse list formula '{formula}':{e}")
153+
return [formula]# Return original formula if parsing fails
154+
155+
defget_all_validation_ranges(worksheet:Worksheet)->List[Dict[str,Any]]:
156+
"""Get all data validation ranges in a worksheet.
157+
158+
Returns:
159+
List of dictionaries containing validation range information
160+
"""
161+
validations= []
162+
163+
try:
164+
fordvinworksheet.data_validations.dataValidation:
165+
validation_info= {
166+
"ranges":str(dv.sqref),
167+
"validation_type":dv.type,
168+
"allow_blank":dv.allowBlank,
169+
}
170+
171+
ifdv.type=="list"anddv.formula1:
172+
validation_info["allowed_values"]=_extract_list_values(dv.formula1,worksheet)
173+
174+
validations.append(validation_info)
175+
176+
exceptExceptionase:
177+
logger.warning(f"Failed to get validation ranges:{e}")
178+
179+
returnvalidations

‎src/excel_mcp/data.py

Lines changed: 107 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,14 @@
11
frompathlibimportPath
2-
fromtypingimportAny
2+
fromtypingimportAny,Dict
33
importlogging
44

55
fromopenpyxlimportload_workbook
6-
fromopenpyxl.stylesimportFont
76
fromopenpyxl.worksheet.worksheetimportWorksheet
87
fromopenpyxl.utilsimportget_column_letter
98

109
from .exceptionsimportDataError
1110
from .cell_utilsimportparse_cell_range
11+
from .cell_validationimportget_data_validation_for_cell
1212

1313
logger=logging.getLogger(__name__)
1414

@@ -244,3 +244,108 @@ def _write_data_to_worksheet(
244244
exceptExceptionase:
245245
logger.error(f"Failed to write worksheet data:{e}")
246246
raiseDataError(str(e))
247+
248+
defread_excel_range_with_metadata(
249+
filepath:Path|str,
250+
sheet_name:str,
251+
start_cell:str="A1",
252+
end_cell:str|None=None,
253+
include_validation:bool=True
254+
)->Dict[str,Any]:
255+
"""Read data from Excel range with cell metadata including validation rules.
256+
257+
Args:
258+
filepath: Path to Excel file
259+
sheet_name: Name of worksheet
260+
start_cell: Starting cell address
261+
end_cell: Ending cell address (optional)
262+
include_validation: Whether to include validation metadata
263+
264+
Returns:
265+
Dictionary containing structured cell data with metadata
266+
"""
267+
try:
268+
wb=load_workbook(filepath,read_only=False)
269+
270+
ifsheet_namenotinwb.sheetnames:
271+
raiseDataError(f"Sheet '{sheet_name}' not found")
272+
273+
ws=wb[sheet_name]
274+
275+
# Parse start cell
276+
if':'instart_cell:
277+
start_cell,end_cell=start_cell.split(':')
278+
279+
# Get start coordinates
280+
try:
281+
start_coords=parse_cell_range(f"{start_cell}:{start_cell}")
282+
ifnotstart_coordsornotall(coordisnotNoneforcoordinstart_coords[:2]):
283+
raiseDataError(f"Invalid start cell reference:{start_cell}")
284+
start_row,start_col=start_coords[0],start_coords[1]
285+
exceptValueErrorase:
286+
raiseDataError(f"Invalid start cell format:{str(e)}")
287+
288+
# Determine end coordinates
289+
ifend_cell:
290+
try:
291+
end_coords=parse_cell_range(f"{end_cell}:{end_cell}")
292+
ifnotend_coordsornotall(coordisnotNoneforcoordinend_coords[:2]):
293+
raiseDataError(f"Invalid end cell reference:{end_cell}")
294+
end_row,end_col=end_coords[0],end_coords[1]
295+
exceptValueErrorase:
296+
raiseDataError(f"Invalid end cell format:{str(e)}")
297+
else:
298+
# Dynamically expand range until all values are empty
299+
end_row,end_col=start_row,start_col
300+
whileend_row<=ws.max_rowandany(ws.cell(row=end_row,column=c).valueisnotNoneforcinrange(start_col,ws.max_column+1)):
301+
end_row+=1
302+
whileend_col<=ws.max_columnandany(ws.cell(row=r,column=end_col).valueisnotNoneforrinrange(start_row,ws.max_row+1)):
303+
end_col+=1
304+
end_row-=1# Adjust back to last non-empty row
305+
end_col-=1# Adjust back to last non-empty column
306+
307+
# Validate range bounds
308+
ifstart_row>ws.max_roworstart_col>ws.max_column:
309+
raiseDataError(
310+
f"Start cell out of bounds. Sheet dimensions are "
311+
f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
312+
)
313+
314+
# Build structured cell data
315+
range_data= {
316+
"range":f"{start_cell}:{get_column_letter(end_col)}{end_row}"ifend_cellelsestart_cell,
317+
"sheet_name":sheet_name,
318+
"cells": []
319+
}
320+
321+
forrowinrange(start_row,end_row+1):
322+
forcolinrange(start_col,end_col+1):
323+
cell=ws.cell(row=row,column=col)
324+
cell_address=f"{get_column_letter(col)}{row}"
325+
326+
cell_data= {
327+
"address":cell_address,
328+
"value":cell.value,
329+
"row":row,
330+
"column":col
331+
}
332+
333+
# Add validation metadata if requested
334+
ifinclude_validation:
335+
validation_info=get_data_validation_for_cell(ws,cell_address)
336+
ifvalidation_info:
337+
cell_data["validation"]=validation_info
338+
else:
339+
cell_data["validation"]= {"has_validation":False}
340+
341+
range_data["cells"].append(cell_data)
342+
343+
wb.close()
344+
returnrange_data
345+
346+
exceptDataErrorase:
347+
logger.error(str(e))
348+
raise
349+
exceptExceptionase:
350+
logger.error(f"Failed to read Excel range with metadata:{e}")
351+
raiseDataError(str(e))

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp