# Directory Structure
```
├── .gitignore
├── .python-version
├── Dockerfile
├── LICENSE
├── pyproject.toml
├── README.md
├── smithery.yaml
├── src
│ └── excel_mcp
│ ├── __main__.py
│ ├── calculations.py
│ ├── cell_utils.py
│ ├── chart.py
│ ├── data.py
│ ├── exceptions.py
│ ├── formatting.py
│ ├── pivot.py
│ ├── server.py
│ ├── sheet.py
│ ├── validation.py
│ └── workbook.py
├── TOOLS.md
└── uv.lock
```
# Files
--------------------------------------------------------------------------------
/.python-version:
--------------------------------------------------------------------------------
```
1 | 3.12
```
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | # Build and Distribution
2 | __pycache__/
3 | *.py[cod]
4 | build/
5 | dist/
6 | src/*.egg-info/
7 |
8 | # Development Environment
9 | .venv/
10 | .env
11 |
12 | # IDE
13 | .vscode/
14 | .idea/
15 | .cursor/
16 | .cursorignore
17 | .cursorrules
18 | .specstory
19 |
20 | # Testing and Linting
21 | .coverage
22 | .pytest_cache/
23 | .ruff_cache/
24 | .mypy_cache/
25 | htmlcov/
26 | tests/
27 |
28 | # Project Files
29 | extras/
30 | .notes/
31 | logs/
32 | output/
33 | *.xlsx
34 | *.xls
35 | *.log
36 | excel_files/
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Excel MCP Server
2 | [](https://smithery.ai/server/@haris-musa/excel-mcp-server)
3 |
4 | A Model Context Protocol (MCP) server implementation that provides Excel file manipulation capabilities without requiring Microsoft Excel installation. This server enables workbook creation, data manipulation, formatting, and advanced Excel features.
5 |
6 | ## Requirements
7 |
8 | - Python 3.10+
9 | - MCP SDK 1.2.0+
10 | - OpenPyXL 3.1.2+
11 |
12 | ## Components
13 |
14 | ### Resources
15 |
16 | The server provides Excel workbook manipulation through OpenPyXL:
17 |
18 | - Creates and modifies Excel workbooks
19 | - Manages worksheets and ranges
20 | - Handles formatting and styles
21 | - Supports charts and pivot tables
22 |
23 | ### Tools
24 |
25 | This server provides a comprehensive set of Excel manipulation tools. For detailed documentation of all available tools, their parameters, and usage examples, please refer to [TOOLS.md](TOOLS.md).
26 |
27 | The tools include capabilities for:
28 |
29 | - Workbook and worksheet management
30 | - Data reading and writing
31 | - Formatting and styling
32 | - Charts and visualizations
33 | - Pivot tables and data analysis
34 |
35 | See [TOOLS.md](TOOLS.md) for complete documentation.
36 |
37 | ## Features
38 |
39 | - Full Excel Support: Comprehensive Excel functionality
40 | - Data Manipulation: Read, write, and transform data
41 | - Advanced Features: Charts, pivot tables, and formatting
42 | - Error Handling: Comprehensive error handling with clear messages
43 |
44 | ## Usage
45 |
46 | ### Environment Configuration
47 |
48 | The server can be configured using the following environment variables:
49 |
50 | - `EXCEL_FILES_PATH`: Directory where Excel files will be stored (default: `./excel_files`)
51 |
52 | You can set this in different ways:
53 |
54 | Windows CMD:
55 |
56 | ```cmd
57 | set EXCEL_FILES_PATH=C:\path\to\excel\files
58 | uv run excel-mcp-server
59 | ```
60 |
61 | Windows PowerShell:
62 |
63 | ```powershell
64 | $env:EXCEL_FILES_PATH="C:\path\to\excel\files"
65 | uv run excel-mcp-server
66 | ```
67 |
68 | Linux/MacOS:
69 |
70 | ```bash
71 | export EXCEL_FILES_PATH=/path/to/excel/files
72 | uv run excel-mcp-server
73 | ```
74 |
75 | Or in Claude Desktop config:
76 |
77 | ```json
78 | {
79 | "mcpServers": {
80 | "excel": {
81 | "command": "uv run excel-mcp-server",
82 | "transport": "sse",
83 | "env": {
84 | "EXCEL_FILES_PATH": "/path/to/excel/files"
85 | }
86 | }
87 | }
88 | }
89 | ```
90 |
91 | ### Starting the Server
92 |
93 | Start the server:
94 |
95 | ```bash
96 | uv run excel-mcp-server
97 | ```
98 |
99 | The server will start in SSE mode and wait for connections from MCP clients.
100 |
101 | ### Connecting in Cursor IDE
102 |
103 | After starting the server, connect to the SSE endpoint in Cursor IDE:
104 |
105 | ```
106 | http://localhost:8000/sse
107 | ```
108 |
109 | The Excel MCP tools will be available through the agent.
110 |
111 | For available tools and their usage, please refer to [TOOLS.md](TOOLS.md).
112 |
113 | ## License
114 |
115 | This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
116 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/__main__.py:
--------------------------------------------------------------------------------
```python
1 | import asyncio
2 | from .server import run_server
3 |
4 | def main():
5 | """Start the Excel MCP server."""
6 | try:
7 | print("Excel MCP Server")
8 | print("---------------")
9 | print("Starting server... Press Ctrl+C to exit")
10 | asyncio.run(run_server())
11 | except KeyboardInterrupt:
12 | print("\nShutting down server...")
13 | except Exception as e:
14 | print(f"\nError: {e}")
15 | import traceback
16 | traceback.print_exc()
17 | finally:
18 | print("Server stopped.")
19 |
20 | if __name__ == "__main__":
21 | main()
```
--------------------------------------------------------------------------------
/pyproject.toml:
--------------------------------------------------------------------------------
```toml
1 | [project]
2 | name = "excel-mcp-server"
3 | version = "0.1.0"
4 | description = "MCP server for Excel file manipulation"
5 | readme = "README.md"
6 | requires-python = ">=3.10"
7 | dependencies = [
8 | "mcp[cli]>=1.2.0",
9 | "openpyxl>=3.1.2",
10 | "requests>=2.28.0"
11 | ]
12 | [[project.authors]]
13 | name = "haris"
14 | email = "[email protected]"
15 |
16 | [build-system]
17 | requires = ["hatchling"]
18 | build-backend = "hatchling.build"
19 |
20 | [project.scripts]
21 | excel-mcp-server = "excel_mcp.__main__:main"
22 |
23 | [tool.hatch.build.targets.wheel]
24 | packages = ["src/excel_mcp"]
25 |
26 | [tool.hatch.build]
27 | packages = ["src/excel_mcp"]
```
--------------------------------------------------------------------------------
/smithery.yaml:
--------------------------------------------------------------------------------
```yaml
1 | # Smithery configuration file: https://smithery.ai/docs/config#smitheryyaml
2 |
3 | startCommand:
4 | type: stdio
5 | configSchema:
6 | # JSON Schema defining the configuration options for the MCP.
7 | type: object
8 | required: []
9 | properties:
10 | excelFilesPath:
11 | type: string
12 | description: Directory where Excel files will be stored.
13 | commandFunction:
14 | # A function that produces the CLI command to start the MCP on stdio.
15 | |-
16 | config => ({ command: 'uv', args: ['run', 'excel-mcp-server'], env: { EXCEL_FILES_PATH: config.excelFilesPath || './excel_files' } })
17 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/exceptions.py:
--------------------------------------------------------------------------------
```python
1 | class ExcelMCPError(Exception):
2 | """Base exception for Excel MCP errors."""
3 | pass
4 |
5 | class WorkbookError(ExcelMCPError):
6 | """Raised when workbook operations fail."""
7 | pass
8 |
9 | class SheetError(ExcelMCPError):
10 | """Raised when sheet operations fail."""
11 | pass
12 |
13 | class DataError(ExcelMCPError):
14 | """Raised when data operations fail."""
15 | pass
16 |
17 | class ValidationError(ExcelMCPError):
18 | """Raised when validation fails."""
19 | pass
20 |
21 | class FormattingError(ExcelMCPError):
22 | """Raised when formatting operations fail."""
23 | pass
24 |
25 | class CalculationError(ExcelMCPError):
26 | """Raised when formula calculations fail."""
27 | pass
28 |
29 | class PivotError(ExcelMCPError):
30 | """Raised when pivot table operations fail."""
31 | pass
32 |
33 | class ChartError(ExcelMCPError):
34 | """Raised when chart operations fail."""
35 | pass
36 |
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
1 | # Generated by https://smithery.ai. See: https://smithery.ai/docs/config#dockerfile
2 | # Use a Python image with uv pre-installed
3 | FROM ghcr.io/astral-sh/uv:python3.12-bookworm-slim AS uv
4 |
5 | # Install the project into /app
6 | WORKDIR /app
7 |
8 | # Enable bytecode compilation
9 | ENV UV_COMPILE_BYTECODE=1
10 |
11 | # Copy from the cache instead of linking since it's a mounted volume
12 | ENV UV_LINK_MODE=copy
13 |
14 | # Install the project's dependencies using the lockfile and settings
15 | RUN --mount=type=cache,target=/root/.cache/uv --mount=type=bind,source=uv.lock,target=uv.lock --mount=type=bind,source=pyproject.toml,target=pyproject.toml uv sync --frozen --no-install-project --no-dev --no-editable
16 |
17 | # Then, add the rest of the project source code and install it
18 | # Installing separately from its dependencies allows optimal layer caching
19 | ADD . /app
20 | RUN --mount=type=cache,target=/root/.cache/uv uv sync --frozen --no-dev --no-editable
21 |
22 | FROM python:3.12-slim-bookworm
23 |
24 | WORKDIR /app
25 |
26 | COPY --from=uv /root/.local /root/.local
27 | COPY --from=uv --chown=app:app /app/.venv /app/.venv
28 |
29 | # Place executables in the environment at the front of the path
30 | ENV PATH="/app/.venv/bin:$PATH"
31 |
32 | # when running the container, add --db-path and a bind mount to the host's db file
33 | ENTRYPOINT ["uv", "run", "excel-mcp-server"]
34 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/cell_utils.py:
--------------------------------------------------------------------------------
```python
1 | import re
2 |
3 | from openpyxl.utils import column_index_from_string
4 |
5 | def parse_cell_range(
6 | cell_ref: str,
7 | end_ref: str | None = None
8 | ) -> tuple[int, int, int | None, int | None]:
9 | """Parse Excel cell reference into row and column indices."""
10 | if end_ref:
11 | start_cell = cell_ref
12 | end_cell = end_ref
13 | else:
14 | start_cell = cell_ref
15 | end_cell = None
16 |
17 | match = re.match(r"([A-Z]+)([0-9]+)", start_cell.upper())
18 | if not match:
19 | raise ValueError(f"Invalid cell reference: {start_cell}")
20 | col_str, row_str = match.groups()
21 | start_row = int(row_str)
22 | start_col = column_index_from_string(col_str)
23 |
24 | if end_cell:
25 | match = re.match(r"([A-Z]+)([0-9]+)", end_cell.upper())
26 | if not match:
27 | raise ValueError(f"Invalid cell reference: {end_cell}")
28 | col_str, row_str = match.groups()
29 | end_row = int(row_str)
30 | end_col = column_index_from_string(col_str)
31 | else:
32 | end_row = None
33 | end_col = None
34 |
35 | return start_row, start_col, end_row, end_col
36 |
37 | def validate_cell_reference(cell_ref: str) -> bool:
38 | """Validate Excel cell reference format (e.g., 'A1', 'BC123')"""
39 | if not cell_ref:
40 | return False
41 |
42 | # Split into column and row parts
43 | col = row = ""
44 | for c in cell_ref:
45 | if c.isalpha():
46 | if row: # Letters after numbers not allowed
47 | return False
48 | col += c
49 | elif c.isdigit():
50 | row += c
51 | else:
52 | return False
53 |
54 | return bool(col and row)
```
--------------------------------------------------------------------------------
/src/excel_mcp/calculations.py:
--------------------------------------------------------------------------------
```python
1 | from typing import Any
2 | import logging
3 |
4 | from .workbook import get_or_create_workbook
5 | from .cell_utils import validate_cell_reference
6 | from .exceptions import ValidationError, CalculationError
7 | from .validation import validate_formula
8 |
9 | logger = logging.getLogger(__name__)
10 |
11 | def apply_formula(
12 | filepath: str,
13 | sheet_name: str,
14 | cell: str,
15 | formula: str
16 | ) -> dict[str, Any]:
17 | """Apply any Excel formula to a cell."""
18 | try:
19 | if not validate_cell_reference(cell):
20 | raise ValidationError(f"Invalid cell reference: {cell}")
21 |
22 | wb = get_or_create_workbook(filepath)
23 | if sheet_name not in wb.sheetnames:
24 | raise ValidationError(f"Sheet '{sheet_name}' not found")
25 |
26 | sheet = wb[sheet_name]
27 |
28 | # Ensure formula starts with =
29 | if not formula.startswith('='):
30 | formula = f'={formula}'
31 |
32 | # Validate formula syntax
33 | is_valid, message = validate_formula(formula)
34 | if not is_valid:
35 | raise CalculationError(f"Invalid formula syntax: {message}")
36 |
37 | try:
38 | # Apply formula to the cell
39 | cell_obj = sheet[cell]
40 | cell_obj.value = formula
41 | except Exception as e:
42 | raise CalculationError(f"Failed to apply formula to cell: {str(e)}")
43 |
44 | try:
45 | wb.save(filepath)
46 | except Exception as e:
47 | raise CalculationError(f"Failed to save workbook after applying formula: {str(e)}")
48 |
49 | return {
50 | "message": f"Applied formula '{formula}' to cell {cell}",
51 | "cell": cell,
52 | "formula": formula
53 | }
54 |
55 | except (ValidationError, CalculationError) as e:
56 | logger.error(str(e))
57 | raise
58 | except Exception as e:
59 | logger.error(f"Failed to apply formula: {e}")
60 | raise CalculationError(str(e))
```
--------------------------------------------------------------------------------
/src/excel_mcp/workbook.py:
--------------------------------------------------------------------------------
```python
1 | import logging
2 | import os
3 | from pathlib import Path
4 | from typing import Any
5 | import requests
6 |
7 | from openpyxl import Workbook, load_workbook
8 | from openpyxl.utils import get_column_letter
9 |
10 | from .exceptions import WorkbookError
11 |
12 | logger = logging.getLogger(__name__)
13 |
14 | # 静态资源服务器配置
15 | FILE_SERVER_URL = "http://localhost:3001"
16 | UPLOAD_ENDPOINT = f"{FILE_SERVER_URL}/upload"
17 | FILES_LIST_ENDPOINT = f"{FILE_SERVER_URL}/files/list"
18 | FILE_ACCESS_BASE_URL = f"{FILE_SERVER_URL}/files/"
19 |
20 | def upload_file_to_server(filepath: str) -> dict[str, Any]:
21 | """Upload a file to the static file server
22 |
23 | Args:
24 | filepath: Path to the file to upload
25 |
26 | Returns:
27 | Dictionary with upload result information including file URL
28 | """
29 | try:
30 | if not os.path.exists(filepath):
31 | raise WorkbookError(f"File not found: {filepath}")
32 |
33 | filename = os.path.basename(filepath)
34 |
35 | with open(filepath, 'rb') as file:
36 | files = {'file': (filename, file)}
37 | response = requests.post(UPLOAD_ENDPOINT, files=files)
38 |
39 | if response.status_code != 200:
40 | raise WorkbookError(f"Failed to upload file: {response.text}")
41 |
42 | # 构建文件访问URL
43 | file_url = f"{FILE_ACCESS_BASE_URL}{filename}"
44 |
45 | return {
46 | "message": f"File uploaded successfully",
47 | "file_url": file_url,
48 | "filename": filename
49 | }
50 | except Exception as e:
51 | logger.error(f"Failed to upload file: {e}")
52 | raise WorkbookError(f"Failed to upload file: {e!s}")
53 |
54 | def download_file_from_url(url: str, save_path: str) -> str:
55 | """Download a file from URL and save it to the specified path
56 |
57 | Args:
58 | url: URL of the file to download
59 | save_path: Path where to save the downloaded file
60 |
61 | Returns:
62 | Path to the downloaded file
63 | """
64 | try:
65 | response = requests.get(url, stream=True)
66 | response.raise_for_status()
67 |
68 | # Create directory if it doesn't exist
69 | os.makedirs(os.path.dirname(save_path), exist_ok=True)
70 |
71 | with open(save_path, 'wb') as file:
72 | for chunk in response.iter_content(chunk_size=8192):
73 | file.write(chunk)
74 |
75 | return save_path
76 | except Exception as e:
77 | logger.error(f"Failed to download file: {e}")
78 | raise WorkbookError(f"Failed to download file: {e!s}")
79 |
80 | def create_workbook(filepath: str, sheet_name: str = "Sheet1", upload: bool = False) -> dict[str, Any]:
81 | """Create a new Excel workbook with optional custom sheet name and upload to server"""
82 | try:
83 | wb = Workbook()
84 | # Rename default sheet
85 | if "Sheet" in wb.sheetnames:
86 | sheet = wb["Sheet"]
87 | sheet.title = sheet_name
88 | else:
89 | wb.create_sheet(sheet_name)
90 |
91 | path = Path(filepath)
92 | path.parent.mkdir(parents=True, exist_ok=True)
93 | wb.save(str(path))
94 |
95 | result = {
96 | "message": f"Created workbook: {filepath}",
97 | "active_sheet": sheet_name,
98 | "workbook": wb
99 | }
100 |
101 | # 如果需要上传文件
102 | if upload:
103 | upload_result = upload_file_to_server(filepath)
104 | result["file_url"] = upload_result["file_url"]
105 | result["message"] = f"Created and uploaded workbook: {filepath}. URL: {upload_result['file_url']}"
106 |
107 | return result
108 | except Exception as e:
109 | logger.error(f"Failed to create workbook: {e}")
110 | raise WorkbookError(f"Failed to create workbook: {e!s}")
111 |
112 | def get_or_create_workbook(filepath: str) -> Workbook:
113 | """Get existing workbook or create new one if it doesn't exist"""
114 | try:
115 | return load_workbook(filepath)
116 | except FileNotFoundError:
117 | return create_workbook(filepath)["workbook"]
118 |
119 | def create_sheet(filepath: str, sheet_name: str) -> dict:
120 | """Create a new worksheet in the workbook if it doesn't exist."""
121 | try:
122 | wb = load_workbook(filepath)
123 |
124 | # Check if sheet already exists
125 | if sheet_name in wb.sheetnames:
126 | raise WorkbookError(f"Sheet {sheet_name} already exists")
127 |
128 | # Create new sheet
129 | wb.create_sheet(sheet_name)
130 | wb.save(filepath)
131 | wb.close()
132 | return {"message": f"Sheet {sheet_name} created successfully"}
133 | except WorkbookError as e:
134 | logger.error(str(e))
135 | raise
136 | except Exception as e:
137 | logger.error(f"Failed to create sheet: {e}")
138 | raise WorkbookError(str(e))
139 |
140 | def get_workbook_info(filepath: str, include_ranges: bool = False) -> dict[str, Any]:
141 | """Get metadata about workbook including sheets, ranges, etc."""
142 | try:
143 | path = Path(filepath)
144 | if not path.exists():
145 | raise WorkbookError(f"File not found: {filepath}")
146 |
147 | wb = load_workbook(filepath, read_only=True)
148 |
149 | info = {
150 | "filename": path.name,
151 | "sheets": wb.sheetnames,
152 | "size": path.stat().st_size,
153 | "modified": path.stat().st_mtime
154 | }
155 |
156 | if include_ranges:
157 | # Add used ranges for each sheet
158 | ranges = {}
159 | for sheet_name in wb.sheetnames:
160 | ws = wb[sheet_name]
161 | if ws.max_row > 0 and ws.max_column > 0:
162 | ranges[sheet_name] = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
163 | info["used_ranges"] = ranges
164 |
165 | wb.close()
166 | return info
167 |
168 | except WorkbookError as e:
169 | logger.error(str(e))
170 | raise
171 | except Exception as e:
172 | logger.error(f"Failed to get workbook info: {e}")
173 | raise WorkbookError(str(e))
174 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/data.py:
--------------------------------------------------------------------------------
```python
1 | from pathlib import Path
2 | from typing import Any
3 | import logging
4 |
5 | from openpyxl import load_workbook
6 | from openpyxl.styles import Font
7 | from openpyxl.worksheet.worksheet import Worksheet
8 | from openpyxl.utils import get_column_letter
9 |
10 | from .exceptions import DataError
11 | from .cell_utils import parse_cell_range
12 |
13 | logger = logging.getLogger(__name__)
14 |
15 | def read_excel_range(
16 | filepath: Path | str,
17 | sheet_name: str,
18 | start_cell: str = "A1",
19 | end_cell: str | None = None,
20 | preview_only: bool = False
21 | ) -> list[dict[str, Any]]:
22 | """Read data from Excel range with optional preview mode"""
23 | try:
24 | wb = load_workbook(filepath, read_only=True)
25 |
26 | if sheet_name not in wb.sheetnames:
27 | raise DataError(f"Sheet '{sheet_name}' not found")
28 |
29 | ws = wb[sheet_name]
30 |
31 | # Parse start cell
32 | if ':' in start_cell:
33 | start_cell, end_cell = start_cell.split(':')
34 |
35 | # Get start coordinates
36 | try:
37 | start_coords = parse_cell_range(f"{start_cell}:{start_cell}")
38 | if not start_coords or not all(coord is not None for coord in start_coords[:2]):
39 | raise DataError(f"Invalid start cell reference: {start_cell}")
40 | start_row, start_col = start_coords[0], start_coords[1]
41 | except ValueError as e:
42 | raise DataError(f"Invalid start cell format: {str(e)}")
43 |
44 | # Determine end coordinates
45 | if end_cell:
46 | try:
47 | end_coords = parse_cell_range(f"{end_cell}:{end_cell}")
48 | if not end_coords or not all(coord is not None for coord in end_coords[:2]):
49 | raise DataError(f"Invalid end cell reference: {end_cell}")
50 | end_row, end_col = end_coords[0], end_coords[1]
51 | except ValueError as e:
52 | raise DataError(f"Invalid end cell format: {str(e)}")
53 | else:
54 | # For single cell, use same coordinates
55 | end_row, end_col = start_row, start_col
56 |
57 | # Validate range bounds
58 | if start_row > ws.max_row or start_col > ws.max_column:
59 | raise DataError(
60 | f"Start cell out of bounds. Sheet dimensions are "
61 | f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
62 | )
63 |
64 | data = []
65 | # If it's a single cell or single row, just read the values directly
66 | if start_row == end_row:
67 | row_data = {}
68 | for col in range(start_col, end_col + 1):
69 | cell = ws.cell(row=start_row, column=col)
70 | col_name = f"Column_{col}"
71 | row_data[col_name] = cell.value
72 | if any(v is not None for v in row_data.values()):
73 | data.append(row_data)
74 | else:
75 | # Multiple rows - use header row
76 | headers = []
77 | for col in range(start_col, end_col + 1):
78 | cell_value = ws.cell(row=start_row, column=col).value
79 | headers.append(str(cell_value) if cell_value is not None else f"Column_{col}")
80 |
81 | # Get data rows
82 | max_rows = min(start_row + 5, end_row) if preview_only else end_row
83 | for row in range(start_row + 1, max_rows + 1):
84 | row_data = {}
85 | for col, header in enumerate(headers, start=start_col):
86 | cell = ws.cell(row=row, column=col)
87 | row_data[header] = cell.value
88 | if any(v is not None for v in row_data.values()):
89 | data.append(row_data)
90 |
91 | wb.close()
92 | return data
93 | except DataError as e:
94 | logger.error(str(e))
95 | raise
96 | except Exception as e:
97 | logger.error(f"Failed to read Excel range: {e}")
98 | raise DataError(str(e))
99 |
100 | def write_data(
101 | filepath: str,
102 | sheet_name: str | None,
103 | data: list[dict[str, Any]] | None,
104 | start_cell: str = "A1",
105 | write_headers: bool = True,
106 | ) -> dict[str, str]:
107 | """Write data to Excel sheet with workbook handling"""
108 | try:
109 | if not data:
110 | raise DataError("No data provided to write")
111 |
112 | wb = load_workbook(filepath)
113 |
114 | # If no sheet specified, use active sheet
115 | if not sheet_name:
116 | sheet_name = wb.active.title
117 | elif sheet_name not in wb.sheetnames:
118 | wb.create_sheet(sheet_name)
119 |
120 | ws = wb[sheet_name]
121 |
122 | # Validate start cell
123 | try:
124 | start_coords = parse_cell_range(start_cell)
125 | if not start_coords or not all(coord is not None for coord in start_coords[:2]):
126 | raise DataError(f"Invalid start cell reference: {start_cell}")
127 | except ValueError as e:
128 | raise DataError(f"Invalid start cell format: {str(e)}")
129 |
130 | if len(data) > 0:
131 | # Check if first row of data contains headers
132 | first_row = data[0]
133 | has_headers = all(
134 | isinstance(value, str) and value.strip() == key.strip()
135 | for key, value in first_row.items()
136 | )
137 |
138 | # If first row contains headers, skip it when write_headers is True
139 | if has_headers and write_headers:
140 | data = data[1:]
141 |
142 | _write_data_to_worksheet(ws, data, start_cell, write_headers)
143 |
144 | wb.save(filepath)
145 | wb.close()
146 |
147 | return {"message": f"Data written to {sheet_name}", "active_sheet": sheet_name}
148 | except DataError as e:
149 | logger.error(str(e))
150 | raise
151 | except Exception as e:
152 | logger.error(f"Failed to write data: {e}")
153 | raise DataError(str(e))
154 |
155 | def _write_data_to_worksheet(
156 | worksheet: Worksheet,
157 | data: list[dict[str, Any]],
158 | start_cell: str = "A1",
159 | write_headers: bool = True,
160 | ) -> None:
161 | """Write data to worksheet - internal helper function"""
162 | try:
163 | if not data:
164 | raise DataError("No data provided to write")
165 |
166 | try:
167 | start_coords = parse_cell_range(start_cell)
168 | if not start_coords or not all(x is not None for x in start_coords[:2]):
169 | raise DataError(f"Invalid start cell reference: {start_cell}")
170 | start_row, start_col = start_coords[0], start_coords[1]
171 | except ValueError as e:
172 | raise DataError(f"Invalid start cell format: {str(e)}")
173 |
174 | # Validate data structure
175 | if not all(isinstance(row, dict) for row in data):
176 | raise DataError("All data rows must be dictionaries")
177 |
178 | # Write headers if requested
179 | headers = list(data[0].keys())
180 | if write_headers:
181 | for i, header in enumerate(headers):
182 | cell = worksheet.cell(row=start_row, column=start_col + i)
183 | cell.value = header
184 | cell.font = Font(bold=True)
185 | start_row += 1 # Move start row down if headers were written
186 |
187 | # Write data
188 | for i, row_dict in enumerate(data):
189 | if not all(h in row_dict for h in headers):
190 | raise DataError(f"Row {i+1} is missing required headers")
191 | for j, header in enumerate(headers):
192 | cell = worksheet.cell(row=start_row + i, column=start_col + j)
193 | cell.value = row_dict.get(header, "")
194 | except DataError as e:
195 | logger.error(str(e))
196 | raise
197 | except Exception as e:
198 | logger.error(f"Failed to write worksheet data: {e}")
199 | raise DataError(str(e))
200 |
```
--------------------------------------------------------------------------------
/TOOLS.md:
--------------------------------------------------------------------------------
```markdown
1 | # Excel MCP Server Tools
2 |
3 | This document provides detailed information about all available tools in the Excel MCP server.
4 |
5 | ## Workbook Operations
6 |
7 | ### create_workbook
8 |
9 | Creates a new Excel workbook.
10 |
11 | ```python
12 | create_workbook(filepath: str) -> str
13 | ```
14 |
15 | - `filepath`: Path where to create workbook
16 | - Returns: Success message with created file path
17 |
18 | ### create_worksheet
19 |
20 | Creates a new worksheet in an existing workbook.
21 |
22 | ```python
23 | create_worksheet(filepath: str, sheet_name: str) -> str
24 | ```
25 |
26 | - `filepath`: Path to Excel file
27 | - `sheet_name`: Name for the new worksheet
28 | - Returns: Success message
29 |
30 | ### get_workbook_metadata
31 |
32 | Get metadata about workbook including sheets and ranges.
33 |
34 | ```python
35 | get_workbook_metadata(filepath: str, include_ranges: bool = False) -> str
36 | ```
37 |
38 | - `filepath`: Path to Excel file
39 | - `include_ranges`: Whether to include range information
40 | - Returns: String representation of workbook metadata
41 |
42 | ## Data Operations
43 |
44 | ### write_data_to_excel
45 |
46 | Write data to Excel worksheet.
47 |
48 | ```python
49 | write_data_to_excel(
50 | filepath: str,
51 | sheet_name: str,
52 | data: List[Dict],
53 | start_cell: str = "A1",
54 | write_headers: bool = True
55 | ) -> str
56 | ```
57 |
58 | - `filepath`: Path to Excel file
59 | - `sheet_name`: Target worksheet name
60 | - `data`: List of dictionaries containing data to write
61 | - `start_cell`: Starting cell (default: "A1")
62 | - `write_headers`: Whether to write dictionary keys as headers
63 | - Returns: Success message
64 |
65 | ### read_data_from_excel
66 |
67 | Read data from Excel worksheet.
68 |
69 | ```python
70 | read_data_from_excel(
71 | filepath: str,
72 | sheet_name: str,
73 | start_cell: str = "A1",
74 | end_cell: str = None,
75 | preview_only: bool = False
76 | ) -> str
77 | ```
78 |
79 | - `filepath`: Path to Excel file
80 | - `sheet_name`: Source worksheet name
81 | - `start_cell`: Starting cell (default: "A1")
82 | - `end_cell`: Optional ending cell
83 | - `preview_only`: Whether to return only a preview
84 | - Returns: String representation of data
85 |
86 | ## Formatting Operations
87 |
88 | ### format_range
89 |
90 | Apply formatting to a range of cells.
91 |
92 | ```python
93 | format_range(
94 | filepath: str,
95 | sheet_name: str,
96 | start_cell: str,
97 | end_cell: str = None,
98 | bold: bool = False,
99 | italic: bool = False,
100 | underline: bool = False,
101 | font_size: int = None,
102 | font_color: str = None,
103 | bg_color: str = None,
104 | border_style: str = None,
105 | border_color: str = None,
106 | number_format: str = None,
107 | alignment: str = None,
108 | wrap_text: bool = False,
109 | merge_cells: bool = False,
110 | protection: Dict[str, Any] = None,
111 | conditional_format: Dict[str, Any] = None
112 | ) -> str
113 | ```
114 |
115 | - `filepath`: Path to Excel file
116 | - `sheet_name`: Target worksheet name
117 | - `start_cell`: Starting cell of range
118 | - `end_cell`: Optional ending cell of range
119 | - Various formatting options (see parameters)
120 | - Returns: Success message
121 |
122 | ### merge_cells
123 |
124 | Merge a range of cells.
125 |
126 | ```python
127 | merge_cells(filepath: str, sheet_name: str, start_cell: str, end_cell: str) -> str
128 | ```
129 |
130 | - `filepath`: Path to Excel file
131 | - `sheet_name`: Target worksheet name
132 | - `start_cell`: Starting cell of range
133 | - `end_cell`: Ending cell of range
134 | - Returns: Success message
135 |
136 | ### unmerge_cells
137 |
138 | Unmerge a previously merged range of cells.
139 |
140 | ```python
141 | unmerge_cells(filepath: str, sheet_name: str, start_cell: str, end_cell: str) -> str
142 | ```
143 |
144 | - `filepath`: Path to Excel file
145 | - `sheet_name`: Target worksheet name
146 | - `start_cell`: Starting cell of range
147 | - `end_cell`: Ending cell of range
148 | - Returns: Success message
149 |
150 | ## Formula Operations
151 |
152 | ### apply_formula
153 |
154 | Apply Excel formula to cell.
155 |
156 | ```python
157 | apply_formula(filepath: str, sheet_name: str, cell: str, formula: str) -> str
158 | ```
159 |
160 | - `filepath`: Path to Excel file
161 | - `sheet_name`: Target worksheet name
162 | - `cell`: Target cell reference
163 | - `formula`: Excel formula to apply
164 | - Returns: Success message
165 |
166 | ### validate_formula_syntax
167 |
168 | Validate Excel formula syntax without applying it.
169 |
170 | ```python
171 | validate_formula_syntax(filepath: str, sheet_name: str, cell: str, formula: str) -> str
172 | ```
173 |
174 | - `filepath`: Path to Excel file
175 | - `sheet_name`: Target worksheet name
176 | - `cell`: Target cell reference
177 | - `formula`: Excel formula to validate
178 | - Returns: Validation result message
179 |
180 | ## Chart Operations
181 |
182 | ### create_chart
183 |
184 | Create chart in worksheet.
185 |
186 | ```python
187 | create_chart(
188 | filepath: str,
189 | sheet_name: str,
190 | data_range: str,
191 | chart_type: str,
192 | target_cell: str,
193 | title: str = "",
194 | x_axis: str = "",
195 | y_axis: str = ""
196 | ) -> str
197 | ```
198 |
199 | - `filepath`: Path to Excel file
200 | - `sheet_name`: Target worksheet name
201 | - `data_range`: Range containing chart data
202 | - `chart_type`: Type of chart (line, bar, pie, scatter, area)
203 | - `target_cell`: Cell where to place chart
204 | - `title`: Optional chart title
205 | - `x_axis`: Optional X-axis label
206 | - `y_axis`: Optional Y-axis label
207 | - Returns: Success message
208 |
209 | ## Pivot Table Operations
210 |
211 | ### create_pivot_table
212 |
213 | Create pivot table in worksheet.
214 |
215 | ```python
216 | create_pivot_table(
217 | filepath: str,
218 | sheet_name: str,
219 | data_range: str,
220 | target_cell: str,
221 | rows: List[str],
222 | values: List[str],
223 | columns: List[str] = None,
224 | agg_func: str = "mean"
225 | ) -> str
226 | ```
227 |
228 | - `filepath`: Path to Excel file
229 | - `sheet_name`: Target worksheet name
230 | - `data_range`: Range containing source data
231 | - `target_cell`: Cell where to place pivot table
232 | - `rows`: Fields for row labels
233 | - `values`: Fields for values
234 | - `columns`: Optional fields for column labels
235 | - `agg_func`: Aggregation function (sum, count, average, max, min)
236 | - Returns: Success message
237 |
238 | ## Worksheet Operations
239 |
240 | ### copy_worksheet
241 |
242 | Copy worksheet within workbook.
243 |
244 | ```python
245 | copy_worksheet(filepath: str, source_sheet: str, target_sheet: str) -> str
246 | ```
247 |
248 | - `filepath`: Path to Excel file
249 | - `source_sheet`: Name of sheet to copy
250 | - `target_sheet`: Name for new sheet
251 | - Returns: Success message
252 |
253 | ### delete_worksheet
254 |
255 | Delete worksheet from workbook.
256 |
257 | ```python
258 | delete_worksheet(filepath: str, sheet_name: str) -> str
259 | ```
260 |
261 | - `filepath`: Path to Excel file
262 | - `sheet_name`: Name of sheet to delete
263 | - Returns: Success message
264 |
265 | ### rename_worksheet
266 |
267 | Rename worksheet in workbook.
268 |
269 | ```python
270 | rename_worksheet(filepath: str, old_name: str, new_name: str) -> str
271 | ```
272 |
273 | - `filepath`: Path to Excel file
274 | - `old_name`: Current sheet name
275 | - `new_name`: New sheet name
276 | - Returns: Success message
277 |
278 | ## Range Operations
279 |
280 | ### copy_range
281 |
282 | Copy a range of cells to another location.
283 |
284 | ```python
285 | copy_range(
286 | filepath: str,
287 | sheet_name: str,
288 | source_start: str,
289 | source_end: str,
290 | target_start: str,
291 | target_sheet: str = None
292 | ) -> str
293 | ```
294 |
295 | - `filepath`: Path to Excel file
296 | - `sheet_name`: Source worksheet name
297 | - `source_start`: Starting cell of source range
298 | - `source_end`: Ending cell of source range
299 | - `target_start`: Starting cell for paste
300 | - `target_sheet`: Optional target worksheet name
301 | - Returns: Success message
302 |
303 | ### delete_range
304 |
305 | Delete a range of cells and shift remaining cells.
306 |
307 | ```python
308 | delete_range(
309 | filepath: str,
310 | sheet_name: str,
311 | start_cell: str,
312 | end_cell: str,
313 | shift_direction: str = "up"
314 | ) -> str
315 | ```
316 |
317 | - `filepath`: Path to Excel file
318 | - `sheet_name`: Target worksheet name
319 | - `start_cell`: Starting cell of range
320 | - `end_cell`: Ending cell of range
321 | - `shift_direction`: Direction to shift cells ("up" or "left")
322 | - Returns: Success message
323 |
324 | ### validate_excel_range
325 |
326 | Validate if a range exists and is properly formatted.
327 |
328 | ```python
329 | validate_excel_range(
330 | filepath: str,
331 | sheet_name: str,
332 | start_cell: str,
333 | end_cell: str = None
334 | ) -> str
335 | ```
336 |
337 | - `filepath`: Path to Excel file
338 | - `sheet_name`: Target worksheet name
339 | - `start_cell`: Starting cell of range
340 | - `end_cell`: Optional ending cell of range
341 | - Returns: Validation result message
342 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/chart.py:
--------------------------------------------------------------------------------
```python
1 | from typing import Any, Optional, Dict
2 | import logging
3 | from enum import Enum
4 |
5 | from openpyxl import load_workbook
6 | from openpyxl.chart import (
7 | BarChart, LineChart, PieChart, ScatterChart,
8 | AreaChart, Reference, Series
9 | )
10 | from openpyxl.chart.label import DataLabelList
11 | from openpyxl.chart.legend import Legend
12 | from openpyxl.chart.axis import ChartLines
13 | from openpyxl.drawing.spreadsheet_drawing import (
14 | AnchorMarker, OneCellAnchor, SpreadsheetDrawing
15 | )
16 | from openpyxl.utils import column_index_from_string
17 |
18 | from .cell_utils import parse_cell_range
19 | from .exceptions import ValidationError, ChartError
20 |
21 | logger = logging.getLogger(__name__)
22 |
23 | class ChartType(str, Enum):
24 | """Supported chart types"""
25 | LINE = "line"
26 | BAR = "bar"
27 | PIE = "pie"
28 | SCATTER = "scatter"
29 | AREA = "area"
30 | BUBBLE = "bubble"
31 | STOCK = "stock"
32 | SURFACE = "surface"
33 | RADAR = "radar"
34 |
35 | class ChartStyle:
36 | """Chart style configuration"""
37 | def __init__(
38 | self,
39 | title_size: int = 14,
40 | title_bold: bool = True,
41 | axis_label_size: int = 12,
42 | show_legend: bool = True,
43 | legend_position: str = "r",
44 | show_data_labels: bool = True,
45 | grid_lines: bool = False,
46 | style_id: int = 2
47 | ):
48 | self.title_size = title_size
49 | self.title_bold = title_bold
50 | self.axis_label_size = axis_label_size
51 | self.show_legend = show_legend
52 | self.legend_position = legend_position
53 | self.show_data_labels = show_data_labels
54 | self.grid_lines = grid_lines
55 | self.style_id = style_id
56 |
57 | def create_chart_in_sheet(
58 | filepath: str,
59 | sheet_name: str,
60 | data_range: str,
61 | chart_type: str,
62 | target_cell: str,
63 | title: str = "",
64 | x_axis: str = "",
65 | y_axis: str = "",
66 | style: Optional[Dict] = None
67 | ) -> dict[str, Any]:
68 | """Create chart in sheet with enhanced styling options"""
69 | try:
70 | wb = load_workbook(filepath)
71 | if sheet_name not in wb.sheetnames:
72 | logger.error(f"Sheet '{sheet_name}' not found")
73 | raise ValidationError(f"Sheet '{sheet_name}' not found")
74 |
75 | worksheet = wb[sheet_name]
76 |
77 | # Initialize collections if they don't exist
78 | if not hasattr(worksheet, '_drawings'):
79 | worksheet._drawings = []
80 | if not hasattr(worksheet, '_charts'):
81 | worksheet._charts = []
82 |
83 | # Parse the data range
84 | if "!" in data_range:
85 | range_sheet_name, cell_range = data_range.split("!")
86 | if range_sheet_name not in wb.sheetnames:
87 | logger.error(f"Sheet '{range_sheet_name}' referenced in data range not found")
88 | raise ValidationError(f"Sheet '{range_sheet_name}' referenced in data range not found")
89 | else:
90 | cell_range = data_range
91 |
92 | try:
93 | start_cell, end_cell = cell_range.split(":")
94 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
95 | except ValueError as e:
96 | logger.error(f"Invalid data range format: {e}")
97 | raise ValidationError(f"Invalid data range format: {str(e)}")
98 |
99 | # Validate chart type
100 | chart_classes = {
101 | "line": LineChart,
102 | "bar": BarChart,
103 | "pie": PieChart,
104 | "scatter": ScatterChart,
105 | "area": AreaChart
106 | }
107 |
108 | chart_type_lower = chart_type.lower()
109 | ChartClass = chart_classes.get(chart_type_lower)
110 | if not ChartClass:
111 | logger.error(f"Unsupported chart type: {chart_type}")
112 | raise ValidationError(
113 | f"Unsupported chart type: {chart_type}. "
114 | f"Supported types: {', '.join(chart_classes.keys())}"
115 | )
116 |
117 | chart = ChartClass()
118 |
119 | # Basic chart settings
120 | chart.title = title
121 | if hasattr(chart, "x_axis"):
122 | chart.x_axis.title = x_axis
123 | if hasattr(chart, "y_axis"):
124 | chart.y_axis.title = y_axis
125 |
126 | try:
127 | # Create data references
128 | if chart_type_lower == "scatter":
129 | # For scatter charts, create series for each pair of columns
130 | for col in range(start_col + 1, end_col + 1):
131 | x_values = Reference(
132 | worksheet,
133 | min_row=start_row + 1,
134 | max_row=end_row,
135 | min_col=start_col
136 | )
137 | y_values = Reference(
138 | worksheet,
139 | min_row=start_row + 1,
140 | max_row=end_row,
141 | min_col=col
142 | )
143 | series = Series(y_values, x_values, title_from_data=True)
144 | chart.series.append(series)
145 | else:
146 | # For other chart types
147 | data = Reference(
148 | worksheet,
149 | min_row=start_row,
150 | max_row=end_row,
151 | min_col=start_col + 1,
152 | max_col=end_col
153 | )
154 | cats = Reference(
155 | worksheet,
156 | min_row=start_row + 1,
157 | max_row=end_row,
158 | min_col=start_col
159 | )
160 | chart.add_data(data, titles_from_data=True)
161 | chart.set_categories(cats)
162 | except Exception as e:
163 | logger.error(f"Failed to create chart data references: {e}")
164 | raise ChartError(f"Failed to create chart data references: {str(e)}")
165 |
166 | # Apply style if provided
167 | try:
168 | if style:
169 | if style.get("show_legend", True):
170 | chart.legend = Legend()
171 | chart.legend.position = style.get("legend_position", "r")
172 | else:
173 | chart.legend = None
174 |
175 | if style.get("show_data_labels", False):
176 | chart.dataLabels = DataLabelList()
177 | chart.dataLabels.showVal = True
178 |
179 | if style.get("grid_lines", False):
180 | if hasattr(chart, "x_axis"):
181 | chart.x_axis.majorGridlines = ChartLines()
182 | if hasattr(chart, "y_axis"):
183 | chart.y_axis.majorGridlines = ChartLines()
184 | except Exception as e:
185 | logger.error(f"Failed to apply chart style: {e}")
186 | raise ChartError(f"Failed to apply chart style: {str(e)}")
187 |
188 | # Set chart size
189 | chart.width = 15
190 | chart.height = 7.5
191 |
192 | # Create drawing and anchor
193 | try:
194 | drawing = SpreadsheetDrawing()
195 | drawing.chart = chart
196 |
197 | # Validate target cell format
198 | if not target_cell or not any(c.isalpha() for c in target_cell) or not any(c.isdigit() for c in target_cell):
199 | raise ValidationError(f"Invalid target cell format: {target_cell}")
200 |
201 | # Create anchor
202 | col = column_index_from_string(target_cell[0]) - 1
203 | row = int(target_cell[1:]) - 1
204 | anchor = OneCellAnchor()
205 | anchor._from = AnchorMarker(col=col, row=row)
206 | drawing.anchor = anchor
207 |
208 | # Add to worksheet
209 | worksheet._drawings.append(drawing)
210 | worksheet._charts.append(chart)
211 | except ValueError as e:
212 | logger.error(f"Invalid target cell: {e}")
213 | raise ValidationError(f"Invalid target cell: {str(e)}")
214 | except Exception as e:
215 | logger.error(f"Failed to create chart drawing: {e}")
216 | raise ChartError(f"Failed to create chart drawing: {str(e)}")
217 |
218 | try:
219 | wb.save(filepath)
220 | except Exception as e:
221 | logger.error(f"Failed to save workbook: {e}")
222 | raise ChartError(f"Failed to save workbook with chart: {str(e)}")
223 |
224 | return {
225 | "message": f"{chart_type.capitalize()} chart created successfully",
226 | "details": {
227 | "type": chart_type,
228 | "location": target_cell,
229 | "data_range": data_range
230 | }
231 | }
232 |
233 | except (ValidationError, ChartError):
234 | raise
235 | except Exception as e:
236 | logger.error(f"Unexpected error creating chart: {e}")
237 | raise ChartError(f"Unexpected error creating chart: {str(e)}")
238 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/validation.py:
--------------------------------------------------------------------------------
```python
1 | import logging
2 | import re
3 | from typing import Any
4 |
5 | from openpyxl import load_workbook
6 | from openpyxl.utils import get_column_letter
7 | from openpyxl.worksheet.worksheet import Worksheet
8 |
9 | from .cell_utils import parse_cell_range, validate_cell_reference
10 | from .exceptions import ValidationError
11 |
12 | logger = logging.getLogger(__name__)
13 |
14 | def validate_formula_in_cell_operation(
15 | filepath: str,
16 | sheet_name: str,
17 | cell: str,
18 | formula: str
19 | ) -> dict[str, Any]:
20 | """Validate Excel formula before writing"""
21 | try:
22 | wb = load_workbook(filepath)
23 | if sheet_name not in wb.sheetnames:
24 | raise ValidationError(f"Sheet '{sheet_name}' not found")
25 |
26 | if not validate_cell_reference(cell):
27 | raise ValidationError(f"Invalid cell reference: {cell}")
28 |
29 | # First validate the provided formula's syntax
30 | is_valid, message = validate_formula(formula)
31 | if not is_valid:
32 | raise ValidationError(f"Invalid formula syntax: {message}")
33 |
34 | # Additional validation for cell references in formula
35 | cell_refs = re.findall(r'[A-Z]+[0-9]+(?::[A-Z]+[0-9]+)?', formula)
36 | for ref in cell_refs:
37 | if ':' in ref: # Range reference
38 | start, end = ref.split(':')
39 | if not (validate_cell_reference(start) and validate_cell_reference(end)):
40 | raise ValidationError(f"Invalid cell range reference in formula: {ref}")
41 | else: # Single cell reference
42 | if not validate_cell_reference(ref):
43 | raise ValidationError(f"Invalid cell reference in formula: {ref}")
44 |
45 | # Now check if there's a formula in the cell and compare
46 | sheet = wb[sheet_name]
47 | cell_obj = sheet[cell]
48 | current_formula = cell_obj.value
49 |
50 | # If cell has a formula (starts with =)
51 | if isinstance(current_formula, str) and current_formula.startswith('='):
52 | if formula.startswith('='):
53 | if current_formula != formula:
54 | return {
55 | "message": "Formula is valid but doesn't match cell content",
56 | "valid": True,
57 | "matches": False,
58 | "cell": cell,
59 | "provided_formula": formula,
60 | "current_formula": current_formula
61 | }
62 | else:
63 | if current_formula != f"={formula}":
64 | return {
65 | "message": "Formula is valid but doesn't match cell content",
66 | "valid": True,
67 | "matches": False,
68 | "cell": cell,
69 | "provided_formula": formula,
70 | "current_formula": current_formula
71 | }
72 | else:
73 | return {
74 | "message": "Formula is valid and matches cell content",
75 | "valid": True,
76 | "matches": True,
77 | "cell": cell,
78 | "formula": formula
79 | }
80 | else:
81 | return {
82 | "message": "Formula is valid but cell contains no formula",
83 | "valid": True,
84 | "matches": False,
85 | "cell": cell,
86 | "provided_formula": formula,
87 | "current_content": str(current_formula) if current_formula else ""
88 | }
89 |
90 | except ValidationError as e:
91 | logger.error(str(e))
92 | raise
93 | except Exception as e:
94 | logger.error(f"Failed to validate formula: {e}")
95 | raise ValidationError(str(e))
96 |
97 | def validate_range_in_sheet_operation(
98 | filepath: str,
99 | sheet_name: str,
100 | start_cell: str,
101 | end_cell: str | None = None,
102 | ) -> dict[str, Any]:
103 | """Validate if a range exists in a worksheet and return data range info."""
104 | try:
105 | wb = load_workbook(filepath)
106 | if sheet_name not in wb.sheetnames:
107 | raise ValidationError(f"Sheet '{sheet_name}' not found")
108 |
109 | worksheet = wb[sheet_name]
110 |
111 | # Get actual data dimensions
112 | data_max_row = worksheet.max_row
113 | data_max_col = worksheet.max_column
114 |
115 | # Validate range
116 | try:
117 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
118 | except ValueError as e:
119 | raise ValidationError(f"Invalid range: {str(e)}")
120 |
121 | # If end not specified, use start
122 | if end_row is None:
123 | end_row = start_row
124 | if end_col is None:
125 | end_col = start_col
126 |
127 | # Validate bounds against maximum possible Excel limits
128 | is_valid, message = validate_range_bounds(
129 | worksheet, start_row, start_col, end_row, end_col
130 | )
131 | if not is_valid:
132 | raise ValidationError(message)
133 |
134 | range_str = f"{start_cell}" if end_cell is None else f"{start_cell}:{end_cell}"
135 | data_range_str = f"A1:{get_column_letter(data_max_col)}{data_max_row}"
136 |
137 | # Check if range is within data or extends beyond
138 | extends_beyond_data = (
139 | end_row > data_max_row or
140 | end_col > data_max_col
141 | )
142 |
143 | return {
144 | "message": (
145 | f"Range '{range_str}' is valid. "
146 | f"Sheet contains data in range '{data_range_str}'"
147 | ),
148 | "valid": True,
149 | "range": range_str,
150 | "data_range": data_range_str,
151 | "extends_beyond_data": extends_beyond_data,
152 | "data_dimensions": {
153 | "max_row": data_max_row,
154 | "max_col": data_max_col,
155 | "max_col_letter": get_column_letter(data_max_col)
156 | }
157 | }
158 | except ValidationError as e:
159 | logger.error(str(e))
160 | raise
161 | except Exception as e:
162 | logger.error(f"Failed to validate range: {e}")
163 | raise ValidationError(str(e))
164 |
165 | def validate_formula(formula: str) -> tuple[bool, str]:
166 | """Validate Excel formula syntax and safety"""
167 | if not formula.startswith("="):
168 | return False, "Formula must start with '='"
169 |
170 | # Remove the '=' prefix for validation
171 | formula = formula[1:]
172 |
173 | # Check for balanced parentheses
174 | parens = 0
175 | for c in formula:
176 | if c == "(":
177 | parens += 1
178 | elif c == ")":
179 | parens -= 1
180 | if parens < 0:
181 | return False, "Unmatched closing parenthesis"
182 |
183 | if parens > 0:
184 | return False, "Unclosed parenthesis"
185 |
186 | # Basic function name validation
187 | func_pattern = r"([A-Z]+)\("
188 | funcs = re.findall(func_pattern, formula)
189 | unsafe_funcs = {"INDIRECT", "HYPERLINK", "WEBSERVICE", "DGET", "RTD"}
190 |
191 | for func in funcs:
192 | if func in unsafe_funcs:
193 | return False, f"Unsafe function: {func}"
194 |
195 | return True, "Formula is valid"
196 |
197 |
198 | def validate_range_bounds(
199 | worksheet: Worksheet,
200 | start_row: int,
201 | start_col: int,
202 | end_row: int | None = None,
203 | end_col: int | None = None,
204 | ) -> tuple[bool, str]:
205 | """Validate that cell range is within worksheet bounds"""
206 | max_row = worksheet.max_row
207 | max_col = worksheet.max_column
208 |
209 | try:
210 | # Check start cell bounds
211 | if start_row < 1 or start_row > max_row:
212 | return False, f"Start row {start_row} out of bounds (1-{max_row})"
213 | if start_col < 1 or start_col > max_col:
214 | return False, (
215 | f"Start column {get_column_letter(start_col)} "
216 | f"out of bounds (A-{get_column_letter(max_col)})"
217 | )
218 |
219 | # If end cell specified, check its bounds
220 | if end_row is not None and end_col is not None:
221 | if end_row < start_row:
222 | return False, "End row cannot be before start row"
223 | if end_col < start_col:
224 | return False, "End column cannot be before start column"
225 | if end_row > max_row:
226 | return False, f"End row {end_row} out of bounds (1-{max_row})"
227 | if end_col > max_col:
228 | return False, (
229 | f"End column {get_column_letter(end_col)} "
230 | f"out of bounds (A-{get_column_letter(max_col)})"
231 | )
232 |
233 | return True, "Range is valid"
234 | except Exception as e:
235 | return False, f"Invalid range: {e!s}"
```
--------------------------------------------------------------------------------
/src/excel_mcp/formatting.py:
--------------------------------------------------------------------------------
```python
1 | import logging
2 | from typing import Any, Dict
3 |
4 | from openpyxl.styles import (
5 | PatternFill, Border, Side, Alignment, Protection, Font,
6 | Color
7 | )
8 | from openpyxl.formatting.rule import (
9 | ColorScaleRule, DataBarRule, IconSetRule,
10 | FormulaRule, CellIsRule
11 | )
12 |
13 | from .workbook import get_or_create_workbook
14 | from .cell_utils import parse_cell_range, validate_cell_reference
15 | from .exceptions import ValidationError, FormattingError
16 |
17 | logger = logging.getLogger(__name__)
18 |
19 | def format_range(
20 | filepath: str,
21 | sheet_name: str,
22 | start_cell: str,
23 | end_cell: str = None,
24 | bold: bool = False,
25 | italic: bool = False,
26 | underline: bool = False,
27 | font_size: int = None,
28 | font_color: str = None,
29 | bg_color: str = None,
30 | border_style: str = None,
31 | border_color: str = None,
32 | number_format: str = None,
33 | alignment: str = None,
34 | wrap_text: bool = False,
35 | merge_cells: bool = False,
36 | protection: Dict[str, Any] = None,
37 | conditional_format: Dict[str, Any] = None
38 | ) -> Dict[str, Any]:
39 | """Apply formatting to a range of cells.
40 |
41 | This function handles all Excel formatting operations including:
42 | - Font properties (bold, italic, size, color, etc.)
43 | - Cell fill/background color
44 | - Borders (style and color)
45 | - Number formatting
46 | - Alignment and text wrapping
47 | - Cell merging
48 | - Protection
49 | - Conditional formatting
50 |
51 | Args:
52 | filepath: Path to Excel file
53 | sheet_name: Name of worksheet
54 | start_cell: Starting cell reference
55 | end_cell: Optional ending cell reference
56 | bold: Whether to make text bold
57 | italic: Whether to make text italic
58 | underline: Whether to underline text
59 | font_size: Font size in points
60 | font_color: Font color (hex code)
61 | bg_color: Background color (hex code)
62 | border_style: Border style (thin, medium, thick, double)
63 | border_color: Border color (hex code)
64 | number_format: Excel number format string
65 | alignment: Text alignment (left, center, right, justify)
66 | wrap_text: Whether to wrap text
67 | merge_cells: Whether to merge the range
68 | protection: Cell protection settings
69 | conditional_format: Conditional formatting rules
70 |
71 | Returns:
72 | Dictionary with operation status
73 | """
74 | try:
75 | # Validate cell references
76 | if not validate_cell_reference(start_cell):
77 | raise ValidationError(f"Invalid start cell reference: {start_cell}")
78 |
79 | if end_cell and not validate_cell_reference(end_cell):
80 | raise ValidationError(f"Invalid end cell reference: {end_cell}")
81 |
82 | wb = get_or_create_workbook(filepath)
83 | if sheet_name not in wb.sheetnames:
84 | raise ValidationError(f"Sheet '{sheet_name}' not found")
85 |
86 | sheet = wb[sheet_name]
87 |
88 | # Get cell range coordinates
89 | try:
90 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
91 | except ValueError as e:
92 | raise ValidationError(f"Invalid cell range: {str(e)}")
93 |
94 | # If no end cell specified, use start cell coordinates
95 | if end_row is None:
96 | end_row = start_row
97 | if end_col is None:
98 | end_col = start_col
99 |
100 | # Apply font formatting
101 | font_args = {
102 | "bold": bold,
103 | "italic": italic,
104 | "underline": 'single' if underline else None,
105 | }
106 | if font_size is not None:
107 | font_args["size"] = font_size
108 | if font_color is not None:
109 | try:
110 | # Ensure color has FF prefix for full opacity
111 | font_color = font_color if font_color.startswith('FF') else f'FF{font_color}'
112 | font_args["color"] = Color(rgb=font_color)
113 | except ValueError as e:
114 | raise FormattingError(f"Invalid font color: {str(e)}")
115 | font = Font(**font_args)
116 |
117 | # Apply fill
118 | fill = None
119 | if bg_color is not None:
120 | try:
121 | # Ensure color has FF prefix for full opacity
122 | bg_color = bg_color if bg_color.startswith('FF') else f'FF{bg_color}'
123 | fill = PatternFill(
124 | start_color=Color(rgb=bg_color),
125 | end_color=Color(rgb=bg_color),
126 | fill_type='solid'
127 | )
128 | except ValueError as e:
129 | raise FormattingError(f"Invalid background color: {str(e)}")
130 |
131 | # Apply borders
132 | border = None
133 | if border_style is not None:
134 | try:
135 | border_color = border_color if border_color else "000000"
136 | border_color = border_color if border_color.startswith('FF') else f'FF{border_color}'
137 | side = Side(
138 | style=border_style,
139 | color=Color(rgb=border_color)
140 | )
141 | border = Border(
142 | left=side,
143 | right=side,
144 | top=side,
145 | bottom=side
146 | )
147 | except ValueError as e:
148 | raise FormattingError(f"Invalid border settings: {str(e)}")
149 |
150 | # Apply alignment
151 | align = None
152 | if alignment is not None or wrap_text:
153 | try:
154 | align = Alignment(
155 | horizontal=alignment,
156 | vertical='center',
157 | wrap_text=wrap_text
158 | )
159 | except ValueError as e:
160 | raise FormattingError(f"Invalid alignment settings: {str(e)}")
161 |
162 | # Apply protection
163 | protect = None
164 | if protection is not None:
165 | try:
166 | protect = Protection(**protection)
167 | except ValueError as e:
168 | raise FormattingError(f"Invalid protection settings: {str(e)}")
169 |
170 | # Apply formatting to range
171 | for row in range(start_row, end_row + 1):
172 | for col in range(start_col, end_col + 1):
173 | cell = sheet.cell(row=row, column=col)
174 | cell.font = font
175 | if fill is not None:
176 | cell.fill = fill
177 | if border is not None:
178 | cell.border = border
179 | if align is not None:
180 | cell.alignment = align
181 | if protect is not None:
182 | cell.protection = protect
183 | if number_format is not None:
184 | cell.number_format = number_format
185 |
186 | # Merge cells if requested
187 | if merge_cells and end_cell:
188 | try:
189 | range_str = f"{start_cell}:{end_cell}"
190 | sheet.merge_cells(range_str)
191 | except ValueError as e:
192 | raise FormattingError(f"Failed to merge cells: {str(e)}")
193 |
194 | # Apply conditional formatting
195 | if conditional_format is not None:
196 | range_str = f"{start_cell}:{end_cell}" if end_cell else start_cell
197 | rule_type = conditional_format.get('type')
198 | if not rule_type:
199 | raise FormattingError("Conditional format type not specified")
200 |
201 | params = conditional_format.get('params', {})
202 |
203 | # Handle fill parameter for cell_is rule
204 | if rule_type == 'cell_is' and 'fill' in params:
205 | fill_params = params['fill']
206 | if isinstance(fill_params, dict):
207 | try:
208 | fill_color = fill_params.get('fgColor', 'FFC7CE') # Default to light red
209 | fill_color = fill_color if fill_color.startswith('FF') else f'FF{fill_color}'
210 | params['fill'] = PatternFill(
211 | start_color=fill_color,
212 | end_color=fill_color,
213 | fill_type='solid'
214 | )
215 | except ValueError as e:
216 | raise FormattingError(f"Invalid conditional format fill color: {str(e)}")
217 |
218 | try:
219 | if rule_type == 'color_scale':
220 | rule = ColorScaleRule(**params)
221 | elif rule_type == 'data_bar':
222 | rule = DataBarRule(**params)
223 | elif rule_type == 'icon_set':
224 | rule = IconSetRule(**params)
225 | elif rule_type == 'formula':
226 | rule = FormulaRule(**params)
227 | elif rule_type == 'cell_is':
228 | rule = CellIsRule(**params)
229 | else:
230 | raise FormattingError(f"Invalid conditional format type: {rule_type}")
231 |
232 | sheet.conditional_formatting.add(range_str, rule)
233 | except Exception as e:
234 | raise FormattingError(f"Failed to apply conditional formatting: {str(e)}")
235 |
236 | wb.save(filepath)
237 |
238 | range_str = f"{start_cell}:{end_cell}" if end_cell else start_cell
239 | return {
240 | "message": f"Applied formatting to range {range_str}",
241 | "range": range_str
242 | }
243 |
244 | except (ValidationError, FormattingError) as e:
245 | logger.error(str(e))
246 | raise
247 | except Exception as e:
248 | logger.error(f"Failed to apply formatting: {e}")
249 | raise FormattingError(str(e))
250 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/pivot.py:
--------------------------------------------------------------------------------
```python
1 | from typing import Any
2 | import uuid
3 | import logging
4 |
5 | from openpyxl import load_workbook
6 | from openpyxl.utils import get_column_letter
7 | from openpyxl.worksheet.table import Table, TableStyleInfo
8 | from openpyxl.styles import Font
9 |
10 | from .data import read_excel_range
11 | from .cell_utils import parse_cell_range
12 | from .exceptions import ValidationError, PivotError
13 |
14 | logger = logging.getLogger(__name__)
15 |
16 | def create_pivot_table(
17 | filepath: str,
18 | sheet_name: str,
19 | data_range: str,
20 | rows: list[str],
21 | values: list[str],
22 | columns: list[str] | None = None,
23 | agg_func: str = "sum"
24 | ) -> dict[str, Any]:
25 | """Create pivot table in sheet using Excel table functionality
26 |
27 | Args:
28 | filepath: Path to Excel file
29 | sheet_name: Name of worksheet containing source data
30 | data_range: Source data range reference
31 | target_cell: Cell reference for pivot table position
32 | rows: Fields for row labels
33 | values: Fields for values
34 | columns: Optional fields for column labels
35 | agg_func: Aggregation function (sum, count, average, max, min)
36 |
37 | Returns:
38 | Dictionary with status message and pivot table dimensions
39 | """
40 | try:
41 | wb = load_workbook(filepath)
42 | if sheet_name not in wb.sheetnames:
43 | raise ValidationError(f"Sheet '{sheet_name}' not found")
44 |
45 | # Parse ranges
46 | if ':' not in data_range:
47 | raise ValidationError("Data range must be in format 'A1:B2'")
48 |
49 | try:
50 | start_cell, end_cell = data_range.split(':')
51 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
52 | except ValueError as e:
53 | raise ValidationError(f"Invalid data range format: {str(e)}")
54 |
55 | if end_row is None or end_col is None:
56 | raise ValidationError("Invalid data range format: missing end coordinates")
57 |
58 | # Create range string
59 | data_range_str = f"{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}"
60 |
61 | # Read source data
62 | try:
63 | data = read_excel_range(filepath, sheet_name, start_cell, end_cell)
64 | if not data:
65 | raise PivotError("No data found in range")
66 | except Exception as e:
67 | raise PivotError(f"Failed to read source data: {str(e)}")
68 |
69 | # Validate aggregation function
70 | valid_agg_funcs = ["sum", "average", "count", "min", "max"]
71 | if agg_func.lower() not in valid_agg_funcs:
72 | raise ValidationError(
73 | f"Invalid aggregation function. Must be one of: {', '.join(valid_agg_funcs)}"
74 | )
75 |
76 | # Clean up field names by removing aggregation suffixes
77 | def clean_field_name(field: str) -> str:
78 | field = str(field).strip()
79 | for suffix in [" (sum)", " (average)", " (count)", " (min)", " (max)"]:
80 | if field.lower().endswith(suffix):
81 | return field[:-len(suffix)]
82 | return field
83 |
84 | # Validate field names exist in data
85 | if data:
86 | first_row = data[0]
87 | available_fields = {clean_field_name(str(header)).lower() for header in first_row.keys()}
88 |
89 | for field_list, field_type in [(rows, "row"), (values, "value")]:
90 | for field in field_list:
91 | if clean_field_name(str(field)).lower() not in available_fields:
92 | raise ValidationError(
93 | f"Invalid {field_type} field '{field}'. "
94 | f"Available fields: {', '.join(sorted(available_fields))}"
95 | )
96 |
97 | if columns:
98 | for field in columns:
99 | if clean_field_name(str(field)).lower() not in available_fields:
100 | raise ValidationError(
101 | f"Invalid column field '{field}'. "
102 | f"Available fields: {', '.join(sorted(available_fields))}"
103 | )
104 |
105 | # Skip header row if it matches our fields
106 | if all(
107 | any(clean_field_name(str(header)).lower() == clean_field_name(str(field)).lower()
108 | for field in rows + values)
109 | for header in first_row.keys()
110 | ):
111 | data = data[1:]
112 |
113 | # Clean up row and value field names
114 | cleaned_rows = [clean_field_name(field) for field in rows]
115 | cleaned_values = [clean_field_name(field) for field in values]
116 |
117 | # Create pivot sheet
118 | pivot_sheet_name = f"{sheet_name}_pivot"
119 | if pivot_sheet_name in wb.sheetnames:
120 | wb.remove(wb[pivot_sheet_name])
121 | pivot_ws = wb.create_sheet(pivot_sheet_name)
122 |
123 | # Write headers
124 | current_row = 1
125 | current_col = 1
126 |
127 | # Write row field headers
128 | for field in cleaned_rows:
129 | cell = pivot_ws.cell(row=current_row, column=current_col, value=field)
130 | cell.font = Font(bold=True)
131 | current_col += 1
132 |
133 | # Write value field headers
134 | for field in cleaned_values:
135 | cell = pivot_ws.cell(row=current_row, column=current_col, value=f"{field} ({agg_func})")
136 | cell.font = Font(bold=True)
137 | current_col += 1
138 |
139 | # Get unique values for each row field
140 | field_values = {}
141 | for field in cleaned_rows:
142 | all_values = []
143 | for record in data:
144 | value = str(record.get(field, ''))
145 | all_values.append(value)
146 | field_values[field] = sorted(set(all_values))
147 |
148 | # Generate all combinations of row field values
149 | row_combinations = _get_combinations(field_values)
150 |
151 | # Calculate table dimensions for formatting
152 | total_rows = len(row_combinations) + 1 # +1 for header
153 | total_cols = len(cleaned_rows) + len(cleaned_values)
154 |
155 | # Write data rows
156 | current_row = 2
157 | for combo in row_combinations:
158 | # Write row field values
159 | col = 1
160 | for field in cleaned_rows:
161 | pivot_ws.cell(row=current_row, column=col, value=combo[field])
162 | col += 1
163 |
164 | # Filter data for current combination
165 | filtered_data = _filter_data(data, combo, {})
166 |
167 | # Calculate and write aggregated values
168 | for value_field in cleaned_values:
169 | try:
170 | value = _aggregate_values(filtered_data, value_field, agg_func)
171 | pivot_ws.cell(row=current_row, column=col, value=value)
172 | except Exception as e:
173 | raise PivotError(f"Failed to aggregate values for field '{value_field}': {str(e)}")
174 | col += 1
175 |
176 | current_row += 1
177 |
178 | # Create a table for the pivot data
179 | try:
180 | pivot_range = f"A1:{get_column_letter(total_cols)}{total_rows}"
181 | pivot_table = Table(
182 | displayName=f"PivotTable_{uuid.uuid4().hex[:8]}",
183 | ref=pivot_range
184 | )
185 | style = TableStyleInfo(
186 | name="TableStyleMedium9",
187 | showFirstColumn=False,
188 | showLastColumn=False,
189 | showRowStripes=True,
190 | showColumnStripes=True
191 | )
192 | pivot_table.tableStyleInfo = style
193 | pivot_ws.add_table(pivot_table)
194 | except Exception as e:
195 | raise PivotError(f"Failed to create pivot table formatting: {str(e)}")
196 |
197 | try:
198 | wb.save(filepath)
199 | except Exception as e:
200 | raise PivotError(f"Failed to save workbook: {str(e)}")
201 |
202 | return {
203 | "message": "Summary table created successfully",
204 | "details": {
205 | "source_range": data_range_str,
206 | "pivot_sheet": pivot_sheet_name,
207 | "rows": cleaned_rows,
208 | "columns": columns or [],
209 | "values": cleaned_values,
210 | "aggregation": agg_func
211 | }
212 | }
213 |
214 | except (ValidationError, PivotError) as e:
215 | logger.error(str(e))
216 | raise
217 | except Exception as e:
218 | logger.error(f"Failed to create pivot table: {e}")
219 | raise PivotError(str(e))
220 |
221 |
222 | def _get_combinations(field_values: dict[str, set]) -> list[dict]:
223 | """Get all combinations of field values."""
224 | result = [{}]
225 | for field, values in list(field_values.items()): # Convert to list to avoid runtime changes
226 | new_result = []
227 | for combo in result:
228 | for value in sorted(values): # Sort for consistent ordering
229 | new_combo = combo.copy()
230 | new_combo[field] = value
231 | new_result.append(new_combo)
232 | result = new_result
233 | return result
234 |
235 |
236 | def _filter_data(data: list[dict], row_filters: dict, col_filters: dict) -> list[dict]:
237 | """Filter data based on row and column filters."""
238 | result = []
239 | for record in data:
240 | matches = True
241 | for field, value in row_filters.items():
242 | if record.get(field) != value:
243 | matches = False
244 | break
245 | for field, value in col_filters.items():
246 | if record.get(field) != value:
247 | matches = False
248 | break
249 | if matches:
250 | result.append(record)
251 | return result
252 |
253 |
254 | def _aggregate_values(data: list[dict], field: str, agg_func: str) -> float:
255 | """Aggregate values using the specified function."""
256 | values = [record[field] for record in data if field in record and isinstance(record[field], (int, float))]
257 | if not values:
258 | return 0
259 |
260 | if agg_func == "sum":
261 | return sum(values)
262 | elif agg_func == "average":
263 | return sum(values) / len(values)
264 | elif agg_func == "count":
265 | return len(values)
266 | elif agg_func == "min":
267 | return min(values)
268 | elif agg_func == "max":
269 | return max(values)
270 | else:
271 | return sum(values) # Default to sum
272 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/sheet.py:
--------------------------------------------------------------------------------
```python
1 | import logging
2 | from typing import Any
3 | from copy import copy
4 |
5 | from openpyxl import load_workbook
6 | from openpyxl.worksheet.worksheet import Worksheet
7 | from openpyxl.utils import get_column_letter, column_index_from_string
8 | from openpyxl.styles import Font, Border, PatternFill, Side
9 |
10 | from .cell_utils import parse_cell_range
11 | from .exceptions import SheetError, ValidationError
12 |
13 | logger = logging.getLogger(__name__)
14 |
15 | def copy_sheet(filepath: str, source_sheet: str, target_sheet: str) -> dict[str, Any]:
16 | """Copy a worksheet within the same workbook."""
17 | try:
18 | wb = load_workbook(filepath)
19 | if source_sheet not in wb.sheetnames:
20 | raise SheetError(f"Source sheet '{source_sheet}' not found")
21 |
22 | if target_sheet in wb.sheetnames:
23 | raise SheetError(f"Target sheet '{target_sheet}' already exists")
24 |
25 | source = wb[source_sheet]
26 | target = wb.copy_worksheet(source)
27 | target.title = target_sheet
28 |
29 | wb.save(filepath)
30 | return {"message": f"Sheet '{source_sheet}' copied to '{target_sheet}'"}
31 | except SheetError as e:
32 | logger.error(str(e))
33 | raise
34 | except Exception as e:
35 | logger.error(f"Failed to copy sheet: {e}")
36 | raise SheetError(str(e))
37 |
38 | def delete_sheet(filepath: str, sheet_name: str) -> dict[str, Any]:
39 | """Delete a worksheet from the workbook."""
40 | try:
41 | wb = load_workbook(filepath)
42 | if sheet_name not in wb.sheetnames:
43 | raise SheetError(f"Sheet '{sheet_name}' not found")
44 |
45 | if len(wb.sheetnames) == 1:
46 | raise SheetError("Cannot delete the only sheet in workbook")
47 |
48 | del wb[sheet_name]
49 | wb.save(filepath)
50 | return {"message": f"Sheet '{sheet_name}' deleted"}
51 | except SheetError as e:
52 | logger.error(str(e))
53 | raise
54 | except Exception as e:
55 | logger.error(f"Failed to delete sheet: {e}")
56 | raise SheetError(str(e))
57 |
58 | def rename_sheet(filepath: str, old_name: str, new_name: str) -> dict[str, Any]:
59 | """Rename a worksheet."""
60 | try:
61 | wb = load_workbook(filepath)
62 | if old_name not in wb.sheetnames:
63 | raise SheetError(f"Sheet '{old_name}' not found")
64 |
65 | if new_name in wb.sheetnames:
66 | raise SheetError(f"Sheet '{new_name}' already exists")
67 |
68 | sheet = wb[old_name]
69 | sheet.title = new_name
70 | wb.save(filepath)
71 | return {"message": f"Sheet renamed from '{old_name}' to '{new_name}'"}
72 | except SheetError as e:
73 | logger.error(str(e))
74 | raise
75 | except Exception as e:
76 | logger.error(f"Failed to rename sheet: {e}")
77 | raise SheetError(str(e))
78 |
79 | def format_range_string(start_row: int, start_col: int, end_row: int, end_col: int) -> str:
80 | """Format range string from row and column indices."""
81 | return f"{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}"
82 |
83 | def copy_range(
84 | source_ws: Worksheet,
85 | target_ws: Worksheet,
86 | source_range: str,
87 | target_start: str | None = None,
88 | ) -> None:
89 | """Copy range from source worksheet to target worksheet."""
90 | # Parse source range
91 | if ':' in source_range:
92 | source_start, source_end = source_range.split(':')
93 | else:
94 | source_start = source_range
95 | source_end = None
96 |
97 | src_start_row, src_start_col, src_end_row, src_end_col = parse_cell_range(
98 | source_start, source_end
99 | )
100 |
101 | if src_end_row is None:
102 | src_end_row = src_start_row
103 | src_end_col = src_start_col
104 |
105 | if target_start is None:
106 | target_start = source_start
107 |
108 | tgt_start_row, tgt_start_col, _, _ = parse_cell_range(target_start)
109 |
110 | for i, row in enumerate(range(src_start_row, src_end_row + 1)):
111 | for j, col in enumerate(range(src_start_col, src_end_col + 1)):
112 | source_cell = source_ws.cell(row=row, column=col)
113 | target_cell = target_ws.cell(row=tgt_start_row + i, column=tgt_start_col + j)
114 |
115 | target_cell.value = source_cell.value
116 |
117 | try:
118 | # Copy font
119 | font_kwargs = {}
120 | if hasattr(source_cell.font, 'name'):
121 | font_kwargs['name'] = source_cell.font.name
122 | if hasattr(source_cell.font, 'size'):
123 | font_kwargs['size'] = source_cell.font.size
124 | if hasattr(source_cell.font, 'bold'):
125 | font_kwargs['bold'] = source_cell.font.bold
126 | if hasattr(source_cell.font, 'italic'):
127 | font_kwargs['italic'] = source_cell.font.italic
128 | if hasattr(source_cell.font, 'color'):
129 | font_color = None
130 | if source_cell.font.color:
131 | font_color = source_cell.font.color.rgb
132 | font_kwargs['color'] = font_color
133 | target_cell.font = Font(**font_kwargs)
134 |
135 | # Copy border
136 | new_border = Border()
137 | for side in ['left', 'right', 'top', 'bottom']:
138 | source_side = getattr(source_cell.border, side)
139 | if source_side and source_side.style:
140 | side_color = source_side.color.rgb if source_side.color else None
141 | setattr(new_border, side, Side(
142 | style=source_side.style,
143 | color=side_color
144 | ))
145 | target_cell.border = new_border
146 |
147 | # Copy fill
148 | if hasattr(source_cell, 'fill'):
149 | fill_kwargs = {'patternType': source_cell.fill.patternType}
150 | if hasattr(source_cell.fill, 'fgColor') and source_cell.fill.fgColor:
151 | fg_color = None
152 | if hasattr(source_cell.fill.fgColor, 'rgb'):
153 | fg_color = source_cell.fill.fgColor.rgb
154 | fill_kwargs['fgColor'] = fg_color
155 | if hasattr(source_cell.fill, 'bgColor') and source_cell.fill.bgColor:
156 | bg_color = None
157 | if hasattr(source_cell.fill.bgColor, 'rgb'):
158 | bg_color = source_cell.fill.bgColor.rgb
159 | fill_kwargs['bgColor'] = bg_color
160 | target_cell.fill = PatternFill(**fill_kwargs)
161 |
162 | # Copy number format and alignment
163 | if source_cell.number_format:
164 | target_cell.number_format = source_cell.number_format
165 | if source_cell.alignment:
166 | target_cell.alignment = source_cell.alignment
167 |
168 | except Exception:
169 | continue
170 |
171 | def delete_range(worksheet: Worksheet, start_cell: str, end_cell: str | None = None) -> None:
172 | """Delete contents and formatting of a range."""
173 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
174 |
175 | if end_row is None:
176 | end_row = start_row
177 | end_col = start_col
178 |
179 | for row in range(start_row, end_row + 1):
180 | for col in range(start_col, end_col + 1):
181 | cell = worksheet.cell(row=row, column=col)
182 | cell.value = None
183 | cell.font = Font()
184 | cell.border = Border()
185 | cell.fill = PatternFill()
186 | cell.number_format = "General"
187 | cell.alignment = None
188 |
189 | def merge_range(filepath: str, sheet_name: str, start_cell: str, end_cell: str) -> dict[str, Any]:
190 | """Merge a range of cells."""
191 | try:
192 | wb = load_workbook(filepath)
193 | if sheet_name not in wb.sheetnames:
194 | raise SheetError(f"Sheet '{sheet_name}' not found")
195 |
196 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
197 |
198 | if end_row is None or end_col is None:
199 | raise SheetError("Both start and end cells must be specified for merging")
200 |
201 | range_string = format_range_string(start_row, start_col, end_row, end_col)
202 | worksheet = wb[sheet_name]
203 | worksheet.merge_cells(range_string)
204 | wb.save(filepath)
205 | return {"message": f"Range '{range_string}' merged in sheet '{sheet_name}'"}
206 | except SheetError as e:
207 | logger.error(str(e))
208 | raise
209 | except Exception as e:
210 | logger.error(f"Failed to merge range: {e}")
211 | raise SheetError(str(e))
212 |
213 | def unmerge_range(filepath: str, sheet_name: str, start_cell: str, end_cell: str) -> dict[str, Any]:
214 | """Unmerge a range of cells."""
215 | try:
216 | wb = load_workbook(filepath)
217 | if sheet_name not in wb.sheetnames:
218 | raise SheetError(f"Sheet '{sheet_name}' not found")
219 |
220 | worksheet = wb[sheet_name]
221 |
222 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
223 |
224 | if end_row is None or end_col is None:
225 | raise SheetError("Both start and end cells must be specified for unmerging")
226 |
227 | range_string = format_range_string(start_row, start_col, end_row, end_col)
228 |
229 | # Check if range is actually merged
230 | merged_ranges = worksheet.merged_cells.ranges
231 | target_range = range_string.upper()
232 |
233 | if not any(str(merged_range).upper() == target_range for merged_range in merged_ranges):
234 | raise SheetError(f"Range '{range_string}' is not merged")
235 |
236 | worksheet.unmerge_cells(range_string)
237 | wb.save(filepath)
238 | return {"message": f"Range '{range_string}' unmerged successfully"}
239 | except SheetError as e:
240 | logger.error(str(e))
241 | raise
242 | except Exception as e:
243 | logger.error(f"Failed to unmerge range: {e}")
244 | raise SheetError(str(e))
245 |
246 | def copy_range_operation(
247 | filepath: str,
248 | sheet_name: str,
249 | source_start: str,
250 | source_end: str,
251 | target_start: str,
252 | target_sheet: str = None
253 | ) -> dict:
254 | """Copy a range of cells to another location."""
255 | try:
256 | wb = load_workbook(filepath)
257 | if sheet_name not in wb.sheetnames:
258 | logger.error(f"Sheet '{sheet_name}' not found")
259 | raise ValidationError(f"Sheet '{sheet_name}' not found")
260 |
261 | source_ws = wb[sheet_name]
262 | target_ws = wb[target_sheet] if target_sheet else source_ws
263 |
264 | # Parse source range
265 | try:
266 | start_row, start_col, end_row, end_col = parse_cell_range(source_start, source_end)
267 | except ValueError as e:
268 | logger.error(f"Invalid source range: {e}")
269 | raise ValidationError(f"Invalid source range: {str(e)}")
270 |
271 | # Parse target starting point
272 | try:
273 | target_row = int(''.join(filter(str.isdigit, target_start)))
274 | target_col = column_index_from_string(''.join(filter(str.isalpha, target_start)))
275 | except ValueError as e:
276 | logger.error(f"Invalid target cell: {e}")
277 | raise ValidationError(f"Invalid target cell: {str(e)}")
278 |
279 | # Copy the range
280 | row_offset = target_row - start_row
281 | col_offset = target_col - start_col
282 |
283 | for i in range(start_row, end_row + 1):
284 | for j in range(start_col, end_col + 1):
285 | source_cell = source_ws.cell(row=i, column=j)
286 | target_cell = target_ws.cell(row=i + row_offset, column=j + col_offset)
287 | target_cell.value = source_cell.value
288 | if source_cell.has_style:
289 | target_cell._style = copy(source_cell._style)
290 |
291 | wb.save(filepath)
292 | return {"message": f"Range copied successfully"}
293 |
294 | except (ValidationError, SheetError):
295 | raise
296 | except Exception as e:
297 | logger.error(f"Failed to copy range: {e}")
298 | raise SheetError(f"Failed to copy range: {str(e)}")
299 |
300 | def delete_range_operation(
301 | filepath: str,
302 | sheet_name: str,
303 | start_cell: str,
304 | end_cell: str | None = None,
305 | shift_direction: str = "up"
306 | ) -> dict[str, Any]:
307 | """Delete a range of cells and shift remaining cells."""
308 | try:
309 | wb = load_workbook(filepath)
310 | if sheet_name not in wb.sheetnames:
311 | raise SheetError(f"Sheet '{sheet_name}' not found")
312 |
313 | worksheet = wb[sheet_name]
314 |
315 | # Validate range
316 | try:
317 | start_row, start_col, end_row, end_col = parse_cell_range(start_cell, end_cell)
318 | if end_row and end_row > worksheet.max_row:
319 | raise SheetError(f"End row {end_row} out of bounds (1-{worksheet.max_row})")
320 | if end_col and end_col > worksheet.max_column:
321 | raise SheetError(f"End column {end_col} out of bounds (1-{worksheet.max_column})")
322 | except ValueError as e:
323 | raise SheetError(f"Invalid range: {str(e)}")
324 |
325 | # Validate shift direction
326 | if shift_direction not in ["up", "left"]:
327 | raise ValidationError(f"Invalid shift direction: {shift_direction}. Must be 'up' or 'left'")
328 |
329 | range_string = format_range_string(
330 | start_row, start_col,
331 | end_row or start_row,
332 | end_col or start_col
333 | )
334 |
335 | # Delete range contents
336 | delete_range(worksheet, start_cell, end_cell)
337 |
338 | # Shift cells if needed
339 | if shift_direction == "up":
340 | worksheet.delete_rows(start_row, (end_row or start_row) - start_row + 1)
341 | elif shift_direction == "left":
342 | worksheet.delete_cols(start_col, (end_col or start_col) - start_col + 1)
343 |
344 | wb.save(filepath)
345 |
346 | return {"message": f"Range {range_string} deleted successfully"}
347 | except (ValidationError, SheetError) as e:
348 | logger.error(str(e))
349 | raise
350 | except Exception as e:
351 | logger.error(f"Failed to delete range: {e}")
352 | raise SheetError(str(e))
353 |
```
--------------------------------------------------------------------------------
/src/excel_mcp/server.py:
--------------------------------------------------------------------------------
```python
1 | import logging
2 | import sys
3 | import os
4 | from typing import Any, List, Dict
5 |
6 | from mcp.server.fastmcp import FastMCP
7 |
8 | # Import exceptions
9 | from excel_mcp.exceptions import (
10 | ValidationError,
11 | WorkbookError,
12 | SheetError,
13 | DataError,
14 | FormattingError,
15 | CalculationError,
16 | PivotError,
17 | ChartError
18 | )
19 |
20 | # Import from excel_mcp package with consistent _impl suffixes
21 | from excel_mcp.validation import (
22 | validate_formula_in_cell_operation as validate_formula_impl,
23 | validate_range_in_sheet_operation as validate_range_impl
24 | )
25 | from excel_mcp.chart import create_chart_in_sheet as create_chart_impl
26 | from excel_mcp.workbook import get_workbook_info
27 | from excel_mcp.data import write_data
28 | from excel_mcp.pivot import create_pivot_table as create_pivot_table_impl
29 | from excel_mcp.sheet import (
30 | copy_sheet,
31 | delete_sheet,
32 | rename_sheet,
33 | merge_range,
34 | unmerge_range,
35 | )
36 |
37 | # Configure logging
38 | logging.basicConfig(
39 | level=logging.INFO,
40 | format="%(asctime)s - %(name)s - %(levelname)s - %(message)s",
41 | handlers=[
42 | logging.StreamHandler(sys.stdout),
43 | logging.FileHandler("excel-mcp.log")
44 | ],
45 | force=True
46 | )
47 |
48 | logger = logging.getLogger("excel-mcp")
49 |
50 | # Get Excel files path from environment or use default
51 | EXCEL_FILES_PATH = os.environ.get("EXCEL_FILES_PATH", "./excel_files")
52 |
53 | # Create the directory if it doesn't exist
54 | os.makedirs(EXCEL_FILES_PATH, exist_ok=True)
55 |
56 | # Initialize FastMCP server
57 | mcp = FastMCP(
58 | "excel-mcp",
59 | version="0.1.0",
60 | description="Excel MCP Server for manipulating Excel files",
61 | dependencies=["openpyxl>=3.1.2"],
62 | env_vars={
63 | "EXCEL_FILES_PATH": {
64 | "description": "Path to Excel files directory",
65 | "required": False,
66 | "default": EXCEL_FILES_PATH
67 | }
68 | }
69 | )
70 |
71 | def get_excel_path(filename: str) -> str:
72 | """Get full path to Excel file.
73 |
74 | Args:
75 | filename: Name of Excel file
76 |
77 | Returns:
78 | Full path to Excel file
79 | """
80 | # If filename is already an absolute path, return it
81 | if os.path.isabs(filename):
82 | return filename
83 |
84 | # Use the configured Excel files path
85 | return os.path.join(EXCEL_FILES_PATH, filename)
86 |
87 | @mcp.tool()
88 | def apply_formula(
89 | filepath: str,
90 | sheet_name: str,
91 | cell: str,
92 | formula: str,
93 | ) -> str:
94 | """Apply Excel formula to cell."""
95 | try:
96 | full_path = get_excel_path(filepath)
97 | # First validate the formula
98 | validation = validate_formula_impl(full_path, sheet_name, cell, formula)
99 | if isinstance(validation, dict) and "error" in validation:
100 | return f"Error: {validation['error']}"
101 |
102 | # If valid, apply the formula
103 | from excel_mcp.calculations import apply_formula as apply_formula_impl
104 | result = apply_formula_impl(full_path, sheet_name, cell, formula)
105 | return result["message"]
106 | except (ValidationError, CalculationError) as e:
107 | return f"Error: {str(e)}"
108 | except Exception as e:
109 | logger.error(f"Error applying formula: {e}")
110 | raise
111 |
112 | @mcp.tool()
113 | def validate_formula_syntax(
114 | filepath: str,
115 | sheet_name: str,
116 | cell: str,
117 | formula: str,
118 | ) -> str:
119 | """Validate Excel formula syntax without applying it."""
120 | try:
121 | full_path = get_excel_path(filepath)
122 | result = validate_formula_impl(full_path, sheet_name, cell, formula)
123 | return result["message"]
124 | except (ValidationError, CalculationError) as e:
125 | return f"Error: {str(e)}"
126 | except Exception as e:
127 | logger.error(f"Error validating formula: {e}")
128 | raise
129 |
130 | @mcp.tool()
131 | def format_range(
132 | filepath: str,
133 | sheet_name: str,
134 | start_cell: str,
135 | end_cell: str = None,
136 | bold: bool = False,
137 | italic: bool = False,
138 | underline: bool = False,
139 | font_size: int = None,
140 | font_color: str = None,
141 | bg_color: str = None,
142 | border_style: str = None,
143 | border_color: str = None,
144 | number_format: str = None,
145 | alignment: str = None,
146 | wrap_text: bool = False,
147 | merge_cells: bool = False,
148 | protection: Dict[str, Any] = None,
149 | conditional_format: Dict[str, Any] = None
150 | ) -> str:
151 | """Apply formatting to a range of cells."""
152 | try:
153 | full_path = get_excel_path(filepath)
154 | from excel_mcp.formatting import format_range as format_range_func
155 |
156 | result = format_range_func(
157 | filepath=full_path,
158 | sheet_name=sheet_name,
159 | start_cell=start_cell,
160 | end_cell=end_cell,
161 | bold=bold,
162 | italic=italic,
163 | underline=underline,
164 | font_size=font_size,
165 | font_color=font_color,
166 | bg_color=bg_color,
167 | border_style=border_style,
168 | border_color=border_color,
169 | number_format=number_format,
170 | alignment=alignment,
171 | wrap_text=wrap_text,
172 | merge_cells=merge_cells,
173 | protection=protection,
174 | conditional_format=conditional_format
175 | )
176 | return "Range formatted successfully"
177 | except (ValidationError, FormattingError) as e:
178 | return f"Error: {str(e)}"
179 | except Exception as e:
180 | logger.error(f"Error formatting range: {e}")
181 | raise
182 |
183 | @mcp.tool()
184 | def read_data_from_excel(
185 | filepath: str,
186 | sheet_name: str,
187 | start_cell: str = "A1",
188 | end_cell: str = None,
189 | preview_only: bool = False
190 | ) -> str:
191 | """Read data from Excel worksheet."""
192 | try:
193 | full_path = get_excel_path(filepath)
194 | from excel_mcp.data import read_excel_range
195 | result = read_excel_range(full_path, sheet_name, start_cell, end_cell, preview_only)
196 | if not result:
197 | return "No data found in specified range"
198 | # Convert the list of dicts to a formatted string
199 | data_str = "\n".join([str(row) for row in result])
200 | return data_str
201 | except Exception as e:
202 | logger.error(f"Error reading data: {e}")
203 | raise
204 |
205 | @mcp.tool()
206 | def write_data_to_excel(
207 | filepath: str,
208 | sheet_name: str,
209 | data: List[Dict],
210 | start_cell: str = "A1",
211 | write_headers: bool = True,
212 | ) -> str:
213 | """Write data to Excel worksheet."""
214 | try:
215 | full_path = get_excel_path(filepath)
216 | result = write_data(full_path, sheet_name, data, start_cell, write_headers)
217 | return result["message"]
218 | except (ValidationError, DataError) as e:
219 | return f"Error: {str(e)}"
220 | except Exception as e:
221 | logger.error(f"Error writing data: {e}")
222 | raise
223 |
224 | @mcp.tool()
225 | def create_workbook(filepath: str, upload: bool = True) -> str:
226 | """Create new Excel workbook and optionally upload it to file server."""
227 | try:
228 | full_path = get_excel_path(filepath)
229 | from excel_mcp.workbook import create_workbook as create_workbook_impl
230 | result = create_workbook_impl(full_path, upload=upload)
231 |
232 | if upload and "file_url" in result:
233 | return f"Created workbook at {full_path} and uploaded to {result['file_url']}"
234 | else:
235 | return f"Created workbook at {full_path}"
236 | except WorkbookError as e:
237 | return f"Error: {str(e)}"
238 | except Exception as e:
239 | logger.error(f"Error creating workbook: {e}")
240 | raise
241 |
242 | @mcp.tool()
243 | def create_worksheet(filepath: str, sheet_name: str) -> str:
244 | """Create new worksheet in workbook."""
245 | try:
246 | full_path = get_excel_path(filepath)
247 | from excel_mcp.workbook import create_sheet as create_worksheet_impl
248 | result = create_worksheet_impl(full_path, sheet_name)
249 | return result["message"]
250 | except (ValidationError, WorkbookError) as e:
251 | return f"Error: {str(e)}"
252 | except Exception as e:
253 | logger.error(f"Error creating worksheet: {e}")
254 | raise
255 |
256 | @mcp.tool()
257 | def create_chart(
258 | filepath: str,
259 | sheet_name: str,
260 | data_range: str,
261 | chart_type: str,
262 | target_cell: str,
263 | title: str = "",
264 | x_axis: str = "",
265 | y_axis: str = ""
266 | ) -> str:
267 | """Create chart in worksheet."""
268 | try:
269 | full_path = get_excel_path(filepath)
270 | result = create_chart_impl(
271 | filepath=full_path,
272 | sheet_name=sheet_name,
273 | data_range=data_range,
274 | chart_type=chart_type,
275 | target_cell=target_cell,
276 | title=title,
277 | x_axis=x_axis,
278 | y_axis=y_axis
279 | )
280 | return result["message"]
281 | except (ValidationError, ChartError) as e:
282 | return f"Error: {str(e)}"
283 | except Exception as e:
284 | logger.error(f"Error creating chart: {e}")
285 | raise
286 |
287 | @mcp.tool()
288 | def create_pivot_table(
289 | filepath: str,
290 | sheet_name: str,
291 | data_range: str,
292 | rows: List[str],
293 | values: List[str],
294 | columns: List[str] = None,
295 | agg_func: str = "mean"
296 | ) -> str:
297 | """Create pivot table in worksheet."""
298 | try:
299 | full_path = get_excel_path(filepath)
300 | result = create_pivot_table_impl(
301 | filepath=full_path,
302 | sheet_name=sheet_name,
303 | data_range=data_range,
304 | rows=rows,
305 | values=values,
306 | columns=columns or [],
307 | agg_func=agg_func
308 | )
309 | return result["message"]
310 | except (ValidationError, PivotError) as e:
311 | return f"Error: {str(e)}"
312 | except Exception as e:
313 | logger.error(f"Error creating pivot table: {e}")
314 | raise
315 |
316 | @mcp.tool()
317 | def copy_worksheet(
318 | filepath: str,
319 | source_sheet: str,
320 | target_sheet: str
321 | ) -> str:
322 | """Copy worksheet within workbook."""
323 | try:
324 | full_path = get_excel_path(filepath)
325 | result = copy_sheet(full_path, source_sheet, target_sheet)
326 | return result["message"]
327 | except (ValidationError, SheetError) as e:
328 | return f"Error: {str(e)}"
329 | except Exception as e:
330 | logger.error(f"Error copying worksheet: {e}")
331 | raise
332 |
333 | @mcp.tool()
334 | def delete_worksheet(
335 | filepath: str,
336 | sheet_name: str
337 | ) -> str:
338 | """Delete worksheet from workbook."""
339 | try:
340 | full_path = get_excel_path(filepath)
341 | result = delete_sheet(full_path, sheet_name)
342 | return result["message"]
343 | except (ValidationError, SheetError) as e:
344 | return f"Error: {str(e)}"
345 | except Exception as e:
346 | logger.error(f"Error deleting worksheet: {e}")
347 | raise
348 |
349 | @mcp.tool()
350 | def rename_worksheet(
351 | filepath: str,
352 | old_name: str,
353 | new_name: str
354 | ) -> str:
355 | """Rename worksheet in workbook."""
356 | try:
357 | full_path = get_excel_path(filepath)
358 | result = rename_sheet(full_path, old_name, new_name)
359 | return result["message"]
360 | except (ValidationError, SheetError) as e:
361 | return f"Error: {str(e)}"
362 | except Exception as e:
363 | logger.error(f"Error renaming worksheet: {e}")
364 | raise
365 |
366 | @mcp.tool()
367 | def get_workbook_metadata(
368 | filepath: str,
369 | include_ranges: bool = False
370 | ) -> str:
371 | """Get metadata about workbook including sheets, ranges, etc."""
372 | try:
373 | full_path = get_excel_path(filepath)
374 | result = get_workbook_info(full_path, include_ranges=include_ranges)
375 | return str(result)
376 | except WorkbookError as e:
377 | return f"Error: {str(e)}"
378 | except Exception as e:
379 | logger.error(f"Error getting workbook metadata: {e}")
380 | raise
381 |
382 | @mcp.tool()
383 | def merge_cells(filepath: str, sheet_name: str, start_cell: str, end_cell: str) -> str:
384 | """Merge a range of cells."""
385 | try:
386 | full_path = get_excel_path(filepath)
387 | result = merge_range(full_path, sheet_name, start_cell, end_cell)
388 | return result["message"]
389 | except (ValidationError, SheetError) as e:
390 | return f"Error: {str(e)}"
391 | except Exception as e:
392 | logger.error(f"Error merging cells: {e}")
393 | raise
394 |
395 | @mcp.tool()
396 | def unmerge_cells(filepath: str, sheet_name: str, start_cell: str, end_cell: str) -> str:
397 | """Unmerge a range of cells."""
398 | try:
399 | full_path = get_excel_path(filepath)
400 | result = unmerge_range(full_path, sheet_name, start_cell, end_cell)
401 | return result["message"]
402 | except (ValidationError, SheetError) as e:
403 | return f"Error: {str(e)}"
404 | except Exception as e:
405 | logger.error(f"Error unmerging cells: {e}")
406 | raise
407 |
408 | @mcp.tool()
409 | def copy_range(
410 | filepath: str,
411 | sheet_name: str,
412 | source_start: str,
413 | source_end: str,
414 | target_start: str,
415 | target_sheet: str = None
416 | ) -> str:
417 | """Copy a range of cells to another location."""
418 | try:
419 | full_path = get_excel_path(filepath)
420 | from excel_mcp.sheet import copy_range_operation
421 | result = copy_range_operation(
422 | full_path,
423 | sheet_name,
424 | source_start,
425 | source_end,
426 | target_start,
427 | target_sheet
428 | )
429 | return result["message"]
430 | except (ValidationError, SheetError) as e:
431 | return f"Error: {str(e)}"
432 | except Exception as e:
433 | logger.error(f"Error copying range: {e}")
434 | raise
435 |
436 | @mcp.tool()
437 | def delete_range(
438 | filepath: str,
439 | sheet_name: str,
440 | start_cell: str,
441 | end_cell: str,
442 | shift_direction: str = "up"
443 | ) -> str:
444 | """Delete a range of cells and shift remaining cells."""
445 | try:
446 | full_path = get_excel_path(filepath)
447 | from excel_mcp.sheet import delete_range_operation
448 | result = delete_range_operation(
449 | full_path,
450 | sheet_name,
451 | start_cell,
452 | end_cell,
453 | shift_direction
454 | )
455 | return result["message"]
456 | except (ValidationError, SheetError) as e:
457 | return f"Error: {str(e)}"
458 | except Exception as e:
459 | logger.error(f"Error deleting range: {e}")
460 | raise
461 |
462 | @mcp.tool()
463 | def validate_excel_range(
464 | filepath: str,
465 | sheet_name: str,
466 | start_cell: str,
467 | end_cell: str = None
468 | ) -> str:
469 | """Validate if a range exists and is properly formatted."""
470 | try:
471 | full_path = get_excel_path(filepath)
472 | range_str = start_cell if not end_cell else f"{start_cell}:{end_cell}"
473 | result = validate_range_impl(full_path, sheet_name, range_str)
474 | return result["message"]
475 | except ValidationError as e:
476 | return f"Error: {str(e)}"
477 | except Exception as e:
478 | logger.error(f"Error validating range: {e}")
479 | raise
480 |
481 | @mcp.tool()
482 | def process_excel_from_url(url: str, operation: str, operation_params: Dict[str, Any]) -> str:
483 | """Download Excel file from URL, process it with specified operation, and upload back to server.
484 |
485 | Args:
486 | url: URL of the Excel file to download and process
487 | operation: Name of the operation to perform (e.g., 'format_range', 'apply_formula')
488 | operation_params: Dictionary containing parameters for the specified operation
489 |
490 | Returns:
491 | Result message with the URL of the processed file
492 | """
493 | try:
494 | # 生成临时文件路径
495 | import tempfile
496 | import uuid
497 | temp_dir = tempfile.gettempdir()
498 | temp_filename = f"temp_excel_{uuid.uuid4()}.xlsx"
499 | temp_filepath = os.path.join(temp_dir, temp_filename)
500 |
501 | # 下载文件
502 | from excel_mcp.workbook import download_file_from_url
503 | download_file_from_url(url, temp_filepath)
504 | logger.info(f"Downloaded file from {url} to {temp_filepath}")
505 |
506 | # 获取文件名用于上传后的文件名
507 | original_filename = os.path.basename(url.split('/')[-1])
508 | processed_filename = f"processed_{original_filename}"
509 | processed_filepath = os.path.join(EXCEL_FILES_PATH, processed_filename)
510 |
511 | # 复制文件到Excel文件目录
512 | import shutil
513 | shutil.copy2(temp_filepath, processed_filepath)
514 |
515 | # 打印operation_params
516 | logger.info(f"operation_params: {operation_params}")
517 |
518 | # 确保operation_params是字典类型
519 | if isinstance(operation_params, str):
520 | import json
521 | try:
522 | operation_params = json.loads(operation_params)
523 | except json.JSONDecodeError:
524 | logger.error(f"Invalid JSON in operation_params: {operation_params}")
525 | raise ValueError(f"Invalid JSON format in operation_params")
526 |
527 | # 执行指定操作
528 | result_message = ""
529 | if operation == "format_range":
530 | # 替换filepath参数为处理后的文件路径
531 | params = {k: v for k, v in operation_params.items() if k != "filepath"}
532 | result_message = format_range(processed_filepath, **params)
533 | elif operation == "apply_formula":
534 | params = {k: v for k, v in operation_params.items() if k != "filepath"}
535 | result_message = apply_formula(processed_filepath, **params)
536 | elif operation == "write_data_to_excel":
537 | params = {k: v for k, v in operation_params.items() if k != "filepath"}
538 | result_message = write_data_to_excel(processed_filepath, **params)
539 | elif operation == "create_chart":
540 | params = {k: v for k, v in operation_params.items() if k != "filepath"}
541 | result_message = create_chart(processed_filepath, **params)
542 | elif operation == "create_pivot_table":
543 | params = {k: v for k, v in operation_params.items() if k != "filepath"}
544 | result_message = create_pivot_table(processed_filepath, **params)
545 | else:
546 | raise ValueError(f"Unsupported operation: {operation}")
547 |
548 | # 上传处理后的文件
549 | from excel_mcp.workbook import upload_file_to_server
550 | upload_result = upload_file_to_server(processed_filepath)
551 |
552 | # 清理临时文件
553 | os.remove(temp_filepath)
554 |
555 | return f"Operation '{operation}' completed successfully. Processed file available at: {upload_result['file_url']}\nOperation result: {result_message}"
556 | except Exception as e:
557 | logger.error(f"Error processing Excel from URL: {e}")
558 | raise WorkbookError(f"Failed to process Excel from URL: {str(e)}")
559 |
560 | async def run_server():
561 | """Run the Excel MCP server."""
562 | try:
563 | logger.info(f"Starting Excel MCP server (files directory: {EXCEL_FILES_PATH})")
564 | await mcp.run_sse_async()
565 | except KeyboardInterrupt:
566 | logger.info("Server stopped by user")
567 | await mcp.shutdown()
568 | except Exception as e:
569 | logger.error(f"Server failed: {e}")
570 | raise
571 | finally:
572 | logger.info("Server shutdown complete")
```