Sheetjs: XLSX.write generates a memory error while dealing with large json data leaving JavaScript heap out of memory error

[jitsu553]

So i have a large json of array object and length of this json array is around 100000 , which i first convert it into a sheet using json_to_sheet , push it into a workbook and then write the given workbook into xlsx format using XLSX.write() , which takes a lot memory and eventually nodejs terminates in between process displaying FATAL ERROR: CALL_AND_RETRY_LAST Allocation failed - JavaScript heap out of memory error ..

i have tried to increase heap memory along the node process with --max-old-space-size=8100 which results the same giving the same error

tried it with
1) node version v4.8.1 and npm v2.15.11
2) node version v6.9.4 and npm v3.10.10

code i m using :

var bstr = [].join(""); 
var wopts = { bookType: 'xlsx', bookSST: false, type: 'base64'}; 
var wb = XLSX.read(bstr, { type: "base64" }); 
var ws = XLSX.utils.json_to_sheet(data.dataList); 
wb.Sheets['Sheet1'] = ws; 
var wbbuf = XLSX.write(wb, wopts);  // <---- point at which node uses a lot of memory

and the error generated

<--- Last few GCs --->
  275744 ms: Mark-sweep 1365.7 (1435.0) -> 1365.7 (1435.0) MB, 1640.3 / 0.0 ms [allocation failure] [GC in old space requested].
  277371 ms: Mark-sweep 1365.7 (1435.0) -> 1364.4 (1435.0) MB, 1626.4 / 0.0 ms [allocation failure] [GC in old space requested].
  279008 ms: Mark-sweep 1364.4 (1435.0) -> 1367.5 (1404.0) MB, 1636.7 / 0.0 ms [last resort gc].
  280643 ms: Mark-sweep 1367.5 (1404.0) -> 1370.7 (1404.0) MB, 1635.9 / 0.0 ms [last resort gc].
