ExcelJS: Merge CSV or XLSX files into one

Published Apr 28, 2023

So you need to merge several files together. I recently bumped into something similar on my current project. So here’s how I’ve done it.

In my case input files were just regular read streams, but it can be just regular files stored on your system. ExcelJS can read them as well.

import { PassThrough } from 'stream'
import Excel from 'exceljs'

function mergeFiles(files: PassThrough[], sheetName: string) {
  //
}

First of all we need to create new workbook.

const workbook = new Excel.Workbook();
const sheet = workbook.addWorksheet(sheetName);

Now let’s get to looping through our files in order to merge them.

let header;
const newRows = [];

for await (const file of files) {
    const fileWorkbook = new Excel.Workbook();

    // Here we read a file
    const oldSheet = await fileWorkbook.csv.read(file, {
        parserOptions: {
            ignoreEmpty: false
        },
    });

    const rows = [];
    // Then we loop through rows
    oldSheet.getRows(1, oldSheet.rowCount).forEach((row) => {
        const rowValues = [];
        // This is important in order to preserve any rows 
        // that have just empty values ('', etc.)
        row.eachCell({
            includeEmpty: true 
        }, (c) => {
            rowValues.push(c.value);
        });

        // Each row is an array of values, sometimes it could be empty.
        // We don't need that.
        if (rowValues.length > 0) {
            rows.push(rowValues);
        }
    });

    // The first row is always gonna be a header
    header = rows.shift();
    newRows.push(...rows);
}

The final step it to save and export our new file.

sheet.addRows([header, ...newRows]);

const pass = new stream.PassThrough();
workbook.csv.write(pass, {
    sheetName: sheetName,
    dateFormat: 'DD/MM/YYYY',
});

The complete code is as follows:

async mergeFiles(files: stream.PassThrough[], sheetName: string): Promise < stream.PassThrough > {
    const workbook = new Excel.Workbook();
    const sheet = workbook.addWorksheet(sheetName);

    let header;
    const newRows = [];

    for await (const file of files) {
        const fileWorkbook = new Excel.Workbook();

        const oldSheet = await fileWorkbook.csv.read(file, {
            parserOptions: {
                ignoreEmpty: false
            },
        });

        const rows = [];

        oldSheet.getRows(1, oldSheet.rowCount).forEach((row) => {
            const rowValues = [];

            row.eachCell({
                includeEmpty: true
            }, (c) => {
                rowValues.push(c.value);
            });

            if (rowValues.length > 0) {
                rows.push(rowValues);
            }
        });

        
        header = rows.shift();
        newRows.push(...rows);
    }

    sheet.addRows([header, ...newRows]);

    const pass = new stream.PassThrough();
    workbook.csv.write(pass, {
        sheetName: sheetName,
        dateFormat: 'DD/MM/YYYY',
    });

    return pass;
}

Published Apr 28, 2023

Software Engineer, PhD StudentFind me on GitHub