read_data_from_excel
Extract structured data from Excel sheets, including cell values, metadata, and validation rules. Specify file path, sheet name, and cell range to retrieve JSON-formatted data for analysis or integration.
Instructions
Read data from Excel worksheet with cell metadata including validation rules.
Args:
filepath: Path to Excel file
sheet_name: Name of worksheet
start_cell: Starting cell (default A1)
end_cell: Ending cell (optional, auto-expands if not provided)
preview_only: Whether to return preview only
Returns:
JSON string containing structured cell data with validation metadata.
Each cell includes: address, value, row, column, and validation info (if any).
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| end_cell | No | ||
| filepath | Yes | ||
| preview_only | No | ||
| sheet_name | Yes | ||
| start_cell | No |
Input Schema (JSON Schema)
{
"properties": {
"end_cell": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"title": "End Cell"
},
"filepath": {
"title": "Filepath",
"type": "string"
},
"preview_only": {
"default": false,
"title": "Preview Only",
"type": "boolean"
},
"sheet_name": {
"title": "Sheet Name",
"type": "string"
},
"start_cell": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"title": "Start Cell"
}
},
"required": [
"filepath",
"sheet_name"
],
"title": "read_data_from_excelArguments",
"type": "object"
}
Implementation Reference
- src/xlwings_mcp/server.py:434-467 (registration)MCP tool registration (@mcp.tool()) and primary handler wrapper. Validates session, acquires lock, imports and calls the core implementation with workbook object.@mcp.tool() def read_data_from_excel( session_id: str, sheet_name: str, start_cell: Optional[str] = None, end_cell: Optional[str] = None, preview_only: bool = False ) -> str: """ Read data from Excel worksheet with cell metadata including validation rules. Args: session_id: Session ID from open_workbook (required) sheet_name: Name of worksheet start_cell: Starting cell (default A1) end_cell: Ending cell (optional, auto-expands if not provided) preview_only: Whether to return preview only """ try: # Validate session using centralized helper session = get_validated_session(session_id) if isinstance(session, str): # Error message returned return session with session.lock: from xlwings_mcp.xlwings_impl.data_xlw import read_data_from_excel_xlw_with_wb return read_data_from_excel_xlw_with_wb(session.workbook, sheet_name, start_cell, end_cell, preview_only) except (ValidationError, DataError) as e: return f"Error: {str(e)}" except Exception as e: logger.error(f"Error reading data: {e}") raise
- Core handler implementation using xlwings. Reads data from specified sheet/range in the provided workbook object, structures as JSON with cell metadata (address, value, row, col). Handles range expansion, errors, etc.def read_data_from_excel_xlw_with_wb( wb, sheet_name: str, start_cell: str = "A1", end_cell: Optional[str] = None, preview_only: bool = False ) -> str: """xlwings 세션 기반 데이터 읽기 Args: wb: 워크북 객체 (세션에서 전달) sheet_name: 시트명 start_cell: 시작 셀 (기본값: A1) end_cell: 종료 셀 (선택사항, 자동 확장) preview_only: 미리보기 모드 (현재 미사용) Returns: JSON 형식의 문자열 - 셀 메타데이터와 함께 구조화된 데이터 """ try: # 시트 존재 확인 if sheet_name not in [s.name for s in wb.sheets]: return json.dumps({"error": f"Sheet '{sheet_name}' not found"}, indent=2) ws = wb.sheets[sheet_name] # Set default start_cell if not provided if not start_cell: # Find first non-empty cell or default to A1 used_range = ws.used_range if used_range: start_cell = used_range.address.split(":")[0].replace("$", "") else: start_cell = "A1" # 범위 결정 if end_cell: # 명시적 범위 사용 data_range = ws.range(f"{start_cell}:{end_cell}") else: # 시작 셀부터 자동 확장 try: data_range = ws.range(start_cell).expand() except Exception: # 빈 시트이거나 단일 셀인 경우 data_range = ws.range(start_cell) # 데이터 읽기 values = data_range.value # 결과 구조 생성 result = { "range": str(data_range.address), "sheet_name": sheet_name, "cells": [] } # 셀별 데이터 변환 if values is None: # 단일 빈 셀 result["cells"].append({ "address": data_range.address, "value": None, "row": data_range.row, "column": data_range.column }) elif isinstance(values, list): # 다차원 배열 for i, row in enumerate(values): if isinstance(row, list): for j, val in enumerate(row): cell_range = data_range.offset(i, j).resize(1, 1) result["cells"].append({ "address": cell_range.address, "value": val, "row": cell_range.row, "column": cell_range.column }) else: # 단일 열의 경우 cell_range = data_range.offset(i, 0).resize(1, 1) result["cells"].append({ "address": cell_range.address, "value": row, "row": cell_range.row, "column": cell_range.column }) else: # 단일 값 result["cells"].append({ "address": data_range.address, "value": values, "row": data_range.row, "column": data_range.column }) return json.dumps(result, indent=2, default=str, ensure_ascii=False) except Exception as e: logger.error(f"xlwings 데이터 읽기 실패: {e}") return json.dumps({"error": f"Failed to read data: {str(e)}"}, indent=2)
- Legacy helper function that opens workbook from filepath directly (non-session). Similar logic to with_wb version but manages app/workbook lifecycle itself.def read_data_from_excel_xlw( filepath: str, sheet_name: str, start_cell: str = "A1", end_cell: Optional[str] = None, preview_only: bool = False ) -> str: """xlwings를 사용한 데이터 읽기 Args: filepath: Excel 파일 경로 sheet_name: 시트명 start_cell: 시작 셀 (기본값: A1) end_cell: 종료 셀 (선택사항, 자동 확장) preview_only: 미리보기 모드 (현재 미사용) Returns: JSON 형식의 문자열 - 셀 메타데이터와 함께 구조화된 데이터 """ app = None wb = None # Initialize COM for thread safety (Windows) _com_initialize() try: # Excel 앱 시작 (백그라운드에서) app = xw.App(visible=False, add_book=False) # 파일 경로 검증 if not os.path.exists(filepath): return json.dumps({"error": f"File not found: {filepath}"}, indent=2) # 워크북 열기 wb = app.books.open(filepath) # 시트 존재 확인 if sheet_name not in [s.name for s in wb.sheets]: return json.dumps({"error": f"Sheet '{sheet_name}' not found"}, indent=2) ws = wb.sheets[sheet_name] # Set default start_cell if not provided if not start_cell: # Find first non-empty cell or default to A1 used_range = ws.used_range if used_range: start_cell = used_range.address.split(":")[0].replace("$", "") else: start_cell = "A1" # 범위 결정 if end_cell: # 명시적 범위 사용 data_range = ws.range(f"{start_cell}:{end_cell}") else: # 시작 셀부터 자동 확장 try: data_range = ws.range(start_cell).expand() except Exception: # 빈 시트이거나 단일 셀인 경우 data_range = ws.range(start_cell) # 데이터 읽기 values = data_range.value # 결과 구조 생성 result = { "range": str(data_range.address), "sheet_name": sheet_name, "cells": [] } # 셀별 데이터 변환 if values is None: # 단일 빈 셀 result["cells"].append({ "address": data_range.address, "value": None, "row": data_range.row, "column": data_range.column }) elif isinstance(values, list): # 다차원 배열 for i, row in enumerate(values): if isinstance(row, list): for j, val in enumerate(row): cell_range = data_range.offset(i, j).resize(1, 1) result["cells"].append({ "address": cell_range.address, "value": val, "row": cell_range.row, "column": cell_range.column }) else: # 단일 열의 경우 cell_range = data_range.offset(i, 0).resize(1, 1) result["cells"].append({ "address": cell_range.address, "value": row, "row": cell_range.row, "column": cell_range.column }) else: # 단일 값 result["cells"].append({ "address": data_range.address, "value": values, "row": data_range.row, "column": data_range.column }) return json.dumps(result, indent=2, default=str, ensure_ascii=False) except Exception as e: logger.error(f"xlwings 데이터 읽기 실패: {e}") return json.dumps({"error": f"Failed to read data: {str(e)}"}, indent=2) 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}")