readSheetData
Extract data from all sheets in an Excel file, specifying header and data start rows, using the Excel MCP Server for structured and efficient processing.
Instructions
Get data from all sheets in the Excel file
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| dataStartRow | No | The row number to start reading data from (default: 2) | |
| fileAbsolutePath | Yes | The absolute path of the Excel file | |
| headerRow | No | The row number to use as field names (default: 1) |
Implementation Reference
- src/tools/readTools.ts:135-186 (handler)The registered handler function for readSheetData tool: normalizes path, checks file existence, invokes core readAllSheetData, handles errors, and returns JSON-formatted content.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)Input schema using Zod for validating parameters: fileAbsolutePath, headerRow, dataStartRow.{ 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)") },
- src/tools/readTools.ts:129-187 (registration)Registration of the readSheetData tool via server.tool, including description, schema, and handler.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" }) }] }; } } );
- Core helper function readAllSheetData that loads workbook (with caching), iterates over sheets, parses raw data with XLSX, extracts headers and data rows into object arrays per sheet.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}`); } }