Combining multiple Excel files into single Excel File in Node.js

Question

I am trying to Upload 4 files of 391 MB each in node server. I Need an output where node will return me a single file where all 4 request files will merge in it. But i am getting response from the server but not getting any attachment excel file from it.

The given commented part indicates that I tried that part too and not getting any expected response

const express = require('express');
const multer = require('multer');
const cors = require('cors');
const xlsx = require('xlsx');
const fs = require('fs');
const ExcelJs = require('exceljs');

const app = express();

app.use(cors());
const port = 8000;


const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        cb(null, 'uploads');
    },
    filename: (req, file, cb) => {
        cb(null, file.originalname);
    }
});

function parseXLSXContent(filePath){
    try{
        const workbook = xlsx.readFile(filePath);
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        const parsedData = xlsx.utils.sheet_to_json(worksheet);

        return parsedData;
    }
    catch(error){
        console.log("Functional error",error);
        return [];
    }
}

const upload = multer({ storage });

async function mergeExcelFiles(paths){
    const workbook = new ExcelJs.Workbook();
    const worksheet = workbook.addWorksheet('Combined');

    for(const path of paths){
        const parsedData = parseXLSXContent(path);
        worksheet.addRows(parsedData);
    }
    const excelBuffer = await workbook.xlsx.writeBuffer();

    return excelBuffer;
}

async function mergeFiles(uploadedFiles){
    const paths = uploadedFiles.map(file => file.path);
    console.log(paths);
    const excelBuffer = await mergeExcelFiles(paths);
    return excelBuffer
}

process.env["NODE_TLS_REJECT_UNAUTHORIZED"] = 0;

app.post('/upload', upload.array('file', 10), async (req, res) => {
    let exportFiles = req.files;

    const excelBuffer = await mergeFiles(exportFiles);

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename=CombinedFile.xlsx');
    res.setHeader('Content-Length', excelBuffer.byteLength);
    // res.status(200).send('Files uploaded success..!')
    // res.attachment('Sheet.xlsx');
    console.log('exported...');
    res.send(excelBuffer);
});

app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
});

I want to merge 4 files in a single Excel file. I tried to make multiple sheets within that single file to make the work easy. But server is giving me the response, but not able to return any attachment.

Answer 1

You can create object url using blob, create link element and download it. Good luck.

const fileBufferURL = url.createObjectURL(new Blob([excelBuffer]));
const link = document.createElement("a");
link.setAttribute("download", "mergedFile.xlsx");
link.href = fileBufferURL;
document.body.appendChild(link);
link.click();