2

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?

Valip
  • 4,440
  • 19
  • 79
  • 150
  • there is library name panda in python which is built for large number data. You can that – nitishk72 May 08 '18 at 08:36
  • The `xl.Workbook` must support generating the xlsx file in chunks (and save them on the disk). Then, you can use the file or send the file for download – Binar Web May 08 '18 at 08:36
  • @nitishk72 thank you, but I'm using node.js... – Valip May 08 '18 at 08:37
  • It looks like the `excel4node` library always, no matter what, builds the XLSX XML tree in-memory. For data this large, you'll probably need another library. https://github.com/natergj/excel4node/blob/5490ce0d6a5b8d534fd782a49744bc42b64da937/source/lib/worksheet/builder.js#L454-L505 – AKX May 08 '18 at 08:39
  • If you only need the data to be openable by Excel (and not strictly be an XLSX file), you could write it as plain XML (like the original JSON structure, but in XML). Excel is surprisingly good at parsing that. Or, of course, CSV and TSV could be options for you. – AKX May 08 '18 at 08:41
  • @AKX I was thinking about writing it to a CSV file. Do you know any good library for doing this? – Valip May 08 '18 at 08:44
  • You don't really need a library for CSV/TSV; just write lines into a stream using the Node standard library. Just be careful about your data containing your chosen delimiter (be it a comma, semicolon or tab), and newlines. If your data _does_ contain those, you might be better off with some library. http://csv.adaltas.com/stringify/ looks decent. – AKX May 08 '18 at 08:46

1 Answers1

3

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/

Bikesh M
  • 8,163
  • 6
  • 40
  • 52