Mehrere Excel-Dateien zu einer mehrtabellarigen Datei mit Zusammenfassungsseite zusammenführen

• •Keine besonderen Voraussetzungen, sofort nach Import nutzbar

{
  "meta": {
    "instanceId": "4b3a33c57adb2434e3d3c2fca2e5f3cdf6673361f4415a580cb62dfe366d1bcf"
  },
  "nodes": [
    {
      "id": "b0865f1f-7e49-48af-829c-49202779ff8e",
      "name": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -944,
        -16
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "a4eb6e0e-999e-4056-9421-abac99c18df1",
      "name": "Jede XLSX lesen",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -496,
        -16
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "ee1d615e-5e7c-4696-b633-113b323536dc",
      "name": "XLSX-Dateien von Festplatte lesen",
      "type": "n8n-nodes-base.readWriteFile",
      "position": [
        -720,
        -16
      ],
      "parameters": {
        "options": {},
        "fileSelector": "n8n_files/*.xlsx"
      },
      "typeVersion": 1
    },
    {
      "id": "8d2bf3e9-2d8a-4279-90e0-3a1c8b49b683",
      "name": "Mehrblättrige Excel-Datei erstellen",
      "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": "Daten sammeln und verarbeiten",
      "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": "Workflow-Start und Dateieinlesung",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1024,
        -208
      ],
      "parameters": {
        "color": 5,
        "width": 680,
        "height": 554,
        "content": "## 🚀 1. Workflow Initiation and File Reading\r\r- **When clicking ‘Execute workflow’**: Manually trigger the workflow.\r- **Read XLXS Files from Disk**: Read all `.xlsx` formatted files from the `n8n_files/` directory mounted to the container.\r- **Read each XLXS**: Split all found files into batches, processing one file at a time to extract data individually."
      }
    },
    {
      "id": "generated-7a99b3f6-0daf-4d46-8d17-e9e5cf96472e",
      "name": "Datenextraktion und -verarbeitung",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        0
      ],
      "parameters": {
        "color": 4,
        "width": 704,
        "height": 346,
        "content": "## 📊 2. Data Extraction and Processing\r\r- **Extract from XLSX**: Extract data from a single XLSX file.\r- **Aggregate**: This node is used to summarize the read JSON data into one."
      }
    },
    {
      "id": "generated-998281e0-d0e4-4c79-af6f-c174dd8cf857",
      "name": "Excel-Datei generieren und speichern",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -736
      ],
      "parameters": {
        "width": 706,
        "height": 720,
        "content": "## 📝 3. Generate and Save Multi-Worksheet Excel File\r\r- **Collect and Process Data**: This code node collects data from all files, cleans up empty rows, and prepares an object for each file containing worksheet names, data, and metadata.\r- **Create Multi-Sheet Excel**: This code node merges all processed data into a new Excel file, with each original file corresponding to a worksheet, and additionally creates a summary sheet.\r    - **🔧 Tip:** You need to enable the external module `xlsx` in your n8n environment variables to run it properly.\r- Modify your **docker-compose.xml** file:\r         - Set `NODE_FUNCTION_ALLOW_EXTERNAL=xlsx` in the configuration file (e.g., `docker-compose.yml` or `.env` file).\r         - Delete **“image: n8nio/n8n:latest”** and replace it with **“build: .”**\r      - Please create a Dockerfile file and enter the following content:\r```\r      FROM n8nio/n8n:latest\r      USER root\r      RUN npm install xlsx\rENV NODE_FUNCTION_ALLOW_EXTERNAL=xlsx\r      ENV NODE_PATH=/home/node/node_modules\r      USER node\r```\r\r- **Write Files from Disk**: Save the final generated Excel files to the `n8n_files/output/` directory."
      }
    },
    {
      "id": "88841064-e087-47f9-97bc-8eb94635d651",
      "name": "XLSX auf Festplatte speichern",
      "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 zu Json-Liste",
      "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": "Mehrere Json zu Einzel-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
          }
        ]
      ]
    }
  }
}