writeSheetData

Instructions

Implementation Reference

• src/handlers/excelHandlers.ts:172-197 (handler)

  Core handler function that creates a new Excel workbook from provided SheetData, iterates over each sheet entry, converts JSON data to worksheet using XLSX.utils.json_to_sheet, appends sheets to workbook, and writes to file using XLSX.writeFile.

  export async function writeSheetData(
      filePathWithName: string,
      data: SheetData
  ): Promise<boolean> {
      try {
          // 创建新的工作簿
          const workbook = XLSX.utils.book_new();
  
          // 遍历数据对象的每个工作表
          for (const [sheetName, sheetData] of Object.entries(data)) {
              // 将数据转换为工作表
              const worksheet = XLSX.utils.json_to_sheet(sheetData);
  
              // 将工作表添加到工作簿
              XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
          }
  
          // 写入文件
          XLSX.writeFile(workbook, filePathWithName);
  
          return true;
      } catch (error) {
          const errorMessage = error instanceof Error ? error.message : String(error);
          throw new Error(`Failed to write Excel data: ${errorMessage}`);
      }
  }

• src/types/index.ts:28-30 (schema)

  Type definition for the data parameter accepted by writeSheetData – a record mapping sheet names to arrays of row data.

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

• src/tools/writeTools.ts:86-180 (registration)

  Registration of the 'writeSheetData' tool on the MCP server using server.tool(), defining input schema with Zod (fileAbsolutePath string, data as a record of sheet names to arrays of row objects), validation logic, and invocation of the handler.

  server.tool("writeSheetData", 'Create a new Excel file with provided data',
      {
          fileAbsolutePath: z.string().describe("The absolute path for the new Excel file"),
          data: z.record(
              z.string(), // 表名（动态）
              z.array(    // 表数据数组
                  z.record( // 每行数据对象
                      z.string(), // 字段名（动态）
                      z.any()     // 字段值（任意类型）
                  )
              )
          ).describe("Data object with dynamic sheet names and column names")
      },
      async (params: {
          fileAbsolutePath: string;
          data: Record<string, 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 (Object.keys(params.data).length === 0) {
                  return {
                      content: [{
                          type: "text",
                          text: JSON.stringify({
                              error: "Empty data object provided",
                              suggestion: "Please provide at least one sheet with data"
                          })
                      }]
                  };
              }
  
              // 校验每个表的数据
              for (const [sheetName, sheetData] of Object.entries(params.data)) {
                  if (!Array.isArray(sheetData) || sheetData.length === 0) {
                      return {
                          content: [{
                              type: "text",
                              text: JSON.stringify({
                                  error: `Invalid data format in sheet "${sheetName}": Data must be a non-empty array`,
                                  suggestion: "Please check the data format of each sheet"
                              })
                          }]
                      };
                  }
              }
  
              if (await fileExists(normalizedPath)) {
                  return {
                      content: [{
                          type: "text",
                          text: JSON.stringify({
                              error: `File already exists: ${params.fileAbsolutePath}`,
                              suggestion: "Please specify a different file path"
                          })
                      }]
                  };
              }
  
              await writeSheetData(normalizedPath, params.data);
              return {
                  content: [{
                      type: "text",
                      text: JSON.stringify({
                          success: true,
                          message: `Excel file created successfully: ${normalizedPath}`
                      })
                  }]
              };
  
          } catch (error) {
              return {
                  content: [{
                      type: "text",
                      text: JSON.stringify({
                          error: `Failed to write Excel data: ${error}`,
                          suggestion: "Please verify the data format and file path"
                      })
                  }]
              };
          }
      }
  );

• src/index.ts:24-25 (registration)

  Main entry point that calls writeTools(server) to register all write-related tools including writeSheetData on the MCP server.

  writeTools(server);
  cacheTools(server);