Excel MCP Server

Instructions

Implementation Reference

• src/handlers/analytics.ts:237-320 (handler)

  The core handler function that implements the pivot_table tool logic: reads data from file/sheet, groups by specified column, aggregates numeric values in another column using sum/average/count/min/max, sorts results descending by aggregate value, and returns structured JSON response.

  async pivotTable(args: ToolArgs): Promise<ToolResponse> {
    const { filePath, groupBy, aggregateColumn, operation, sheet } = args;
    const data = await readFileContent(filePath, sheet);
  
    if (data.length <= 1) {
      throw new Error('File has no data rows');
    }
  
    const groupByIndex = isNaN(Number(groupBy)) ? data[0].indexOf(groupBy) : Number(groupBy);
    const aggIndex = isNaN(Number(aggregateColumn)) ? data[0].indexOf(aggregateColumn) : Number(aggregateColumn);
  
    if (groupByIndex === -1 || aggIndex === -1) {
      throw new Error('One or both columns not found');
    }
  
    // Group data
    const groups: Record<string, number[]> = {};
    for (let i = 1; i < data.length; i++) {
      const groupKey = String(data[i][groupByIndex]);
      const value = Number(data[i][aggIndex]);
  
      if (!groups[groupKey]) {
        groups[groupKey] = [];
      }
  
      if (!isNaN(value)) {
        groups[groupKey].push(value);
      }
    }
  
    // Calculate aggregations
    const results: Array<{ group: string, value: number, count: number }> = [];
    for (const [group, values] of Object.entries(groups)) {
      if (values.length === 0) continue;
  
      let result: number;
      switch (operation) {
        case 'sum':
          result = values.reduce((a: number, b: number) => a + b, 0);
          break;
        case 'average':
          result = values.reduce((a: number, b: number) => a + b, 0) / values.length;
          break;
        case 'count':
          result = values.length;
          break;
        case 'min':
          result = Math.min(...values);
          break;
        case 'max':
          result = Math.max(...values);
          break;
        default:
          result = 0;
      }
  
      results.push({
        group,
        value: Math.round(result * 100) / 100,
        count: values.length
      });
    }
  
    // Sort by value descending
    results.sort((a, b) => b.value - a.value);
  
    return {
      content: [
        {
          type: 'text',
          text: JSON.stringify({
            success: true,
            pivotTable: {
              groupBy: data[0][groupByIndex],
              aggregateColumn: data[0][aggIndex],
              operation,
              totalGroups: results.length,
              results
            }
          }, null, 2),
        },
      ],
    };
  }

• src/index.ts:362-392 (schema)

  The input schema definition for the pivot_table tool, defining parameters: filePath (required), groupBy, aggregateColumn, operation (sum/average/count/min/max), and optional sheet.

  {
    name: 'pivot_table',
    description: 'Create pivot table with grouping and aggregation',
    inputSchema: {
      type: 'object',
      properties: {
        filePath: {
          type: 'string',
          description: 'Path to the CSV or Excel file',
        },
        groupBy: {
          type: 'string',
          description: 'Column to group by',
        },
        aggregateColumn: {
          type: 'string',
          description: 'Column to aggregate',
        },
        operation: {
          type: 'string',
          description: 'Aggregation operation',
          enum: ['sum', 'average', 'count', 'min', 'max'],
        },
        sheet: {
          type: 'string',
          description: 'Sheet name for Excel files (optional)',
        },
      },
      required: ['filePath', 'groupBy', 'aggregateColumn', 'operation'],
    },
  },

• src/index.ts:1227-1228 (registration)

  The dispatch/registration in the main MCP server request handler switch statement, routing 'pivot_table' calls to AnalyticsHandler.pivotTable method.

  case 'pivot_table':
    return await this.analyticsHandler.pivotTable(toolArgs);