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

Closed
opened 2017-09-01 05:25:46 +00:00 by jitsu553 · 15 comments
jitsu553 commented 2017-09-01 05:25:46 +00:00 (Migrated from github.com)

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

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 commented 2018-01-10 22:04:12 +00:00 (Migrated from github.com)

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);

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 commented 2018-01-11 06:50:37 +00:00 (Migrated from github.com)

try using buffer instead of base64

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

or

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

try using `buffer` instead of `base64` `var wb = XLSX.read(bstr, { type: "buffer" });` or ` var wbbuff = XLSX.write(wb, { type: 'buffer' }); `
reviewher commented 2018-01-11 07:32:27 +00:00 (Migrated from github.com)

@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 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: ```js /* 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 commented 2018-01-12 13:17:38 +00:00 (Migrated from github.com)

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 <an Object with map 0x52c36a...

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::Localv8::Value, v8::Localv8::Message) [/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::Handlev8::internal::Object, 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.

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 <JS Object> 1: encode_row [/Users/lukasz/Documents/Projects/project/node_modules/xlsx/xlsx.js:~2579] [pc=0x2b890a89414e] (this=0xd21b423e2d1 <JS Global Object>,row=75644) 2: write_ws_biff8 [/Users/lukasz/Documents/Projects/-reports/node_modules/xlsx/xlsx.js:~17106] [pc=0x2b890a44369e] (this=0xd21b423e2d1 <JS Global Object>,idx=0,opts=0x647d1be2869 <an Object with map 0x52c36a... 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::Value>, v8::Local<v8::Message>) [/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<unsigned char const>, v8::internal::PretenureFlag) [/usr/local/bin/node] 8: v8::internal::Factory::NumberToString(v8::internal::Handle<v8::internal::Object>, 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 commented 2018-11-28 02:25:22 +00:00 (Migrated from github.com)

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'
}));
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 commented 2018-12-12 23:20:25 +00:00 (Migrated from github.com)

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.
image

image

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. ![image](https://user-images.githubusercontent.com/6012693/49974735-4e16cc80-ff00-11e8-8b5a-42b432e09897.png) ![image](https://user-images.githubusercontent.com/6012693/49974740-540cad80-ff00-11e8-8e6a-d59e3923a3f7.png)
genyklemberg commented 2019-03-01 15:20:11 +00:00 (Migrated from github.com)

@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"

> @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 commented 2019-06-01 03:05:39 +00:00 (Migrated from github.com)

Did anyone find resolution to this issue?

Did anyone find resolution to this issue?
davidoskay commented 2019-09-25 22:24:18 +00:00 (Migrated from github.com)

Same problem

Same problem
zoreet commented 2020-02-16 18:13:10 +00:00 (Migrated from github.com)

Having the same problem too. Using SheetJS in a browser

Having the same problem too. Using SheetJS in a browser
alobaidizt commented 2020-08-19 19:58:53 +00:00 (Migrated from github.com)

having same issue, anyone found a solution?

having same issue, anyone found a solution?
Bmastaz commented 2020-09-12 16:51:11 +00:00 (Migrated from github.com)

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.

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 commented 2020-11-11 16:41:03 +00:00 (Migrated from github.com)

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

Is it possible to append to a sheet in a file without opening the whole file into memory?
zeyadetman commented 2020-12-10 06:37:11 +00:00 (Migrated from github.com)

@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);
    });
  }),
> @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: > > ```js > /* 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 ```js 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); }); }), ```
reviewher commented 2021-09-19 00:49:04 +00:00 (Migrated from github.com)

Grouping large file write issues into #77

Grouping large file write issues into #77
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#798
No description provided.