<--- JS stacktrace --->
==== JS stack trace =========================================
Security context: 0x1b60ef3cfb51 <JS Object>
    2: encode [/home/naitik/BizOs/bizos-group/automobile-dealers/node_modules/xlsx/jszip.js:~29] [pc=0x1e6e7372b380] (this=0x1d02f8efacd9 <an Object with map 0x2515380313e1>,input=0x206915d04201 <Very long string[110957501]>,utf8=0x1b60ef304381 <undefined>)
    3: arguments adaptor frame: 1->2
    4: generate [/home/naitik/BizOs/bizos-group/automobile-dealers/node_modules/xlsx/jszip.js:1362] [p...
FATAL ERROR: CALL_AND_RETRY_LAST Allocation failed - JavaScript heap out of memory
 1: 
node::Abort() [node]
 2: 
0x109624c [node]
 3: 
v8::Utils::ReportApiFailure(char const*, char const*) [node]
 4: 
v8::internal::V8::FatalProcessOutOfMemory(char const*, bool) [node]
 5: 
v8::internal::Factory::NewFillerObject(int, bool, v8::internal::AllocationSpace) [node]
 6: 
v8::internal::Runtime_AllocateInTargetSpace(int, v8::internal::Object**, v8::internal::Isolate*) [node]
 7: 0x1e6e703079a7

[wlodi83]

I am getting the same issue but for method writeFile.

My code is:

let workbook = { SheetNames: [], Sheets: {} };
let result = await makeGetRequest(url, headers, null, cookies);
let data = result.body;
let wb = XLSX.read(data, {raw: true});
let ws = wb.Sheets[wb.SheetNames[0]];

/* Add the sheet name to the list */
let sheetName = key.replace(/\/*\\*\[*\]*\:*\**\?*/g, '').substr(0,30);
workbook.SheetNames.push(sheetName);

/* Load the worksheet object */
workbook.Sheets[key] = ws;
//Synchronous version of temp file creation
const tmpobj = tmp.fileSync({mode: '0644', postfix: '_tab.xlsx'});
XLSX.writeFile(workbook, tmpobj.name);

[shubham-kumar]

try using buffer instead of base64

var wb = XLSX.read(bstr, { type: "buffer" });

or

var wbbuff = XLSX.write(wb, { type: 'buffer' });

[reviewher]

@wlodi83 XLSX eventually has to go through XML text when reading and writing, and V8 has some limitations that you may be encountering. In addition to @shubham-kumar 's suggestion to use the buffer type in nodejs, can you also try writing to xlsb or xls to see if the issue occurs? to control the output format, just change the filename:

/* XLSB */
const tmpobj = tmp.fileSync({mode: '0644', postfix: '_tab.xlsb'});
XLSX.writeFile(workbook, tmpobj.name);

/* XLS */
const tmpobj = tmp.fileSync({mode: '0644', postfix: '_tab.xls'});
XLSX.writeFile(workbook, tmpobj.name);

[wlodi83]

Hi @reviewher, @shubham-kumar,

Neither buffer nor xls/xlsb work. I am getting following error:

<--- Last few GCs --->

364747 ms: Mark-sweep 1376.6 (1435.2) -> 1376.6 (1435.2) MB, 2993.1 / 0.0 ms [allocation failure] [GC in old space requested].
367677 ms: Mark-sweep 1376.6 (1435.2) -> 1376.6 (1435.2) MB, 2929.6 / 0.0 ms [allocation failure] [GC in old space requested].
371880 ms: Mark-sweep 1376.6 (1435.2) -> 1380.2 (1419.2) MB, 4203.1 / 0.0 ms [last resort gc].
374800 ms: Mark-sweep 1380.2 (1419.2) -> 1384.0 (1419.2) MB, 2919.7 / 0.0 ms [last resort gc].

<--- JS stacktrace --->

==== JS stack trace =========================================

Security context: 0x27414f53fa99
1: encode_row [/Users/lukasz/Documents/Projects/project/node_modules/xlsx/xlsx.js:~2579] [pc=0x2b890a89414e] (this=0xd21b423e2d1 ,row=75644)
2: write_ws_biff8 [/Users/lukasz/Documents/Projects/-reports/node_modules/xlsx/xlsx.js:~17106] [pc=0x2b890a44369e] (this=0xd21b423e2d1 ,idx=0,opts=0x647d1be2869

FATAL ERROR: CALL_AND_RETRY_LAST Allocation failed - JavaScript heap out of memory
1: node::Abort() [/usr/local/bin/node]
2: node::FatalException(v8::Isolate, v8::Local, v8::Local) [/usr/local/bin/node]
3: v8::Utils::ReportApiFailure(char const, char const) [/usr/local/bin/node]
4: v8::Utils::ApiCheck(bool, char const, char const) [/usr/local/bin/node]
5: v8::internal::V8::FatalProcessOutOfMemory(char const, bool) [/usr/local/bin/node]
6: v8::internal::Factory::NewRawOneByteString(int, v8::internal::PretenureFlag) [/usr/local/bin/node]
7: v8::internal::Factory::NewStringFromOneByte(v8::internal::Vector, v8::internal::PretenureFlag) [/usr/local/bin/node]
8: v8::internal::Factory::NumberToString(v8::internal::Handle, bool) [/usr/local/bin/node]
9: v8::internal::Runtime_NumberToStringSkipCache(int, v8::internal::Object*, v8::internal::Isolate) [/usr/local/bin/node]
10: 0x2b8909e060c7
11: 0x2b890a89414e
sh: line 1: 7952 Abort trap: 6 node dist/main $PWD/settings.json
npm ERR! code ELIFECYCLE
npm ERR! errno 134
npm ERR! Exit status 134
npm ERR!
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR! /Users/wlodi83/.npm/_logs/2018-01-12T13_06_28_815Z-debug.log

File is quite big. It has around 300k rows. I wonder if I can handle it easily.

[ianchiu28]

get this issue too. allocation failed.

var items = [[],[]....];  // 42000 rows, 30 columns
var ws = xlsx.utils.aoa_to_sheet(items);
var wb = {
    SheetNames: ['sheet'],
    Sheets: {
        sheet: ws
    }
};
res.setHeader('Content-Disposition', 'attachment; filename=export.xlsx');
res.status(200).end(xlsx.write(wb, {
    type: 'buffer',
    bookType: 'xlsx'
}));

[Bmastaz]

I too get the error. Specifically in Internet Explorer. Chrome seems to handle it just fine. Watching the memory as the function runs just eats and eats memory. IE can't handle and just bombs out with an out of memory error.

[genyklemberg]

@shubham-kumar
try using buffer instead of base64

var wb = XLSX.read(bstr, { type: "buffer" });

or

var wbbuff = XLSX.write(wb, { type: 'buffer' });

"buffer" is used only for NodeJS so "base64" should be used

Actually I have issues with reading .xlsx files more than 7Mb using either "base64" or "binary"

[only1chi]

Did anyone find resolution to this issue?

[davidoskay]

Same problem

[zoreet]

Having the same problem too. Using SheetJS in a browser

[alobaidizt]

having same issue, anyone found a solution?

[Bmastaz]

Coming back to this my issue turned out I had memory leak bad function loop. Chrome could handle the leak while IE could not. Fixing the leak solved the error for me. Make sure you are handling the data you are processing clean.

[Noitidart]

Is it possible to append to a sheet in a file without opening the whole file into memory?

[zeyadetman]

@wlodi83 XLSX eventually has to go through XML text when reading and writing, and V8 has some limitations that you may be encountering. In addition to @shubham-kumar 's suggestion to use the buffer type in nodejs, can you also try writing to xlsb or xls to see if the issue occurs? to control the output format, just change the filename:

/* XLSB */
const tmpobj = tmp.fileSync({mode: '0644', postfix: '_tab.xlsb'});
XLSX.writeFile(workbook, tmpobj.name);

/* XLS */
const tmpobj = tmp.fileSync({mode: '0644', postfix: '_tab.xls'});
XLSX.writeFile(workbook, tmpobj.name);

I just use xls file and it works fine with me on node, here's my function

  writeFileAsync: () => new Promise(async (resolve, reject) => {
    const content = XLSX.write(workbook, { type: 'buffer', bookType: 'xls', bookSST: false });
    fs.writeFile(filePath, content, (err) => {
      if (err) {
        return reject(err);
      }
      return resolve(true);
    });
  }),