# 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/)
