writeDataBySheetName

Instructions

Implementation Reference

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

  Core handler function that writes data to a specific sheet in an Excel file. If the file exists, it reads the existing workbook; if not, it creates a new one. If the sheet already exists, it deletes and replaces it. Converts data using XLSX.utils.json_to_sheet, appends it, writes to file, and updates the 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:5-85 (registration)

  Registration of the 'writeDataBySheetName' tool with MCP server. Defines the input schema (fileAbsolutePath, sheetName, data array of records), validates inputs, calls the handler, and returns a success/error response.

  export const writeTools = (server: any) => {
      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/types/index.ts:28-31 (schema)

  The SheetData interface defines the type used by writeSheetData (a related tool). The writeDataBySheetName handler uses raw `any[]` data directly, so this is the closest schema type definition.

  export interface SheetData {
      [sheetName: string]: any[];
  }