Excel MCP Server

Instructions

Implementation Reference

• src/handlers/excelHandlers.ts:253-331 (handler)

  Core handler function that loads the Excel workbook (using cache), extracts sheet names with indices, and for each sheet, parses column headers from the first 'headerRows' rows, structuring them into sheetField array.

  export async function analyzeExcelStructure(
      filePathWithName: string,
      headerRows: number = 1
  ): Promise<ExcelStructure> {
      try {
          const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName);
          let workbook: XLSX.WorkBook;
  
          if (!workbookResult.success) {
              const readResult = await readAndCacheFile(filePathWithName);
              if (!readResult.success) {
                  throw new Error(`Failed to read file: ${readResult.data.errors}`);
              }
              workbook = workbookCache.get(filePathWithName)!;
          } else {
              workbook = workbookResult.data as XLSX.WorkBook;
          }
  
          const result: ExcelStructure = {
              sheetList: [],
              sheetField: []
          };
  
          result.sheetList = workbook.SheetNames.map((sheetName, index) => ({
              SheetNo: index + 1,  // 添加从1开始的序号
              SheetName: sheetName
          }));
          // 遍历所有工作表
          for (const sheetName of workbook.SheetNames) {
              const worksheet = workbook.Sheets[sheetName];
              
              // 获取原始数据
              const rawData = XLSX.utils.sheet_to_json(worksheet, {
                  raw: true,
                  defval: '',
                  header: 1
              });
  
              if (rawData.length === 0) {
                  continue;
              }
              // 获取每列的数据
              const columnCount = (rawData[0] as any[]).length;
              for (let colIndex = 0; colIndex < columnCount; colIndex++) {
                  const fieldInfo: any = {
                      SheetName: sheetName
                  };
  
                  // 根据 headerRows 获取指定数量的表头行
                  for (let i = 1; i <= headerRows; i++) {
                      const headerIndex = i - 1;
                      if (rawData.length > headerIndex) {
                          const rowData = rawData[headerIndex] as any[];
                          fieldInfo[`Field${i}`] = rowData[colIndex] || '';
                      } else {
                          fieldInfo[`Field${i}`] = '';
                      }
                  }
  
                  result.sheetField = result.sheetField || [];
                  result.sheetField.push(fieldInfo);
              }
          }
  
          return  result
          // {
          //     // 修改 sheetList 的映射，添加 SheetNo
          //     sheetList: workbook.SheetNames.map((sheetName, index) => ({ 
          //         SheetNo: index + 1,  // 添加从1开始的序号
          //         SheetName: sheetName 
          //     })),
          //     sheetField: result.sheetField || []
          // };
  
      } catch (error) {
          const errorMessage = error instanceof Error ? error.message : String(error);
          throw new Error(`Failed to get Excel structure: ${errorMessage}`);
      }
  }

• src/tools/structureTools.ts:6-62 (registration)

  Tool registration including description, Zod input schema, and wrapper handler that validates/normalizes input, checks file existence, delegates to core analyzeExcelStructure function, and formats JSON response.

  server.tool("analyzeExcelStructure", 'Get Excel file structure including sheet list and column headers in JSON format',
      {
          fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
          headerRows: z.number().default(1).describe("Number of header rows to read (default: 1)")
      },
      async (params: {
          fileAbsolutePath: string;
          headerRows: number;
      }) => {
          try {
              const normalizedPath = await normalizePath(params.fileAbsolutePath);
              if (normalizedPath === 'error') {
                  return {
                      content: [{
                          type: "text",
                          text: JSON.stringify({
                              error: `Invalid file path: ${params.fileAbsolutePath}`,
                              suggestion: "Please verify the file path and name"
                          })
                      }]
                  };
              }
  
              if (!(await fileExists(normalizedPath))) {
                  return {
                      content: [{
                          type: "text",
                          text: JSON.stringify({
                              error: `File not found: ${params.fileAbsolutePath}`,
                              suggestion: "Please verify the file path and name"
                          })
                      }]
                  };
              }
  
              const result = await analyzeExcelStructure(normalizedPath, params.headerRows);
  
              return {
                  content: [{
                      type: "text",
                      text: JSON.stringify(result)
                  }]
              };
  
          } catch (error) {
              return {
                  content: [{
                      type: "text",
                      text: JSON.stringify({
                          error: `Failed to get Excel structure: ${error}`,
                          suggestion: "Please verify all parameters"
                      })
                  }]
              };
          }
      }
  );

• src/types/index.ts:32-41 (schema)

  TypeScript interface defining the structure of the output returned by analyzeExcelStructure: sheetList with sheet number and name, and sheetField with per-column header fields.

  export interface ExcelStructure {
      sheetList: Array<{
          SheetNo: number;
          SheetName: string;
      }>;
      sheetField: Array<{
          SheetName: string;
          [key: `Field${number}`]: string;
      }>;
  }