Dynamic Excel MCP Server

Instructions

Implementation Reference

• src/index.ts:151-227 (handler)

  Main handler for the 'generate_excel' tool: validates input schema, selects generator (Basic or Report), generates Excel file buffer, uploads to storage (S3 or local), generates presigned URL, returns structured response with download link.

  server.setRequestHandler(CallToolRequestSchema, async (request) => {
    if (request.params.name !== 'generate_excel') {
      throw new McpError(
        ErrorCode.MethodNotFound,
        `Unknown tool: ${request.params.name}`
      );
    }
  
    try {
      logger.info('Received generate_excel request');
  
      // Validate schema
      const validation = SchemaValidator.validate(request.params.arguments);
  
      if (!validation.success) {
        throw new McpError(
          ErrorCode.InvalidParams,
          `Schema validation failed: ${validation.errors.join(', ')}`
        );
      }
  
      const config: ExcelConfig = validation.data;
  
      // Select appropriate generator
      const generator = selectGenerator(config);
  
      // Generate Excel file
      logger.info(`Generating Excel file: ${config.file_name}`);
      const buffer = await generator.generate(config);
  
      logger.info(`Excel file generated, size: ${buffer.length} bytes`);
  
      // Upload to storage
      const fileName = config.file_name || `report_${Date.now()}.xlsx`;
      const key = await storage.upload(buffer, fileName);
  
      // Get download URL
      let downloadUrl: string;
      if (storage instanceof S3Storage) {
        const expiresIn = parseInt(process.env.PRESIGNED_URL_EXPIRY || '3600');
        downloadUrl = await storage.getPresignedUrl(key, expiresIn);
      } else {
        downloadUrl = await (storage as LocalStorage).getFileUrl(key);
      }
  
      logger.info('Excel file ready for download');
  
      // Return result
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify({
              success: true,
              download_url: downloadUrl,
              file_name: fileName,
              file_size: buffer.length,
              sheets_count: config.sheets.length,
              message: 'Excel file generated successfully',
              expires_in: storageType === 's3' ? parseInt(process.env.PRESIGNED_URL_EXPIRY || '3600') : null,
            }, null, 2),
          },
        ],
      };
    } catch (error) {
      logger.error('Error generating Excel:', error);
  
      if (error instanceof McpError) {
        throw error;
      }
  
      throw new McpError(
        ErrorCode.InternalError,
        `Failed to generate Excel file: ${error instanceof Error ? error.message : 'Unknown error'}`
      );
    }
  });

• src/index.ts:57-148 (registration)

  Registers the 'generate_excel' tool in the MCP server with detailed description and comprehensive input schema for Excel generation configuration.

    server.setRequestHandler(ListToolsRequestSchema, async () => {
      return {
        tools: [
          {
            name: 'generate_excel',
            description: `Generate an Excel file from a structured JSON schema.
  
  Use this tool when the user wants to:
  - Create an Excel file
  - Export data to Excel
  - Generate a report/spreadsheet
  - Download data as .xlsx file
  
  The tool accepts a JSON schema describing the structure, data, and formatting of the Excel file.
  
  Supported features:
  - Multiple sheets
  - Custom column widths and formats
  - Cell styling (fonts, colors, borders, alignment)
  - Data validation
  - Conditional formatting
  - Formulas and totals
  - Charts and images
  - Page setup and printing options
  - Freeze panes, auto-filter
  - Merged cells
  - Grouped rows/columns
  - Sheet protection
  
  Layout types:
  - table: Simple data table (default)
  - report: Formatted report with headers and styling
  - form: Form-style layout
  - dashboard: Dashboard with charts
  - calendar: Calendar view`,
            inputSchema: {
              type: 'object',
              properties: {
                file_name: {
                  type: 'string',
                  description: 'Name of the Excel file (e.g., "report.xlsx")',
                },
                sheets: {
                  type: 'array',
                  description: 'Array of sheet configurations',
                  items: {
                    type: 'object',
                    properties: {
                      name: {
                        type: 'string',
                        description: 'Sheet name',
                      },
                      layout: {
                        type: 'string',
                        enum: ['table', 'report', 'form', 'dashboard', 'calendar'],
                        description: 'Layout type for the sheet',
                      },
                      columns: {
                        type: 'array',
                        description: 'Column definitions',
                      },
                      data: {
                        type: 'array',
                        description: 'Array of data rows',
                      },
                      formatting: {
                        type: 'object',
                        description: 'Formatting options',
                      },
                      charts: {
                        type: 'array',
                        description: 'Charts to add to the sheet',
                      },
                    },
                    required: ['name', 'columns', 'data'],
                  },
                },
                metadata: {
                  type: 'object',
                  description: 'Workbook metadata',
                },
                options: {
                  type: 'object',
                  description: 'Output options',
                },
              },
              required: ['sheets'],
            },
          },
        ],
      };
    });

• src/types/schema.ts:233-263 (schema)

  Zod schema definition for ExcelConfig used for input validation, defining structure for workbook, sheets, columns, data, formatting, charts, etc.

  export const ExcelSchema = z.object({
    file_name: z.string().describe('Name of the Excel file'),
  
    sheets: z.array(SheetSchema).min(1),
  
    // Workbook metadata
    metadata: z.object({
      title: z.string().optional(),
      subject: z.string().optional(),
      author: z.string().optional().default('AI Assistant'),
      description: z.string().optional(),
      company: z.string().optional(),
      category: z.string().optional(),
      keywords: z.array(z.string()).optional(),
    }).optional(),
  
    // Output options
    options: z.object({
      storage: z.enum(['s3', 'local']).optional(),
      compress: z.boolean().optional(),
      password: z.string().optional(),
    }).optional(),
  });
  
  // Export types
  export type ColumnConfig = z.infer<typeof ColumnSchema>;
  export type ConditionalFormatting = z.infer<typeof ConditionalFormattingSchema>;
  export type ChartConfig = z.infer<typeof ChartSchema>;
  export type FormattingConfig = z.infer<typeof FormattingSchema>;
  export type SheetConfig = z.infer<typeof SheetSchema>;
  export type ExcelConfig = z.infer<typeof ExcelSchema>;

• src/generators/base-generator.ts:19-46 (helper)

  Core method in BaseGenerator that creates an Excel worksheet: sets columns, applies header styles, adds data rows with formatting/validation, applies sheet-level formatting (freeze panes, filters, etc.), adds charts.

  protected async createSheet(sheetConfig: SheetConfig): Promise<ExcelJS.Worksheet> {
    const worksheet = this.workbook.addWorksheet(sheetConfig.name);
  
    // Setup columns
    worksheet.columns = sheetConfig.columns.map(col => ({
      header: col.header,
      key: col.key,
      width: col.width || 15,
    }));
  
    // Apply header styling
    this.applyHeaderStyle(worksheet, sheetConfig.columns);
  
    // Add data rows
    await this.addDataRows(worksheet, sheetConfig);
  
    // Apply formatting
    if (sheetConfig.formatting) {
      await this.applyFormatting(worksheet, sheetConfig);
    }
  
    // Add charts
    if (sheetConfig.charts) {
      await this.addCharts(worksheet, sheetConfig.charts);
    }
  
    return worksheet;
  }

• src/generators/base-generator.ts:203-206 (helper)

  Generates the final Excel file buffer from the workbook using ExcelJS writeBuffer.

  protected async generateBuffer(): Promise<Buffer> {
    const buffer = await this.workbook.xlsx.writeBuffer();
    return Buffer.from(buffer);
  }