read_data_from_excel
Extract data from Excel worksheets with cell metadata and validation rules for analysis or processing in corporate environments.
Instructions
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
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| session_id | Yes | ||
| sheet_name | Yes | ||
| start_cell | No | ||
| end_cell | No | ||
| preview_only | No |
Implementation Reference
- src/xlwings_mcp/server.py:435-467 (handler)MCP tool handler for 'read_data_from_excel'. Validates the session, locks it for thread-safety, imports and calls the xlwings implementation function.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 helper function that performs the actual Excel data reading using xlwings. Handles range expansion, data extraction, and JSON serialization with cell metadata.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)
- src/xlwings_mcp/server.py:435-435 (registration)The @mcp.tool() decorator registers this function as an MCP tool named 'read_data_from_excel'.def read_data_from_excel(
- Legacy helper function for reading data without pre-opened workbook (opens Excel app and workbook).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}")