Dynamic Excel MCP Server

# Dynamic Excel MCP Server Dynamic Excel file generation server using Model Context Protocol (MCP). This server allows LLMs to automatically create Excel files with any structure through dynamic JSON schemas. ## 🚀 Features - ✅ Generate Excel files from JSON schemas - ✅ **Dual transport modes**: Local (stdio) and Remote (HTTP/SSE) - ✅ **Deploy anywhere**: VPS, Cloud (AWS, GCP, Heroku), Docker - ✅ Multiple sheets support - ✅ Advanced formatting (styling, borders, colors) - ✅ Data validation and conditional formatting - ✅ Formulas and calculations - ✅ Charts support (limited) - ✅ Page setup and printing options - ✅ S3 and local file storage - ✅ Presigned URLs for secure downloads - ✅ Freeze panes, auto-filter - ✅ Merged cells and row grouping - ✅ API key authentication - ✅ CORS support for web clients ## 📦 Installation ```bash npm install npm run build ``` ## ⚙️ Configuration Create a `.env` file (copy from `.env.example`): **For Local (Stdio) Mode:** ```env TRANSPORT_MODE=stdio # Local MCP client mode STORAGE_TYPE=local DEV_STORAGE_PATH=./temp-files LOG_LEVEL=info ``` **For Remote (HTTP/SSE) Mode:** ```env TRANSPORT_MODE=http # Remote server mode HTTP_PORT=3000 HTTP_HOST=0.0.0.0 ALLOWED_ORIGINS=* # Or specific domains: https://app.example.com API_KEY=your-secret-api-key # Optional STORAGE_TYPE=s3 # or 'local' AWS_ACCESS_KEY_ID=your_key AWS_SECRET_ACCESS_KEY=your_secret AWS_REGION=ap-southeast-1 S3_BUCKET=your-bucket PRESIGNED_URL_EXPIRY=3600 LOG_LEVEL=info ``` ## 🔧 Usage ### 🖥️ Local Mode (Stdio) - For Claude Desktop Add to your Claude Desktop or MCP client configuration: **For macOS** (`~/Library/Application Support/Claude/claude_desktop_config.json`): ```json { "mcpServers": { "excel-generator": { "command": "node", "args": ["/absolute/path/to/excel-mcp-server/build/index.js"], "env": { "STORAGE_TYPE": "local", "DEV_STORAGE_PATH": "./temp-files", "LOG_LEVEL": "info" } } } } ``` **For Windows** (`%APPDATA%\Claude\claude_desktop_config.json`): ```json { "mcpServers": { "excel-generator": { "command": "node", "args": ["C:\\path\\to\\excel-mcp-server\\build\\index.js"], "env": { "STORAGE_TYPE": "local", "DEV_STORAGE_PATH": "./temp-files", "LOG_LEVEL": "info" } } } } ``` ### 🌐 Remote Mode (HTTP/SSE) - For Web Apps & Remote Access **Start the server:** ```bash # Using environment variable TRANSPORT_MODE=http npm start # Or using npm script npm run start:http # Or with .env file configured for http mode npm start ``` **Server endpoints:** ``` http://localhost:3000/health - Health check http://localhost:3000/info - Server information http://localhost:3000/sse - SSE endpoint for MCP clients ``` **Example client usage:** See `examples/client-example.ts` for a complete TypeScript client example using the MCP SDK. ```typescript import { Client } from '@modelcontextprotocol/sdk/client/index.js'; import { SSEClientTransport } from '@modelcontextprotocol/sdk/client/sse.js'; const transport = new SSEClientTransport( new URL('http://localhost:3000/sse'), { headers: { 'X-API-Key': 'your-api-key' } // If API_KEY is set } ); const client = new Client({ name: 'excel-client', version: '1.0.0', }, { capabilities: {} }); await client.connect(transport); const result = await client.callTool({ name: 'generate_excel', arguments: excelSchema }); ``` **Deployment options:** - 🐳 **Docker**: See `DEPLOYMENT.md` for Dockerfile and docker-compose examples - ☁️ **Cloud**: Deploy to AWS, GCP, Heroku, etc. - 🖧 **VPS**: Use PM2, systemd, or other process managers - 🔒 **Production**: Enable API key auth, configure CORS, use HTTPS 📚 **Full deployment guide**: See [DEPLOYMENT.md](./DEPLOYMENT.md) ### Tool: generate_excel The server provides one tool: `generate_excel` **Input Schema:** ```json { "file_name": "report.xlsx", "sheets": [ { "name": "Sheet1", "columns": [...], "data": [...], "formatting": {...} } ], "metadata": {...}, "options": {...} } ``` ## 📚 JSON Schema Structure ### Column Configuration ```json { "header": "Column Name", "key": "data_key", "width": 20, "type": "currency", "format": "#,##0₫", "style": { "font": {"bold": true, "size": 12}, "alignment": {"horizontal": "center"}, "fill": { "type": "pattern", "pattern": "solid", "fgColor": {"argb": "FFFF0000"} } } } ``` ### Supported Column Types - `text`: Plain text - `number`: Numeric values - `currency`: Currency format - `percentage`: Percentage format - `date`: Date format - `datetime`: Date and time format - `boolean`: Boolean values - `formula`: Excel formulas ### Formatting Options ```json { "freeze_panes": "A2", "auto_filter": true, "conditional_formatting": [ { "range": "A2:A100", "type": "cellIs", "operator": "greaterThan", "formulae": [0], "style": { "fill": { "type": "pattern", "pattern": "solid", "fgColor": {"argb": "FF90EE90"} } } } ], "totals_row": { "column_key": "=SUM(A2:A100)" }, "merged_cells": ["A1:D1"], "row_heights": { "1": 30, "2": 25 } } ``` ## 📖 Examples ### 1. Simple Data Table See: `examples/01-simple-table.json` Creates a basic product table with formatting: - Freeze panes - Auto-filter - Currency formatting ### 2. Financial Report See: `examples/02-financial-report.json` Advanced report with: - Report layout with title - Conditional formatting - Percentage calculations - Formula totals ### 3. Employee Database See: `examples/03-employee-database.json` Employee management spreadsheet with: - Multiple column types - Date formatting - Currency display - Auto-filter ### 4. Multi-Sheet Report See: `examples/04-multi-sheet-report.json` Comprehensive report with: - Multiple sheets - Summary and detail views - Cross-sheet consistency ## 🔨 Development ```bash # Run in development mode (with auto-reload) npm run dev # Build TypeScript npm run build # Start production server npm start # Run tests npm test # Lint code npm run lint ``` ## 🧪 Testing with MCP Inspector Test the server using the MCP Inspector: ```bash npx @modelcontextprotocol/inspector node build/index.js ``` ## 🎯 Use Cases 1. **Data Export**: Export database queries to formatted Excel files 2. **Financial Reports**: Generate quarterly/annual financial statements 3. **Inventory Management**: Create product catalogs and stock reports 4. **HR Management**: Employee databases and payroll reports 5. **Sales Analytics**: Sales reports with charts and conditional formatting 6. **Project Tracking**: Project status reports with multiple sheets ## 🏗️ Architecture ``` src/ ├── index.ts # MCP Server entry point ├── types/ │ └── schema.ts # TypeScript types & Zod schemas ├── generators/ │ ├── base-generator.ts # Abstract base class │ ├── basic-generator.ts # Simple tables │ └── report-generator.ts # Reports with styling ├── formatters/ │ ├── cell-formatter.ts # Cell formatting │ ├── style-formatter.ts # Styling utilities │ └── formula-builder.ts # Formula generation ├── storage/ │ ├── s3-storage.ts # S3 upload handler │ └── local-storage.ts # Local file system ├── validators/ │ └── schema-validator.ts # JSON schema validation └── utils/ ├── logger.ts # Logging utility └── error-handler.ts # Error handling ``` ## 🔐 Security Notes - For S3 storage, ensure proper IAM permissions - Use presigned URLs for temporary file access - Set appropriate expiry times for download links - Validate all user inputs through Zod schemas ## 📝 License MIT ## 🤝 Contributing Contributions are welcome! Please feel free to submit a Pull Request. ## 📧 Support For issues and questions, please open an issue on GitHub. ## 🎉 Acknowledgments Built with: - [Model Context Protocol (MCP)](https://github.com/anthropics/mcp) - [ExcelJS](https://github.com/exceljs/exceljs) - [Zod](https://github.com/colinhacks/zod) - [AWS SDK](https://aws.amazon.com/sdk-for-javascript/)