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