Excel MCP Server

Instructions

Implementation Reference

• src/handlers/excel-workflow.ts:247-356 (handler)

  The primary handler function for the 'vlookup_helper' MCP tool. It reads data from source and lookup files, identifies columns, builds a lookup map for efficient querying, and returns a summary with preview data. Supports fuzzy matching and error handling options. Note: actual source data lookup and match counts are placeholders in this implementation.

  async vlookupHelper(args: ToolArgs): Promise<ToolResponse> {
    try {
      const {
        sourceFile,
        lookupFile,
        lookupColumn,
        returnColumns = [],
        fuzzyMatch = false,
        handleErrors = true,
        sourceSheet,
        lookupSheet
      } = args;
  
      if (!sourceFile || !lookupFile || !lookupColumn) {
        return {
          content: [{
            type: 'text',
            text: JSON.stringify({
              success: false,
              error: 'Missing required parameters: sourceFile, lookupFile, lookupColumn'
            }, null, 2)
          }]
        };
      }
  
      // Read both files
      const sourceData = await readFileContent(sourceFile, sourceSheet);
      const lookupData = await readFileContent(lookupFile, lookupSheet);
  
      if (sourceData.length === 0 || lookupData.length === 0) {
        return {
          content: [{
            type: 'text',
            text: JSON.stringify({
              success: false,
              error: 'One or both files are empty'
            }, null, 2)
          }]
        };
      }
  
      const sourceHeaders = sourceData[0];
      const lookupHeaders = lookupData[0];
      const lookupRows = lookupData.slice(1);
  
      // Find lookup column index
      let lookupColIndex = typeof lookupColumn === 'number' ? lookupColumn : lookupHeaders.indexOf(lookupColumn);
      if (lookupColIndex === -1) {
        throw new Error(`Lookup column "${lookupColumn}" not found`);
      }
  
      // Find return column indices
      let returnColIndices: number[] = [];
      if (returnColumns.length === 0) {
        // Return all columns except lookup column
        returnColIndices = Array.from({length: lookupHeaders.length}, (_, i) => i)
          .filter(i => i !== lookupColIndex);
      } else {
        returnColIndices = returnColumns.map((col: any) => {
          if (typeof col === 'number') return col;
          const index = lookupHeaders.indexOf(col);
          if (index === -1) throw new Error(`Return column "${col}" not found`);
          return index;
        });
      }
  
      // Create lookup map
      const lookupMap = new Map<string, any[]>();
      lookupRows.forEach((row: any[]) => {
        const key = String(row[lookupColIndex] || '').toLowerCase();
        const values = returnColIndices.map((i: number) => row[i]);
        lookupMap.set(key, values);
      });
  
      const result = {
        success: true,
        operation: 'vlookup_helper',
        summary: {
          sourceRows: sourceData.length - 1,
          lookupRows: lookupRows.length,
          lookupColumn: lookupHeaders[lookupColIndex],
          returnColumns: returnColIndices.map((i: number) => lookupHeaders[i]),
          fuzzyMatch,
          handleErrors
        },
        lookupMap: Object.fromEntries(Array.from(lookupMap.entries()).slice(0, 10)), // Preview
        matchedCount: 0, // Would be calculated during actual lookup
        unmatchedCount: 0 // Would be calculated during actual lookup
      };
  
      return {
        content: [{
          type: 'text',
          text: JSON.stringify(result, null, 2)
        }]
      };
  
    } catch (error) {
      return {
        content: [{
          type: 'text',
          text: JSON.stringify({
            success: false,
            error: error instanceof Error ? error.message : 'Unknown error',
            operation: 'vlookup_helper'
          }, null, 2)
        }]
      };
    }
  }