Multi-File XLSX Merger with Summary Generation - n8n Workflow

• Data analysts and business intelligence specialists who frequently consolidate reports.


• Users running n8n self-hosted instances who need advanced file manipulation capabilities.


• n8n developers looking for complex examples of using the Code n8n node with external libraries.


• Teams needing automated, reliable multi-sheet report generation.

Managing multiple Excel reports can lead to fragmentation and difficulty in reporting. This highly technical n8n workflow solves this by automatically scanning a local directory, reading all individual .xlsx files, extracting their data, and compiling them into a single, unified Excel document.

Crucially, this n8n template uses the powerful Code n8n node along with the external xlsx module to dynamically create multiple worksheets (one for each source file) and generate a comprehensive 'Summary' sheet detailing file counts and record totals. This level of customized file generation showcases the advanced capabilities available within an n8n installation, transforming scattered data into a clean, consolidated report.

This specialized n8n workflow is designed for self-hosted environments requiring external package support.

1. Initiation (The n8n Trigger): The process starts with the manual trigger n8n node, When clicking ‘Execute workflow’.


2. File Scanning: The Read XLXS Files from Disk ReadWriteFile n8n node scans the configured n8nfiles/ directory, identifying all .xlsx files.


3. Batch Processing: The Read each XLXS n8n node (Split In Batches) iterates over the list of files, allowing the n8n workflow to process them one at a time.


4. Data Extraction: The XLSX to Json List n8n node extracts the tabular data from the current Excel file, converting it into JSON items.


5. Data Aggregation: The Mulipte Json to Single Json n8n node (Aggregate) collects all the extracted JSON data from every batch iteration into a single list.


6. Pre-processing (Custom Code): The Collect and Process Data Code n8n node receives the aggregated list. It cleans the data (filtering out empty rows), extracts metadata like original filenames, and organizes the data structure for the next step, ensuring proper sheet naming.


7. Excel Creation (Advanced Code n8n Node): The Create Multi-Sheet Excel Code n8n node executes the core merging logic. It utilizes the external xlsx library to build a new workbook. It loops through the processed data, creating a unique worksheet for each original file, applying sanitization to sheet names (max 31 chars), and finally, generating a dedicated 'Summary' sheet with file metadata. The result is returned as binary data.


8. Final Saving: The Save XLXS to Disk ReadWriteFile n8n node saves the final generated binary Excel file to the specified output path (n8nfiles/output/) on the disk.

To deploy this powerful n8n workflow, follow these steps:

1. Import: Copy the provided JSON code and import it directly into your n8n instance using the 'New' > 'Import from JSON' option.


2. External Library Setup (Crucial): This n8n workflow relies on the external xlsx library. If you are self-hosting via Docker (the recommended method for this n8n template), you must configure your environment:

FROM n8nio/n8n:latest
        USER root
        RUN npm install xlsx
        ENV NODEFUNCTIONALLOWEXTERNAL=xlsx
        ENV NODEPATH=/home/node/nodemodules
        USER node

1. File Preparation: Create the input directory n8n_files/ and place all the .xlsx files you wish to merge inside it.


2. Execution: Click 'Execute Workflow' to run the n8n trigger and initiate the file consolidation.

• When clicking ‘Execute workflow’ (Manual Trigger n8n node): Serves as the starting n8n trigger for the entire automation.


• Read XLXS Files from Disk (ReadWriteFile n8n node): Configured to search for *.xlsx files within the n8nfiles/ directory.


• Read each XLXS (SplitInBatches n8n node): Ensures that the extraction and processing steps handle each file found sequentially, maintaining context for the aggregation step.


• XLSX to Json List (ExtractFromFile n8n node): Extracts raw data from the Excel files into structured JSON format, preparing it for the merging logic.


• Mulipte Json to Single Json (Aggregate n8n node): Collects all the JSON data extracted from every batch into a single payload for efficient centralized processing.


• Collect and Process Data (Code n8n node): Custom logic to iterate through all extracted data, clean rows, normalize sheet names, and compile file metadata (original name, record count) into a master object.


• Create Multi-Sheet Excel (Code n8n node): The core of this n8n workflow. It imports and uses the xlsx external module. It programmatically constructs a multi-sheet workbook based on the collected data and adds a crucial summary report sheet. It outputs the final merged file as binary data.


• Save XLXS to Disk (ReadWriteFile n8n node): Writes the binary Excel file generated by the previous Code n8n node to the output path n8nfiles/output/ using a dynamically generated filename including a timestamp.