readSheetData
Extract structured data from all worksheets in an Excel file by specifying file path, header row, and data start position for analysis.
Instructions
Get data from all sheets in the Excel file
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| fileAbsolutePath | Yes | The absolute path of the Excel file | |
| headerRow | No | The row number to use as field names (default: 1) | |
| dataStartRow | No | The row number to start reading data from (default: 2) |
Implementation Reference
- src/tools/readTools.ts:129-187 (registration)Registers the "readSheetData" tool with server.tool, providing description, Zod input schema, and an async wrapper handler function.server.tool("readSheetData", 'Get data from all sheets in the Excel file', { fileAbsolutePath: z.string().describe("The absolute path of the Excel file"), headerRow: z.number().default(1).describe("The row number to use as field names (default: 1)"), dataStartRow: z.number().default(2).describe("The row number to start reading data from (default: 2)") }, async (params: { fileAbsolutePath: string, headerRow: number, dataStartRow: 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 readAllSheetData( normalizedPath, params.headerRow, params.dataStartRow ); return { content: [{ type: "text", text: JSON.stringify(result) }] }; } catch (error) { return { content: [{ type: "text", text: JSON.stringify({ error: `Failed to read Excel data: ${error}`, suggestion: "Please verify all parameters" }) }] }; } } );
- src/tools/readTools.ts:135-186 (handler)The registered tool handler: normalizes path, checks file existence, invokes core readAllSheetData helper, formats response as MCP content block.async (params: { fileAbsolutePath: string, headerRow: number, dataStartRow: 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 readAllSheetData( normalizedPath, params.headerRow, params.dataStartRow ); return { content: [{ type: "text", text: JSON.stringify(result) }] }; } catch (error) { return { content: [{ type: "text", text: JSON.stringify({ error: `Failed to read Excel data: ${error}`, suggestion: "Please verify all parameters" }) }] }; } }
- src/tools/readTools.ts:130-134 (schema)Zod schema defining input parameters for the readSheetData tool: file path, optional header and data start rows.{ fileAbsolutePath: z.string().describe("The absolute path of the Excel file"), headerRow: z.number().default(1).describe("The row number to use as field names (default: 1)"), dataStartRow: z.number().default(2).describe("The row number to start reading data from (default: 2)") },
- Core implementation logic for reading all sheets: loads workbook from cache or disk, parses each sheet into raw data rows using XLSX, extracts headers, transforms data rows into header-keyed objects.export async function readAllSheetData( filePathWithName: string, headerRow: number = 1, dataStartRow: number = 2 ): Promise<{ [sheetName: string]: any[] }> { try { const workbookResult: EnsureWorkbookResult = workbookCache.ensureWorkbook(filePathWithName); let workbook: XLSX.WorkBook; if (!workbookResult.success) { const readResult: ReadSheetNamesResult = 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: { [sheetName: string]: any[] } = {}; const headerIndex = headerRow - 1; const dataStartIndex = dataStartRow - 1; // 遍历所有工作表 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 <= headerIndex) { await logToFile(`Sheet ${sheetName} is empty or header row (${headerRow}) exceeds sheet length`); continue; } // 获取表头 const headers = rawData[headerIndex] as string[]; // 验证数据起始行 if (rawData.length <= dataStartIndex) { await logToFile(`Sheet ${sheetName} data start row (${dataStartRow}) exceeds sheet length`); continue; } // 处理数据行 const dataRows = rawData.slice(dataStartIndex); result[sheetName] = dataRows.map((row: any) => { const item: { [key: string]: any } = {}; headers.forEach((header, index) => { if (header) { item[header] = row[index] || ''; } }); return item; }); } return result; } catch (error) { const errorMessage = error instanceof Error ? error.message : String(error); throw new Error(`Failed to read Excel data: ${errorMessage}`); } }