CODEWITHSUNDEEP
node.jsexcel
tomalex
tomalex

Reputation: 1271

JSON to Excel convertion in Nodejs

I'm trying to convert large amount of json data to excel and tried couple of modules Below are my findings, if anyone used better node module which handle more data please let me know so that i can explore

json2xls

JSON array with 100000 length took 402574ms once i exceeded to 200000 it failed with this error FATAL ERROR: CALL_AND_RETRY_2 Allocation failed - process out of memory

node-xls

JSON array with 100000 length took 444578ms

I tried this in windows 7 system with 8GB RAM, Intel Core i7, CPU @ 2.10Ghz - 2.70Ghz

Upvotes: 11

Views: 44800

Answers (4)

Sylvain P
Sylvain P

Reputation: 91

If your JSON is already properly formatted, you juste have to do:

const json2xls = require('json2xls');

// Example JSON
const json = [{firstName: 'Bob', name: 'Lennon'}, {firstName: 'Jack', name: 'Sparrow'}]

const xls = json2xls(json);
fs.writeFileSync('exported.xlsx', xls, 'binary');

Works fine, and very simple.

Upvotes: 0

Chuck McKnight
Chuck McKnight

Reputation: 119

In Ratul Das' answer, there is a typo on the following line:

var newWB = xlsx.book_new()

The code should read:

var newWB = xslx.utils.book_new()

The snippet below is the code I use to generate an Excel spreadsheet from an array of JSON objects named imageList:

const workSheet = XLSX.utils.json_to_sheet(imageList);
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "Product Image Catalog");

// Generate buffer
XLSX.write(workBook, {bookType: 'xlsx', type: 'buffer'})

// Binary String
XLSX.write(workBook, {bookType: 'xlsx', type: 'binary'})

XLSX.writeFile(workBook, 'image-catalog.xlsx')

Building the buffer helps with large amounts of data.

Upvotes: 0

Ratul Das
Ratul Das

Reputation: 33

If its a nodejs project then do this,

const xlsx = require("xlsx")//npm install xlsx
const fs = require("fs")//npm install fs
var rawFile = fs.readFileSync("./datas.json")//dir of your json file as param
var raw = JSON.parse(rawFile)
var files  = []
for (each in raw){
    files.push(raw[each])
    }  
   var obj = files.map((e) =>{
        return e
       })

   var newWB = xlsx.book_new()

   var newWS = xlsx.utils.json_to_sheet(obj)

   xlsx.utils.book_append_sheet(newWB,newWS,"name")//workbook name as param

   xlsx.writeFile(newWB,"Sample-Sales-Data.xlsx")//file name as param

Upvotes: 1

Maximus
Maximus

Reputation: 164

First push your data into a temporary array with required column and then convert it into xls, I have done it in following manner: 

// use the below package to convert json to xls
var json2xls = require('json2xls');

json.forEach(function(instance, indexx,record){
        var tempArry = {
            'ColoumnName1' : record[indexx].columnNameVlaue,
            'ColoumnName2' : record[indexx].columnNameVlaue,
            'ColoumnName3' : record[indexx].columnNameVlaue,
            'ColoumnName4' : record[indexx].columnNameVlaue
        }
        jsonArray.push(tempArry);
    });
//this code is for sorting  xls with required value
    jsonArray.sort(function(a, b) {
        return parseFloat(b.ColoumnName4) - parseFloat(a.ColoumnName4);
    });
    var xls = json2xls(jsonArray);

    fs.writeFileSync('yourXLName.xlsx', xls, 'binary');

Dont try to add all the data into the excel file, use the specific columns you want in the file to be saved.

Upvotes: 8

Related Questions