Transforming an Excel document to JSON using Node.js

Consider an Excel spreadsheet as a data square, where the first row contains object keys and the remaining rows hold object values. The desired output is a list of objects in JSON format.
Using the dotted notation method, the key row (row 0) with fields such as firstName, lastName, address.street, address.city, address.state, and address.zip, would produce a document with first and last names, as well as an embedded document named address, containing the address.
To set an object property (of an object property) in JavaScript using its string name, use the assign function by VisioN.
To begin, load the Excel module.
For a quick and effective solution that worked for me, I used the server.js and index.html files.
Note: The code presented is not elegant but will get the job done.

Question:

I am attempting to transform an Excel file into JSON format using the convert-json node API.

The csv-to-json function works both on my local machine and server. However, I am unable to perform other tasks on the server despite being successful on my local machine. Here is the code I used: https://gist.github.com/debasreedash/33efd4473ba8b344a5ac.

After the first console.log, while attempting to parse the excel file, the server crashes. Refer to this link for a visual representation: http://imgur.com/lzUy8sc.

After downloading and installing excel drivers on the server, I realized that it wasn’t the issue causing the problem. Has anyone encountered this problem before? If you require additional information, please inform me.


Solution 1:

Apply this technique to achieve clear comprehension and analysis.

npm install --save excel'
var xls = require('excel');
xls('Sheet.xlsx', function(err, data) {
  if(err) throw err;
    // data is an array of arrays
});

The returned data is an array of arrays as stated. To enable flexibility, we desire it to be in JSON format.

This function is designed to transform an array of arrays into JSON format.

function convertToJSON(array) {
  var first = array[0].join()
  var headers = first.split(',');
  var jsonData = [];
  for ( var i = 1, length = array.length; i < length; i++ )
  {
    var myRow = array[i].join();
    var row = myRow.split(',');
    var data = {};
    for ( var x = 0; x < row.length; x++ )
    {
      data[headers[x]] = row[x];
    }
    jsonData.push(data);
  }
  return jsonData;
};

Then:

xlsx('tasks.xlsx', function(err,data) {
    if(err) throw err;
    //console.log(jsonDataArray(data));
    console.log(JSON.stringify(convertToJSON(data)));
    //console.log(data);
});


Solution 2:


Concentrating on the initial segment of the inquiry’s first sentence, the task is to convert
convert excel
into JSON format.

Consider an Excel spreadsheet to be a square of information, where the initial row consists of object keys and all the subsequent rows contain object values. The required outcome is a collection of objects in JSON format.

Enhancements made to the previous solution include eliminating redundant splits and joins, which may lead to invalid transformations if keys or values include commas. Additionally, dotted strings in keys now imply the use of coffeescript. Finally, the file has been written.

A document containing first and last names and an embedded address document can be generated per row by using the key row (0) that includes firstName, lastName, address.street, address.city, address.state, and address.zip in dotted notation.

How can one set an object property in JavaScript by providing its string name, as demonstrated by VisioN’s function assignment?

First, load the excel module

npm install excel --save-dev

Not elegant, just get’r’done code

fs = require 'fs'
excel = require 'excel'
FILES = [
  {src: 'input.xlsx', dst: 'output.json'}
  ]
# Assign values to dotted property names - set values on sub-objects
assign = (obj, key, value) ->
  # Because we recurse, a key may be a dotted string or a previously split
  # dotted string.
  key = key.split '.' unless typeof key is 'object'
  if key.length > 1
    e = key.shift()
    obj[e] = if Object.prototype.toString.call(obj[e]) is "[object Object]" then obj[e] else {}
    assign obj[e], key, value
  else
    obj[key[0]] = value
# The excel module reads sheet 0 from specified xlsx file
process = (src, dst) ->
  excel src, (err, data) ->
    throw err if err 
    keys = data[0]
    rows = data[1..]
    result = []
    for row in rows
      item = {}
      assign item, keys[index], value for value, index in row
      result.push item
    fs.writeFile dst, JSON.stringify(result, null, 2), (err) ->
      if err
        console.error("Error writing file #{dst}", err)
      else
        console.log "Updated #{dst}"
process file.src, file.dst for file in FILES



Solution 3:


Discover a swift and precise resolution that suits my needs.

server.js

let express = require('express'),
    app = express(),
    bodyParser = require('body-parser'),
    multer = require('multer'),
    crypto = require('crypto'),
    xlsxtojson = require('xlsx-to-json'),
    xlstojson = require("xls-to-json");
let fileExtension = require('file-extension');
    app.use(bodyParser.json());  
    let storage = multer.diskStorage({ //multers disk storage settings
        destination: function (req, file, cb) {
            cb(null, './input/')
        },
        filename: function (req, file, cb) {
            crypto.pseudoRandomBytes(16, function (err, raw) {
                cb(null, raw.toString('hex') + Date.now() + '.' + fileExtension(file.mimetype));
                });
        }
    });
    let upload = multer({storage: storage}).single('file');
    /** Method to handle the form submit */
    app.post('/sendFile', function(req, res) {
        let excel2json;
        upload(req,res,function(err){
            if(err){
                 res.json({error_code:401,err_desc:err});
                 return;
            }
            if(!req.file){
                res.json({error_code:404,err_desc:"File not found!"});
                return;
            }
            if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
                excel2json = xlsxtojson;
            } else {
                excel2json = xlstojson;
            }
           //  code to convert excel data to json  format
            excel2json({
                input: req.file.path,  
                output: "output/"+Date.now()+".json", // output json 
                lowerCaseHeaders:true
            }, function(err, result) {
                if(err) {
                  res.json(err);
                } else {
                  res.json(result);
                }
            });
        })
    });
    // load index file to upload file on http://localhost:3000/
    app.get('/',function(req,res){
        res.sendFile(__dirname + "/index.html");
    });
    app.listen('3000', function(){
        console.log('Server running on port 3000');
    });

index.html





    Excel to Json in nodejs | jsonworld
    
    
    
    
    


Excel to Json in nodejs

source : jsonworld

Frequently Asked Questions