Node Js heap out for memory using excelJS package

Question

I am using nodejs to create excel files on the server, when calls is greater then 20 heap memory error I got. I have use following technique as well but all in vain.

npm install -g increase-memory-limit increase-memory-limit

I have follow below link as well but still no luck. any suggestion ?

Node.js heap out of memory

pageNumbers = [1,2,3,4,5,6,7,8,9,10,11....40]; //example

for (const pageNumbers of p) {
    data = await getData(p,limit,organization_id,token, baseurl, sideFilter, null, client).then(response=>{
        return response;
    });

    await readAndWrite(data);
}


let getData = (page,limit, organization_id,token, baseurl, sideFilter, worksheet, client) =>{

  return new Promise((resolve, reject) => {

    axios.post(baseurl+`/v2/get-export`, {
      page:page,
      organization_id:organization_id,
      per_page:limit,
      filter: "",
      sorted:"",
      ...sideFilter
    },{ headers: {"Authorization" : `Bearer ${token}`} }).then(function (response) {

      let dataTemp = response.data.data.data.map((t,i)=>{
        var parent = '';
        var child  = '';
        if (t.teams_relation && t.teams_relation.length > 0) {
          //business logic
        }


        return {
          ...t, 
          content     :  t.content ? convert(t.content) : '--',
          parent_team : parent,
          child_team  : child,
          reopened_comments_latest_content : t.reopened_comments_latest?t.reopened_comments_latest.content:'--',
          solved_comments_latest_content : t.solved_comments_latest?t.solved_comments_latest.content:'--',
          closed_comments_latest_content : t.closed_comments_latest?t.closed_comments_latest.content:'--',
          requester_comments_latest_content : t.requester_comments_latest?t.requester_comments_latest.content:'--',
          tags_impolode : t.tags?t.tags.length > 0 ? t.tags.join(", ") : '--':'--',
          organization : t.organization?t.organization.name:'--',
          comments : convert ( t.comments.map(function(c){return c.content;}).join(",") ),
          user_name : t.user ? t.user.name : '',
          user_email : t.user ? t.user.email : '',
          escalated_at : t.escalated_at ? t.escalated_at : '',
          region_Cluster : getDynamicFieldData(t.dynamicFieldsValue, "5f2ff7557a17f166076f2aa2"),
          mawid_Facility_Name : getDynamicFieldData(t.dynamicFieldsValue, "5f3000d1486e94459b6531c2"),
          hospital : getDynamicFieldData(t.dynamicFieldsValue, "609274609d16481196010c6d"),
        }
      });
        resolve(dataTemp);
    }).catch(function (error) {
      reject(error);
    });
  });
}


let getDynamicFieldData = (data, id) => {
    let df = data && data.length>0 ? data.find(d => d._id === id ):null;
    if (df)
        return df.value ;
    else
        return '--';
}


let readAndWrite = async (data) => {
  if (fs.existsSync('./export.xlsx')) {
    const newWorkbook = new excel.Workbook();
    await newWorkbook.xlsx.readFile('export.xlsx').then(() => {
      console.log("read");
    })
    .catch((err) => {
      console.log("error ha yeh while reading", err);
    });
    const newworksheet = newWorkbook.getWorksheet('My Sheet');
    // console.log('columns check ', typeof newworksheet.columns);
    newworksheet.columns = columns;
    await newworksheet.addRows(data);
    await newWorkbook.xlsx.writeFile('export.xlsx').then(() => {
      console.log("updated");
    })
    .catch((err) => {
      console.log("error ha yeh while updating", err);
    });
    delete newworksheet;
    delete newWorkbook;

  } else {
    const workbook1 = new excel.Workbook();
    const worksheet1 = workbook1.addWorksheet("My Sheet");
    worksheet1.columns = columns;
    await worksheet1.addRows(data);
    await workbook1.xlsx.writeFile('export.xlsx').then(() => {
      console.log("saved");
    })
    .catch((err) => {
      console.log("error ha yeh while writing", err);
    });

    delete workbook1;
    delete worksheet1;
  }
  
}

Error npm ERR! code ELIFECYCLE npm ERR! errno 137 npm ERR! wapp-permutas-backend@1.0.0 start: node -max_old_space_size=20480 server.js npm ERR! Exit status 137

@Gagantous you have to add row with loop when you get data from server then you have to commit worksheet.addRow(data).commit(); await Promise.all(promises); await workbook.commit(); — hu7sy, Feb 22 '22 at 11:19
it depends as I am using promises to fetch data from the server because i have data more then 100K normally so i used to get data as chunk — hu7sy, Feb 22 '22 at 11:30

score 0 · Answer 1 · answered Nov 17 '21 at 07:07

0

What version of NodeJS you are using? I've read in the link you provided that some people solved it by upgrading to the latest version. Also did you used --optimize-for-size? people reported this helped to solve the issue after specifying the --max-old-space-size

answered Nov 17 '21 at 07:07

niiir

357
4
9

nodejs version = v16.13.0 – hu7sy Nov 17 '21 at 08:53
npm version = 8.1.0 – hu7sy Nov 17 '21 at 08:53
i have used --max-old-space-size also – hu7sy Nov 17 '21 at 08:55
is this can be issue with pm2 ? – hu7sy Nov 17 '21 at 08:55
http://sendanywhe.re/SDUQ2G2Y controller – hu7sy Nov 17 '21 at 08:59
did you used `--optimize-for-size`? – niiir Nov 17 '21 at 10:49
no, is this have to use with pm2 ? – hu7sy Nov 17 '21 at 11:32
this is a Node.js optimization flag. btw, have a look [here](https://stackoverflow.com/questions/42963708/node-js-pm2-maximum-memory-limit) maybe this will solve your issue. – niiir Nov 17 '21 at 14:17

score 0 · Answer 2 · answered Feb 22 '22 at 04:10

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.

Node Js heap out for memory using excelJS package

2 Answers2