Total data is not getting filled in exceljs excel sheet in nodejs

Question

I have a report task in which all data should fill in an excel file and it should be sent by mail to customers from the backend side. I have used excel.js to write an excel file and it is working fine with fewer data. If data is more like 2000 or more than that obj then all data is not getting filled in excel file. following is the example which what I have tried.

Below is the API in which.

router.get('/:type/:fromDate/:toDate',userAuth,(req,res)=>{
    if(!req.query.ids) return res.send({'message':'Please send ID as query',statusCode:2});
    let ids = req.query.ids.split(',');
    var workbook = new Excel.Workbook();
    let type = req.params.type
    workbook.creator = ' 32';
    workbook.lastModifiedBy = '321';
    workbook.created = new Date();
    workbook.modified = new Date();
    workbook.views = [{
            x: 0, y: 0, width: 10000, height: 20000,
            firstSheet: 0, activeTab: 1, visibility: 'visible'
    }]
    var reportWorkSheet = workbook.addWorksheet( req.params.type +' Report', {
        pageSetup: { paperSize: 9, orientation: 'landscape' }
    });
    if(type === 'customers'){
        userCustomerReport(req,res ,ids , reportWorkSheet ,workbook );
    } else if(type === 'interactions'){
        userInteractionReport(req ,res, ids , reportWorkSheet ,workbook , req.params.fromDate , req.params.toDate);
    } else if(type === 'allocations'){
        userAllocationReport(req ,res,ids , reportWorkSheet ,workbook);
    } else return res.send({'message':'Please check the request type',statusCode:2});
})



commonColomns = () => ([
    { header: 'Customer Name', key: 'cName', width: 25, style: { font: { size: 12 } } },
    { header: 'Customer Phone', key: 'cPhone', width: 35, style: { font: { size: 12 } } },
    { header: 'Customer Email', key: 'cEmail', width: 35, style: { font: { size: 12 } } },
    { header: 'Customer Company Name', key: 'cCompName', width: 18, style: { font: { size: 12 } } },
    { header: 'Assigned to name', key: 'assignedTName', width: 18, style: { font: { size: 12 } } },
    { header: 'Assigned from name ', key: 'assignedFName', width: 20, style: { font: { size: 12 } } }
]);


// here i am generation all JSON data .

function  userInteractionReport(req ,res , ids ,reportWorkSheet , workbook , fromDate , toDate) {
    let idString = req.query.ids.split(',');
    let id =[];
    idString.forEach(element => {id.push(new ObjectID(element));});
    Interaction.aggregate([
        { $match:{$or: [{"assigned.toId":{$in:id}},{"assigned.fromId":{$in:id}}] ,createdTimeStamp : {$gte:Number(fromDate),$lt:Number(toDate)}} },
        { "$project": {
            "assigned": 1,
            "type": 1,
            "priority": 1,
            "customer": 1,
            "customFields": 1,
            "dateTime": 1,
            "notes":1,
            "length": { "$size": "$customFields" }
        }},
        { "$sort": { "length": -1 } },
    ])
    .then((interactions)=>{
        if(!interactions[0]){
            return res.send({'message':'No data found',statusCode:1 , "data":0})
        }
        let columns = commonColomns();
            columns.push({ header: 'type', key: 'type', width: 25, style: { font: { size: 12 } } });
            columns.push({ header: 'priority', key: 'priority', width: 25, style: { font: { size: 12 } } });
            columns.push({ header: 'Company Address', key: 'cAddress', width: 25, style: { font: { size: 12 } } });
            columns.push({ header: 'Key Decision Maker Name', key: 'kdm', width: 25, style: { font: { size: 12 } } });
            columns.push({ header: 'Key Decision Maker Phone', key: 'kdmPhone', width: 25, style: { font: { size: 12 } } });
            columns.push({ header: 'Date', key: 'dateTime', width: 25, style: { font: { size: 12 } } });
            columns.push({ header: 'Notes', key: 'notes', width: 25, style: { font: { size: 12 } } });
            for (let i = 0; i < interactions[0].customFields.length; i++) {
                columns.push({ header: interactions[0].customFields[i].dName , key: interactions[0].customFields[i].dName, width: 25, style: { font: { size: 12 } } });
            }
            reportWorkSheet.columns = columns;
            interactions.forEach(interaction => {
                let  assignedTo  = interaction.assigned.toName ? interaction.assigned.toName : '';
                let  assignedFrom  = interaction.assigned.fromName ? interaction.assigned.fromName : '';
                let  companyName = interaction.customer.company ? interaction.customer.company.name : '';
                let  cAddress ;
                let  kdm,kdmPhone ;
                if(interaction.customer.company.address){
                    let companyAddress = interaction.customer.company.address ;           
                    cAddress = companyAddress.street ? companyAddress.street+' ,' : '' ;
                    cAddress = cAddress + (companyAddress.city ? companyAddress.city + ' ,' :'' );
                    cAddress = cAddress + (companyAddress.state ? companyAddress.state +' ,' :'') ;
                    cAddress = cAddress+ (companyAddress.country ? companyAddress.country+' ,':'') ;
                    cAddress = cAddress + (companyAddress.pincode ? companyAddress.pincode +' ,' :'');                    
                }

                if(interaction.customer.company.kdm){
                    kdm = interaction.customer.company.kdm.fName;
                    kdmPhone =  interaction.customer.company.kdm.phone;
                }

                let row = {
                    cName:interaction.customer.fName || '' + ' '+interaction.customer.lName || '',
                    cPhone : interaction.customer.phone.join(','),
                    assignedTName : assignedTo,
                    assignedFName : assignedFrom,
                    cEmail : interaction.email || ' ',
                    source : interaction.source || '',
                    type : interaction.type || '',
                    dateTime : interaction.dateTime ? new Date(interaction.dateTime) : '',
                    notes : interaction.notes || '',
                    priority : interaction.priority === 1 ? "High" : interaction.priority === 2 ? "Medium" : interaction.priority === 3 ? "Low" : " " ,
                    status : interaction.status||'',
                    cCompName : companyName,
                    cAddress : cAddress,
                    kdm :kdm,
                    kdmPhone:kdmPhone
                }

                for (let i = 0; i < interaction.customFields.length; i++) {
                    row[interaction.customFields[i].dName] = interaction.customFields[i].type === "dateTime" ? moment(interaction.customFields[i].value).format('l, h:mm:ss a')  : interaction.customFields[i].value || " ";
                }

                reportWorkSheet.addRow(row);
            });
            return interactions;
    }).then((interactions)=>{
        writeWorkbook(workbook , req);
        res.send({"messgae":"report send sucessfully" , "statusCode":0 ,"data":""})
    }).catch((e)=>{
        console.log(e);
    })
}


