exportExcelStructure
Extract sheet names and headers from an Excel file and save them to a new template file.
Instructions
Export Excel file structure (sheets and headers) to a new Excel template file
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sourceFilePath | Yes | The source Excel file path to analyze | |
| targetFilePath | Yes | The target Excel file path to save structure | |
| headerRows | No | Number of header rows to analyze (default: 1) |
Implementation Reference
- src/handlers/excelHandlers.ts:333-364 (handler)The core handler function that exports Excel file structure. It calls analyzeExcelStructure to get the source file's sheet list and field headers, then calls writeSheetData to write them into a new Excel template file.
export async function exportExcelStructure( sourceFilePath: string, targetFilePath: string, headerRows: number = 1 ): Promise<boolean> { try { // 1. 获取源文件的结构 const structure = await analyzeExcelStructure(sourceFilePath, headerRows); // 校验结构数据 if (!structure.sheetList || !Array.isArray(structure.sheetList) || structure.sheetList.length === 0) { throw new Error('Invalid Excel structure: sheetList is empty or invalid'); } if (!structure.sheetField || !Array.isArray(structure.sheetField) || structure.sheetField.length === 0) { throw new Error('Invalid Excel structure: sheetField is empty or invalid'); } // 2. 转换为 SheetData 格式 const data: SheetData = { 'sheetList': structure.sheetList, 'sheetField': structure.sheetField }; // 3. 使用 writeSheetData 写入文件 return await writeSheetData(targetFilePath, data); } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error); throw new Error(`Failed to export Excel structure: ${errorMessage}`); } } - src/tools/structureTools.ts:63-155 (registration)The MCP tool registration for 'exportExcelStructure'. Defines the tool name, description, Zod schema (sourceFilePath, targetFilePath, headerRows), and the handler callback that validates paths, checks file existence, and calls the export handler.
server.tool("exportExcelStructure", 'Export Excel file structure (sheets and headers) to a new Excel template file', { sourceFilePath: z.string().describe("The source Excel file path to analyze"), targetFilePath: z.string().describe("The target Excel file path to save structure"), headerRows: z.number().default(1).describe("Number of header rows to analyze (default: 1)") }, async (params: { sourceFilePath: string; targetFilePath: string; headerRows: number; }) => { try { // 验证源文件路径 const normalizedSourcePath = await normalizePath(params.sourceFilePath); if (normalizedSourcePath === 'error') { return { content: [{ type: "text", text: JSON.stringify({ error: `Invalid source file path: ${params.sourceFilePath}`, suggestion: "Please verify the source file path" }) }] }; } // 验证源文件是否存在 if (!(await fileExists(normalizedSourcePath))) { return { content: [{ type: "text", text: JSON.stringify({ error: `Source file not found: ${params.sourceFilePath}`, suggestion: "Please verify the source file exists" }) }] }; } // 验证目标文件路径 const normalizedTargetPath = await normalizePath(params.targetFilePath); if (normalizedTargetPath === 'error') { return { content: [{ type: "text", text: JSON.stringify({ error: `Invalid target file path: ${params.targetFilePath}`, suggestion: "Please verify the target file path" }) }] }; } // 验证目标文件是否已存在 if (await fileExists(normalizedTargetPath)) { return { content: [{ type: "text", text: JSON.stringify({ error: `Target file already exists: ${params.targetFilePath}`, suggestion: "Please specify a different target file path" }) }] }; } // 导出结构 await exportExcelStructure(normalizedSourcePath, normalizedTargetPath, params.headerRows); return { content: [{ type: "text", text: JSON.stringify({ success: true, message: `Excel structure exported successfully to: ${normalizedTargetPath}` }) }] }; } catch (error) { return { content: [{ type: "text", text: JSON.stringify({ error: `Failed to export Excel structure: ${error}`, suggestion: "Please verify all parameters and try again" }) }] }; } } ); } - The writeSheetData helper function used by exportExcelStructure to create a new Excel workbook from SheetData. Creates a new workbook, converts each data entry to JSON-based sheets, and writes the file.
export async function writeSheetData( filePathWithName: string, data: SheetData ): Promise<boolean> { try { // 创建新的工作簿 const workbook = XLSX.utils.book_new(); // 遍历数据对象的每个工作表 for (const [sheetName, sheetData] of Object.entries(data)) { // 将数据转换为工作表 const worksheet = XLSX.utils.json_to_sheet(sheetData); // 将工作表添加到工作簿 XLSX.utils.book_append_sheet(workbook, worksheet, sheetName); } // 写入文件 XLSX.writeFile(workbook, filePathWithName); return true; } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error); throw new Error(`Failed to write Excel data: ${errorMessage}`); } } - src/types/index.ts:28-41 (schema)Type definitions used by exportExcelStructure: ExcelStructure (input from analyzeExcelStructure) and SheetData (output format for writeSheetData).
export interface SheetData { [sheetName: string]: any[]; } export interface ExcelStructure { sheetList: Array<{ SheetNo: number; SheetName: string; }>; sheetField: Array<{ SheetName: string; [key: `Field${number}`]: string; }>; } - The analyzeExcelStructure helper function called by exportExcelStructure to extract sheet names and column headers from the source Excel file.
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}`); } }