readDataBySheetName
Extract data from a specific worksheet in an Excel file by specifying the sheet name, file path, and optional row parameters for structured data retrieval.
Instructions
Get data from a specific sheet in the Excel file
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| fileAbsolutePath | Yes | The absolute path of the Excel file | |
| sheetName | Yes | tThe name of the sheet to read | |
| headerRow | No | tThe row number to use as field names (default: 1) | |
| dataStartRow | No | The row number to start reading data from (default: 2) |
Implementation Reference
- src/handlers/excelHandlers.ts:34-100 (handler)Core handler function that reads data from a specific sheet in an Excel file. Uses workbook caching, parses the sheet with XLSX, extracts headers from specified row, and maps data rows into objects.export async function readDataBySheetName( filePathWithName: string, sheetName: string, headerRow: number = 1, // 默认第一行为表头 dataStartRow: number = 2 // 默认第二行开始为数据 ): Promise<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(`read file failure: ${readResult.data.errors}`); } workbook = workbookCache.get(filePathWithName)!; } else { workbook = workbookResult.data as XLSX.WorkBook; } const worksheet = workbook.Sheets[sheetName]; if (!worksheet) { throw new Error(`sheet ${sheetName} not found`); } // 将 Excel 行号转换为数组索引（减1） const headerIndex = headerRow - 1; const dataStartIndex = dataStartRow - 1; // 获取原始数据 const rawData = XLSX.utils.sheet_to_json(worksheet, { raw: true, defval: '', header: 1 }); if (rawData.length <= headerIndex) { throw new Error(`sheet is empty or header row (${headerRow}) exceeds sheet length`); } // 获取表头 const headers = rawData[headerIndex] as string[]; // 验证数据起始行 if (rawData.length <= dataStartIndex) { throw new Error(`data start row (${dataStartRow}) exceeds sheet length`); } // 处理数据行 const dataRows = rawData.slice(dataStartIndex); const result = 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(`read sheet data failure: ${errorMessage}`); } }
- src/tools/readTools.ts:60-64 (schema)Zod schema defining the input parameters for the tool, including file path, sheet name, and optional header and data start rows.fileAbsolutePath: z.string().describe("The absolute path of the Excel file"), sheetName: z.string().describe("tThe name of the sheet to read"), headerRow: z.number().default(1).describe("tThe 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:58-128 (registration)Registers the tool with the server, providing description, input schema, and an async executor that handles validation, calls the handler, and returns formatted content.server.tool("readDataBySheetName", 'Get data from a specific sheet in the Excel file', { fileAbsolutePath: z.string().describe("The absolute path of the Excel file"), sheetName: z.string().describe("tThe name of the sheet to read"), headerRow: z.number().default(1).describe("tThe 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, sheetName: 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 exist: ${params.fileAbsolutePath}`, suggestion: "Please verify the file path and name" }) }] }; } if (!params.sheetName) { return { content: [{ type: "text", text: JSON.stringify({ error: `Invalid sheet name: ${params.sheetName}`, suggestion: "Please verify the sheet name" }) }] }; } const result = await readDataBySheetName(normalizedPath, params.sheetName, 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 data from sheet: ${params.sheetName} failure: ${error}`, suggestion: "Please verify all parameters" }) }] }; } } );