function writeWorkbook(workbook , req) {
    workbook.xlsx.writeFile('templates/excel/Report.xlsx')
    .then(function () {
        sendMail(req);
        console.log("report send successfully ");
    });
}

After writing the excel file attaching it to mail and sending it.

Answer 1

in new version of exceljs@4.4.0 u may use option ignoreNodes to save ur RAM

const response = await fetch("abc.com")

const arrayBuffer = await response.arrayBuffer()

const workbook = new ExcelJS.Workbook();

await workbook.xlsx.load(arrayBuffer, {
        ignoreNodes: [
            "dataValidations",
            "sheetPr",
            "dimension",
            "sheetViews",
            "sheetFormatPr",
            "cols",
            "sheetData",
            "autoFilter",
            "mergeCells",
            "rowBreaks",
            "hyperlinks",
            "pageMargins",
            "dataValidations",
            "pageSetup",
            "headerFooter",
            "printOptions",
            "picture",
            "drawing",
            "sheetProtection",
            "tableParts",
            "conditionalFormatting",
            "extLst",
        ],
});

without ignoreNodes - 30MB downloaded.xlsx raise RAM usage to 3200-3500MB by nodeJS process

with ignoreNodes - 30MB downloaded.xlsx raise RAM usage to 380-450MB by nodeJS process

standard (without exceljs load) nodeJS process RAM usage in this project 200-220MB

Answer 2

I solved this issue with this code. I tried to use stream instead of buffer. I am code this using Typescript, But the problem is when the data hit > 250K row it will said heap out of memory. So for a large data, instead of using Nodejs, used Golang, very powerful and very fast compiling and writing data.

If you using this and still got the heap memory error try running this instead.

node --max-old-space-size=<enter amount of more memory here> index.js

for example

(6GB-like) : node --max-old-space-size=6124 index.js

or

(12GB-like) : node --max-old-space-size=12028 index.js

instead of running like we usually do.

node index.js

But setting the max memory is not recommended since, it take more resource for RAM to procced and will slow down the server performance.

Note, I am using node 14.

export async function exportToexcel(res: any, jsonSetting: ObjExcelSetting): Promise<any> {

  // Initiate Excel Workbook
  const workBook = new excel.stream.xlsx.WorkbookWriter({
    // the most important part, dont forget to set this
    stream: res
  });

  // initiate into variable from jsonSetting; column, data, sheetname
  let { column, data, sheetname } = jsonSetting;
  // default name for sheet if null
  let defaultsheetname = sheetname ?? "Sheet"
  // Add the worksheet
  const workSheet = workBook.addWorksheet(defaultsheetname);
  // Set the column
  workSheet.columns = column
  // Looping for adding the data to excel
  console.log("Looping for adding the data to excel")
  for (let i = 0; i < data.length; i++) {
    const r = i + 1;
    
    // dont forget to commit for **every** loop
    workSheet.addRow(data[i]).commit();
  }
  // commit the workbook
  console.log("Commit the excel workboom")
  await workBook.commit();
}   

res: any -> refers to response, request method in expressjs or something similar in another framework like nestjs

jsonSetting: ObjExcelSetting -> refers metadata for my function, included, column, sheetname, and jsondata. For more information, read exceljs Documentation, ObjexcelSetting is just typescript annotation, if u using Javascript don't use that

const workBook = new excel ... -> refers to the initiate module like const excel = require('exceljs') or import * as excel from 'exceljs'

The data will return in Blob, so u guess it how could you parse it.

Answer 3

Try streaming:

 // pipe from stream
 const workbook = new Excel.Workbook()
 workbook.useSharedStrings = false

 stream.pipe(workbook.xlsx.createInputStream())

However, Memory management seems to be an ongoing issue with this library (as of the time of this answer). See this github issue for reference:

https://github.com/exceljs/exceljs/issues/709 and these related issues

You may want to use another library to handle massive excel files (like this one: Node-libxl. This extenesion is a paid extension btw).

If you can use Python, you can also try OpenPyxl