writeSheetData
Generate a new Excel file using structured data by specifying file path and dynamic sheet-column configurations for efficient worksheet management.
Instructions
Create a new Excel file with provided data
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| data | Yes | Data object with dynamic sheet names and column names | |
| fileAbsolutePath | Yes | The absolute path for the new Excel file |
Implementation Reference
- src/handlers/excelHandlers.ts:172-197 (handler)Core implementation of writeSheetData: creates a new Excel workbook, iterates over the data object to create worksheets from each sheet's data using XLSX.utils.json_to_sheet, appends them to the workbook, and writes the file to disk using XLSX.writeFile.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-30 (schema)TypeScript interface defining SheetData: a record where keys are sheet names (strings) and values are arrays of row objects (any[]), used as the type for the data parameter in writeSheetData.export interface SheetData { [sheetName: string]: any[]; }
- src/tools/writeTools.ts:86-180 (registration)Registers the "writeSheetData" MCP tool with description, Zod input schema validating fileAbsolutePath (string) and data (record of sheetName to array of records), and an async handler that normalizes the path, validates data structure and file non-existence, then calls the core writeSheetData function.server.tool("writeSheetData", 'Create a new Excel file with provided data', { fileAbsolutePath: z.string().describe("The absolute path for the new Excel file"), data: z.record( z.string(), // 表名（动态） z.array( // 表数据数组 z.record( // 每行数据对象 z.string(), // 字段名（动态） z.any() // 字段值（任意类型） ) ) ).describe("Data object with dynamic sheet names and column names") }, async (params: { fileAbsolutePath: string; data: Record<string, Record<string, any>[]>; }) => { 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 (Object.keys(params.data).length === 0) { return { content: [{ type: "text", text: JSON.stringify({ error: "Empty data object provided", suggestion: "Please provide at least one sheet with data" }) }] }; } // 校验每个表的数据 for (const [sheetName, sheetData] of Object.entries(params.data)) { if (!Array.isArray(sheetData) || sheetData.length === 0) { return { content: [{ type: "text", text: JSON.stringify({ error: `Invalid data format in sheet "${sheetName}": Data must be a non-empty array`, suggestion: "Please check the data format of each sheet" }) }] }; } } if (await fileExists(normalizedPath)) { return { content: [{ type: "text", text: JSON.stringify({ error: `File already exists: ${params.fileAbsolutePath}`, suggestion: "Please specify a different file path" }) }] }; } await writeSheetData(normalizedPath, params.data); return { content: [{ type: "text", text: JSON.stringify({ success: true, message: `Excel file created successfully: ${normalizedPath}` }) }] }; } catch (error) { return { content: [{ type: "text", text: JSON.stringify({ error: `Failed to write Excel data: ${error}`, suggestion: "Please verify the data format and file path" }) }] }; } } );