CODEWITHSUNDEEP
node.jsexcelexcel4node
Valip
Valip

Reputation: 4650

How to export a large JSON data to an excel file

I'm trying to export an array of 250000 objects to an excel file using node.js but the server fails everytime with:

FATAL ERROR: CALL_AND_RETRY_LAST Allocation failed - JavaScript heap out of memory

I'm always starting the server with npm --max-old-space-size=8000 start

This is the part of my code where I'm trying to generate the excel file using the excel4node module:

// Create a new instance of a Workbook class
var wb = new xl.Workbook();

// Add Worksheets to the workbook
var ws = wb.addWorksheet('Sheet 1');


for(const [idx, request] of report.entries()) {
  let counter = 1;
  for(const [_, val] of Object.keys(request)) {
    ws.cell(idx + 1, counter).string(val);
    counter++;
  }
}

wb.write('Excel.xlsx');

This is the content of the report array:

[
    {
        "Page URL": "http://www.example.com",
        "Request URL": "http://use.typekit.net/yse3oeo.js",
        "Domain": "typekit.net",
        "OP Tag Category": "Data Management",
        "OP Tag Name": "Adobe TypeKit",
        "Registrar Name": "Adobe Systems Incorporated",
        "Method": "GET",
        "Type": "Script",
        "mimeType": "text/javascript",
        "Status": 200,
        "Content Encoding": "gzip",
        "Encoded Data Length": 8028,
        "Action": null,
        "IP Address": "92.123.20.219",
        "Geolocation": "FR",
        "Match Regex": null,
        "Not Match Regex": null,
        "Error": null,
        "Chrome Initiator": "http://example.com",
        "Final Page URL": null,
        "Initial Page Status": null
    }
    ...250000 more objects
]

I also tried to use the mongo-xlsx module, but it fails with the same error...

Is there any way to improve my code efficiency in order to decrease the memory usage? Or maybe there better ways of doing this?

Upvotes: 2

Views: 11571

Answers (1)

Bikesh M
Bikesh M

Reputation: 8383

you can use exceljs node module and stream the data 

var options = {
    filename: './streamed-workbook.xlsx',
    useStyles: true,
    useSharedStrings: true
};
var workbook = new Excel.stream.xlsx.WorkbookWriter(options);

worksheet.addRow({
   id: i,
   name: theName,
   etc: someOtherDetail
}).commit();

refer: http://wiki.workassis.com/node-js-write-large-data-to-excel/

Upvotes: 3

Related Questions