analyzeExcelStructure

Instructions

Implementation Reference

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

  Core handler function that reads an Excel file, builds a structure with sheet list and per-column header fields. Uses workbookCache to get the workbook, iterates over sheets, and extracts header rows (configurable count) per column.

  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/types/index.ts:32-41 (schema)

  Type definition for ExcelStructure, the return type of analyzeExcelStructure. Contains sheetList (array of {SheetNo, SheetName}) and sheetField (array of {SheetName, Field1...FieldN}).

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

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

  Registration of the 'analyzeExcelStructure' tool on the MCP server. Defines the Zod schema for parameters (fileAbsolutePath, headerRows) and the handler callback that normalizes path, checks file existence, calls the handler, and returns JSON result.

  import { z } from "zod";
  import { fileExists, normalizePath } from "../utils/utils.js";
  import { analyzeExcelStructure, exportExcelStructure } from '../handlers/excelHandlers.js'
  
  export const structureTools = (server: any) => {
      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"
                          })
                      }]
                  };
              }
          }
      );

• dist/tools/structureTools.js:1-10 (registration)

  Compiled JS version of the registration (same logic as src/tools/structureTools.ts).

  import { z } from "zod";
  import { fileExists, normalizePath } from "../utils/utils.js";
  import { analyzeExcelStructure, exportExcelStructure } from '../handlers/excelHandlers.js';
  export const structureTools = (server) => {
      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) => {
          try {
              const normalizedPath = await normalizePath(params.fileAbsolutePath);