Excel MCP Server

Instructions

Implementation Reference

• src/handlers/excelHandlers.ts:199-251 (handler)

  Core handler function that writes the provided data array to a specific sheet in the Excel file. Loads existing workbook from cache if file exists, overwrites the sheet if present, creates new workbook if not. Uses XLSX library for manipulation and updates cache.

  export async function writeDataBySheetName(
      filePathWithName: string,
      sheetName: string,
      data: any[]
  ): Promise<boolean> {
      try {
          let workbook: XLSX.WorkBook;
  
  
          // 检查文件是否存在，注：filePathWithName ,已经经过了normalizePath
          if (await fileExists(filePathWithName)) {
              // 如果文件存在，读取现有工作簿
              const workbookResult = workbookCache.ensureWorkbook(filePathWithName);
              if (!workbookResult.success) {
                  const readResult = await readAndCacheFile(filePathWithName);
                  if (!readResult.success) {
                      throw new Error(`Failed to read existing file: ${readResult.data.errors}`);
                  }
                  workbook = workbookCache.get(filePathWithName)!;
              } else {
                  workbook = workbookResult.data as XLSX.WorkBook;
              }
          } else {
              // 如果文件不存在，创建新的工作簿
              workbook = XLSX.utils.book_new();
          }
  
          // 将数据转换为工作表
          const worksheet = XLSX.utils.json_to_sheet(data);
  
          // 检查工作表是否已存在
          if (workbook.SheetNames.includes(sheetName)) {
              // 如果存在，删除旧的工作表
              const index = workbook.SheetNames.indexOf(sheetName);
              workbook.SheetNames.splice(index, 1);
              delete workbook.Sheets[sheetName];
          }
  
          // 添加新的工作表
          XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
  
          // 写入文件
          XLSX.writeFile(workbook, filePathWithName);
  
          // 更新缓存
          workbookCache.set(filePathWithName, workbook);
  
          return true;
      } catch (error) {
          const errorMessage = error instanceof Error ? error.message : String(error);
          throw new Error(`Failed to write sheet data: ${errorMessage}`);
      }
  }

• src/tools/writeTools.ts:6-85 (registration)

  Registers the 'writeDataBySheetName' tool on the MCP server. Includes Zod schema for input validation (fileAbsolutePath, sheetName, data), path normalization, input checks, calls the core handler, and formats success/error responses.

  server.tool("writeDataBySheetName", 'Write data to a specific sheet in the Excel file (overwrites if sheet exists)',
      {
          fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
          sheetName: z.string().describe("The name of the sheet to write"),
          data: z.array(
              z.record(
                  z.string(),
                  z.any()
              )
          ).describe("Array of objects to write to the sheet")
      },
      async (params: {
          fileAbsolutePath: string;
          sheetName: string;
          data: 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 (!Array.isArray(params.data) || params.data.length === 0) {
                  return {
                      content: [{
                          type: "text",
                          text: JSON.stringify({
                              error: "Empty data array provided",
                              suggestion: "Please provide non-empty array of data"
                          })
                      }]
                  };
              }
  
              // 校验工作表名称
              if (!params.sheetName) {
                  return {
                      content: [{
                          type: "text",
                          text: JSON.stringify({
                              error: "Invalid sheet name",
                              suggestion: "Please provide a valid sheet name"
                          })
                      }]
                  };
              }
  
              await writeDataBySheetName(normalizedPath, params.sheetName, params.data);
              return {
                  content: [{
                      type: "text",
                      text: JSON.stringify({
                          success: true,
                          message: `Data written successfully to sheet '${params.sheetName}' in file: ${normalizedPath}`
                      })
                  }]
              };
  
          } catch (error) {
              return {
                  content: [{
                      type: "text",
                      text: JSON.stringify({
                          error: `Failed to write sheet data: ${error}`,
                          suggestion: "Please verify all parameters and try again"
                      })
                  }]
              };
          }
      }
  );

• src/tools/writeTools.ts:8-16 (schema)

  Zod schema defining the input parameters for the tool: fileAbsolutePath (string), sheetName (string), data (array of records).

      fileAbsolutePath: z.string().describe("The absolute path of the Excel file"),
      sheetName: z.string().describe("The name of the sheet to write"),
      data: z.array(
          z.record(
              z.string(),
              z.any()
          )
      ).describe("Array of objects to write to the sheet")
  },