Excel MCP Server

Instructions

Implementation Reference

• src/handlers/data-operations.ts:412-554 (handler)

  The core handler function that performs aggregation operations (sum, average, count, min, max) on numeric data in a specified column of a CSV or Excel file, with validation for data types and column existence.

  async aggregate(args: ToolArgs): Promise<ToolResponse> {
    try {
      const { filePath, column, operation, sheet } = args;
      const data = await readFileContent(filePath, sheet);
  
      if (data.length <= 1) {
        return {
          content: [
            {
              type: 'text',
              text: JSON.stringify({
                success: false,
                error: 'File has no data rows',
              }, null, 2),
            },
          ],
        };
      }
  
      const colIndex = isNaN(Number(column))
        ? data[0].indexOf(column)
        : Number(column);
  
      if (colIndex === -1 || colIndex >= (data[0]?.length || 0)) {
        return {
          content: [
            {
              type: 'text',
              text: JSON.stringify({
                success: false,
                error: `Column "${column}" not found`,
              }, null, 2),
            },
          ],
        };
      }
  
      const numericValues = [];
      const nonNullValues = [];
  
      for (let i = 1; i < data.length; i++) {
        const cellValue = data[i][colIndex];
        if (cellValue !== null && cellValue !== undefined && cellValue !== '') {
          nonNullValues.push(cellValue);
          const numVal = Number(cellValue);
          if (!isNaN(numVal)) {
            numericValues.push(numVal);
          }
        }
      }
  
      // Check if operation requires numeric data but no numeric values found
      if (['sum', 'average', 'min', 'max'].includes(operation) && numericValues.length === 0) {
        return {
          content: [
            {
              type: 'text',
              text: JSON.stringify({
                success: false,
                error: 'Column contains no numeric values for numeric operation',
              }, null, 2),
            },
          ],
        };
      }
  
      // Check for mixed data types when doing numeric operations
      if (['sum', 'average', 'min', 'max'].includes(operation) &&
          numericValues.length > 0 &&
          numericValues.length < nonNullValues.length) {
        const mixedCount = nonNullValues.length - numericValues.length;
        return {
          content: [
            {
              type: 'text',
              text: JSON.stringify({
                success: false,
                error: `Column contains mixed data types: ${mixedCount} non-numeric values found among ${nonNullValues.length} total values`,
              }, null, 2),
            },
          ],
        };
      }
  
      let result;
      switch (operation) {
        case 'sum':
          result = numericValues.reduce((a, b) => a + b, 0);
          break;
        case 'average':
          result = numericValues.length > 0 ? numericValues.reduce((a, b) => a + b, 0) / numericValues.length : 0;
          break;
        case 'count':
          result = nonNullValues.length;
          break;
        case 'min':
          result = numericValues.length > 0 ? Math.min(...numericValues) : null;
          break;
        case 'max':
          result = numericValues.length > 0 ? Math.max(...numericValues) : null;
          break;
        default:
          return {
            content: [
              {
                type: 'text',
                text: JSON.stringify({
                  success: false,
                  error: `Unknown operation: ${operation}`,
                }, null, 2),
              },
            ],
          };
      }
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify({
              success: true,
              column: data[0][colIndex],
              operation,
              result,
              validValues: operation === 'count' ? nonNullValues.length : numericValues.length,
            }, null, 2),
          },
        ],
      };
    } catch (error) {
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify({
              success: false,
              error: error instanceof Error ? error.message : 'Unknown error occurred',
            }, null, 2),
          },
        ],
      };
    }
  }

• src/index.ts:221-246 (schema)

  Input schema definition for the 'aggregate' tool, specifying parameters: filePath (required), column (required), operation (enum: sum, average, count, min, max), sheet (optional).

    name: 'aggregate',
    description: 'Perform aggregation operations on a column',
    inputSchema: {
      type: 'object',
      properties: {
        filePath: {
          type: 'string',
          description: 'Path to the CSV or Excel file',
        },
        column: {
          type: 'string',
          description: 'Column name or index (0-based)',
        },
        operation: {
          type: 'string',
          description: 'Aggregation operation',
          enum: ['sum', 'average', 'count', 'min', 'max'],
        },
        sheet: {
          type: 'string',
          description: 'Sheet name for Excel files (optional)',
        },
      },
      required: ['filePath', 'column', 'operation'],
    },
  },

• src/index.ts:1211-1212 (registration)

  Tool dispatch registration in the main server request handler, mapping 'aggregate' tool calls to the DataOperationsHandler.aggregate method.

  case 'aggregate':
    return await this.dataOpsHandler.aggregate(toolArgs);