合并多个Excel文件为带汇总页的多工作表文件

• •无特殊前置要求，导入即可使用

{
  "meta": {
    "instanceId": "4b3a33c57adb2434e3d3c2fca2e5f3cdf6673361f4415a580cb62dfe366d1bcf"
  },
  "nodes": [
    {
      "id": "b0865f1f-7e49-48af-829c-49202779ff8e",
      "name": "当点击“执行工作流”时",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -944,
        -16
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "a4eb6e0e-999e-4056-9421-abac99c18df1",
      "name": "读取每个 XLXS",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -496,
        -16
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "ee1d615e-5e7c-4696-b633-113b323536dc",
      "name": "从磁盘读取 XLXS 文件",
      "type": "n8n-nodes-base.readWriteFile",
      "position": [
        -720,
        -16
      ],
      "parameters": {
        "options": {},
        "fileSelector": "n8n_files/*.xlsx"
      },
      "typeVersion": 1
    },
    {
      "id": "8d2bf3e9-2d8a-4279-90e0-3a1c8b49b683",
      "name": "创建多工作表 Excel",
      "type": "n8n-nodes-base.code",
      "position": [
        -32,
        -224
      ],
      "parameters": {
        "jsCode": "// Use the XLSX library to create a multi-sheet Excel file\nconst XLSX = require('xlsx');\n\n// Get all file data\nconst allFiles = $input.first().json.allFiles;\n\nif (!allFiles || allFiles.length === 0) {\n  throw new Error('No files to process');\n}\n\n// Create a new workbook\nconst workbook = XLSX.utils.book_new();\n\n// Create a worksheet for each file\nallFiles.forEach((file, index) => {\n  console.log(`Creating sheet: ${file.sheetName}`);\n  \n  let sheetName = file.sheetName;\n  \n  // Ensure the sheet name is unique and conforms to Excel standards\n  if (workbook.SheetNames.includes(sheetName)) {\n    sheetName = `${sheetName}_${index + 1}`;\n  }\n  \n  // Excel sheet name limitations: max 31 characters, cannot contain special characters\n  sheetName = sheetName\n    .replace(/[\\[\\]\\*\\/\\\\\\?\\:]/g, '_')\n    .substring(0, 31);\n  \n  // Create the worksheet\n  let worksheet;\n  \n  if (file.data && file.data.length > 0) {\n    // Convert JSON data to a worksheet\n    worksheet = XLSX.utils.json_to_sheet(file.data);\n    \n    // To add metadata, we need to recreate the worksheet\n    const wsData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });\n    const finalData = [\n      ...wsData\n    ];\n    \n    worksheet = XLSX.utils.aoa_to_sheet(finalData);\n  } else {\n    // If there is no data, create an empty worksheet\n    worksheet = XLSX.utils.aoa_to_sheet([\n      [`Original File: ${file.originalFileName}`],\n      ['No data'],\n      [`Processing Time: ${new Date().toLocaleString()}`]\n    ]);\n  }\n  \n  // Add the worksheet to the workbook\n  XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);\n  \n  console.log(`Sheet '${sheetName}' created with ${file.recordCount} records`);\n});\n\n// Create a summary sheet\nconst summaryData = [\n  ['File Summary Report'],\n  ['Generation Time', new Date().toLocaleString()],\n  ['Total Files', allFiles.length],\n  [],\n  ['Sheet Name', 'Original File Name', 'Record Count']\n];\n\nallFiles.forEach(file => {\n  summaryData.push([file.sheetName, file.originalFileName, file.recordCount]);\n});\n\nconst summarySheet = XLSX.utils.aoa_to_sheet(summaryData);\nXLSX.utils.book_append_sheet(workbook, summarySheet, 'Summary');\n\n// Convert the workbook to a buffer\nconst excelBuffer = XLSX.write(workbook, { \n  type: 'buffer', \n  bookType: 'xlsx',\n  compression: true\n});\n\n// Create a file name (including a timestamp)\nconst timestamp = new Date().toISOString().replace(/[:\\-T]/g, '').split('.')[0];\nconst fileName = `Merged_Files_${timestamp}.xlsx`;\n\nconsole.log(`Excel file created: ${fileName}`);\nconsole.log(`Total sheets: ${workbook.SheetNames.length}`);\nconsole.log(`Sheet names: ${workbook.SheetNames.join(', ')}`);\n\n// Return binary data\n// Convert buffer to base64 so n8n can download it\nconst base64Data = excelBuffer.toString('base64');\n\nreturn [{\n  json: {\n    fileName: fileName,\n    sheetsCreated: workbook.SheetNames,\n    totalFiles: allFiles.length,\n    summary: `Successfully merged ${allFiles.length} Excel files into ${workbook.SheetNames.length} worksheets`\n  },\n  binary: {\n    data: {\n      data: base64Data,\n      mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',\n      fileName: fileName\n    }\n  }\n}];\n"
      },
      "executeOnce": true,
      "typeVersion": 2,
      "alwaysOutputData": false
    },
    {
      "id": "3371ae1b-7fc2-4981-9893-a08760e1549d",
      "name": "收集和处理数据",
      "type": "n8n-nodes-base.code",
      "position": [
        -240,
        -224
      ],
      "parameters": {
        "jsCode": "const allFiles = [];\n\nfor (const item of $input.all()) {\n  // First, get the file name (prioritizing from json, then from binary metadata)\n  const fileName =\n    item.json.fileName ??\n    item.binary?.data?.fileName ??\n    'UnknownFile';\n\n  const sheetName = fileName\n    .replace(/^.*[\\/\\\\]/, '')\n    .replace(/\\.[^/.]+$/, '');\n\n  // 'Extract from File' puts an array of \"rows\" into json.data\n  let sheetData = item.json.data ?? [];\n  if (!Array.isArray(sheetData)) sheetData = [sheetData];\n\n  // Filter out empty rows\n  const cleanedData = sheetData.filter(row =>\n    row && typeof row === 'object' &&\n    Object.values(row).some(v => v !== null && v !== undefined && v !== '')\n  );\n\n  allFiles.push({\n    sheetName,\n    data: cleanedData,\n    originalFileName: fileName,\n    recordCount: cleanedData.length,\n  });\n}\n\nreturn [{ json: { allFiles } }];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "generated-4d48e4e6-790e-4517-aecb-695cb18ceca8",
      "name": "工作流启动与文件读取",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1024,
        -208
      ],
      "parameters": {
        "color": 5,
        "width": 680,
        "height": 554,
        "content": "## 🚀 1. 工作流启动与文件读取"
      }
    },
    {
      "id": "generated-7a99b3f6-0daf-4d46-8d17-e9e5cf96472e",
      "name": "数据提取与处理",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        0
      ],
      "parameters": {
        "color": 4,
        "width": 704,
        "height": 346,
        "content": "## 📊 2. 数据提取与处理"
      }
    },
    {
      "id": "generated-998281e0-d0e4-4c79-af6f-c174dd8cf857",
      "name": "生成与保存 Excel 文件",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -736
      ],
      "parameters": {
        "width": 706,
        "height": 720,
        "content": "## 📝 3. 生成并保存多工作表 Excel 文件"
      }
    },
    {
      "id": "88841064-e087-47f9-97bc-8eb94635d651",
      "name": "保存 XLXS 到磁盘",
      "type": "n8n-nodes-base.readWriteFile",
      "position": [
        176,
        -224
      ],
      "parameters": {
        "options": {},
        "fileName": "=n8n_files/output/{{$json.fileName}}",
        "operation": "write"
      },
      "typeVersion": 1
    },
    {
      "id": "448417f6-a9dc-4fc2-a05c-1b53da843f1f",
      "name": "XLSX 转 Json 列表",
      "type": "n8n-nodes-base.extractFromFile",
      "position": [
        -176,
        160
      ],
      "parameters": {
        "options": {
          "rawData": true,
          "headerRow": true,
          "includeEmptyCells": false
        },
        "operation": "xlsx"
      },
      "typeVersion": 1
    },
    {
      "id": "e4354d1b-87fb-4e21-869d-7706acef1f76",
      "name": "多个 Json 转单个 Json",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        64,
        160
      ],
      "parameters": {
        "options": {},
        "aggregate": "aggregateAllItemData"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "a4eb6e0e-999e-4056-9421-abac99c18df1": {
      "main": [
        [
          {
            "node": "3371ae1b-7fc2-4981-9893-a08760e1549d",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "448417f6-a9dc-4fc2-a05c-1b53da843f1f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "448417f6-a9dc-4fc2-a05c-1b53da843f1f": {
      "main": [
        [
          {
            "node": "e4354d1b-87fb-4e21-869d-7706acef1f76",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3371ae1b-7fc2-4981-9893-a08760e1549d": {
      "main": [
        [
          {
            "node": "8d2bf3e9-2d8a-4279-90e0-3a1c8b49b683",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8d2bf3e9-2d8a-4279-90e0-3a1c8b49b683": {
      "main": [
        [
          {
            "node": "88841064-e087-47f9-97bc-8eb94635d651",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ee1d615e-5e7c-4696-b633-113b323536dc": {
      "main": [
        [
          {
            "node": "a4eb6e0e-999e-4056-9421-abac99c18df1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e4354d1b-87fb-4e21-869d-7706acef1f76": {
      "main": [
        [
          {
            "node": "a4eb6e0e-999e-4056-9421-abac99c18df1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b0865f1f-7e49-48af-829c-49202779ff8e": {
      "main": [
        [
          {
            "node": "ee1d615e-5e7c-4696-b633-113b323536dc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}