Converting excel file to json in nodejs

Question

I am trying to convert an excel file to json by using this api: convert-json node api.

I am able to do it on my local machine but not on my server. Only the csv-to-json works locally and on the server. Here is my code: https://gist.github.com/debasreedash/33efd4473ba8b344a5ac

The server crashes while trying to parse the excel file right after the first console.log. Here is what it looks like: https://i.stack.imgur.com/2bAld.jpg

I thought that it was a problem with not having excel drivers on the server but after downloading and installing it did not work either. Has anyone come across this issue? If you need anymore information then let me know.

Answer 1

Use this method for easy undertsanding and parsing :

npm install --save excel'



var xls = require('excel');

xls('Sheet.xlsx', function(err, data) {
  if(err) throw err;
    // data is an array of arrays
});

As it says, the data it returns is an array of arrays. We want it to be JSON, so that we can do whatever we want with it.

This is a function that converts an array of arrays to JSON:

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

Answer 2

Focusing on the first half of the first line of the question: how to convert Excel to json.

Assume: Excel spreadsheet is a square of data, where the first row is object keys and remaining rows are object values and the desired json is a list of objects.

Improvements on previous answer: remove unnecessary splits and joins (unnecessary and could cause invalid transforms if keys or values contained commas), allow dotted strings in keys to imply nested objects, coffeescript, write the file.

Dotted notation: Key row (0) containing firstName, lastName, address.street, address.city, address.state, address.zip would product, per row, a doc with first and last names and an embedded doc named address, with the address.

assign function by VisioN from How to set object property (of object property of..) given its string name in JavaScript?

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

Answer 3

Find quick and exact solution which work for me:

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

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Excel to Json in nodejs | jsonworld</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
 
<div class="jumbotron text-center">
    <h1>Excel to Json in nodejs</h1>
    <p>source : <a href="https://jsonworld.com">jsonworld</a></p> 
</div>
  
<div class="container">
    <div class="row">
        <div class="col-sm-4 col-md-offset-4">
            <form id="form" enctype ="multipart/form-data" action="sendFile" method="post">
            <div class="form-group">
            <input type="file" name="file" class="form-control"/>
            <input type="submit" value="Upload" name="submit" class="btn btn-primary" style="float:right; margin-top:30px;">
            </form>    
        </div>
    </div>
</div>
 
</body>
</html>

Find more information at: jsonworld