Parse XLSX with Node and create json

Question

Ok so I found this really well documented node_module called js-xlsx

Question: How can I parse an xlsx to output json?

Here is what the excel sheet looks like:

In the end the json should look like this:

[
   {
   "id": 1,
   "Headline": "Team: Sally Pearson",
   "Location": "Austrailia",
   "BodyText": "...",
   "Media: "..."
   },
   {
   "id": 2,
   "Headline": "Team: Rebeca Andrade",
   "Location": "Brazil",
   "BodyText": "...",
   "Media: "..."
   }
]

index.js:

if(typeof require !== 'undefined') {
    console.log('hey');
    XLSX = require('xlsx');
}
var workbook = XLSX.readFile('./assets/visa.xlsx');
var sheet_name_list = workbook.SheetNames;
sheet_name_list.forEach(function(y) { /* iterate through sheets */
  var worksheet = workbook.Sheets[y];
  for (z in worksheet) {
    /* all keys that do not begin with "!" correspond to cell addresses */
    if(z[0] === '!') continue;
    // console.log(y + "!" + z + "=" + JSON.stringify(worksheet[z].v));

  }

});
XLSX.writeFile(workbook, 'out.xlsx');

Answer 1

You can also use

var XLSX = require('xlsx');
var workbook = XLSX.readFile('Master.xlsx');
var sheet_name_list = workbook.SheetNames;
console.log(XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]))

Answer 2

Improved Version of "Josh Marinacci" answer , it will read beyond Z column (i.e. AA1).

var XLSX = require('xlsx');
var workbook = XLSX.readFile('test.xlsx');
var sheet_name_list = workbook.SheetNames;
sheet_name_list.forEach(function(y) {
    var worksheet = workbook.Sheets[y];
    var headers = {};
    var data = [];
    for(z in worksheet) {
        if(z[0] === '!') continue;
        //parse out the column, row, and value
        var tt = 0;
        for (var i = 0; i < z.length; i++) {
            if (!isNaN(z[i])) {
                tt = i;
                break;
            }
        };
        var col = z.substring(0,tt);
        var row = parseInt(z.substring(tt));
        var value = worksheet[z].v;

        //store header names
        if(row == 1 && value) {
            headers[col] = value;
            continue;
        }

        if(!data[row]) data[row]={};
        data[row][headers[col]] = value;
    }
    //drop those first two rows which are empty
    data.shift();
    data.shift();
    console.log(data);
});

Answer 3

I think this code will do what you want. It stores the first row as a set of headers, then stores the rest in a data object which you can write to disk as JSON.

var XLSX = require('xlsx');
var workbook = XLSX.readFile('test.xlsx');
var sheet_name_list = workbook.SheetNames;
sheet_name_list.forEach(function(y) {
    var worksheet = workbook.Sheets[y];
    var headers = {};
    var data = [];
    for(z in worksheet) {
        if(z[0] === '!') continue;
        //parse out the column, row, and value
        var col = z.substring(0,1);
        var row = parseInt(z.substring(1));
        var value = worksheet[z].v;

        //store header names
        if(row == 1) {
            headers[col] = value;
            continue;
        }

        if(!data[row]) data[row]={};
        data[row][headers[col]] = value;
    }
    //drop those first two rows which are empty
    data.shift();
    data.shift();
    console.log(data);
});

prints out

[ { id: 1,
    headline: 'team: sally pearson',
    location: 'Australia',
    'body text': 'majority have…',
    media: 'http://www.youtube.com/foo' },
  { id: 2,
    headline: 'Team: rebecca',
    location: 'Brazil',
    'body text': 'it is a long established…',
    media: 'http://s2.image.foo/' } ]

Answer 4

**podria ser algo asi en react y electron**

 xslToJson = workbook => {
        //var data = [];
        var sheet_name_list = workbook.SheetNames[0];
        return XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list], {
            raw: false,
            dateNF: "DD-MMM-YYYY",
            header:1,
            defval: ""
        });
    };

    handleFile = (file /*:File*/) => {
        /* Boilerplate to set up FileReader */
        const reader = new FileReader();
        const rABS = !!reader.readAsBinaryString;

        reader.onload = e => {
            /* Parse data */
            const bstr = e.target.result;
            const wb = XLSX.read(bstr, { type: rABS ? "binary" : "array" });
            /* Get first worksheet */
            let arr = this.xslToJson(wb);

            console.log("arr ", arr)
            var dataNueva = []

            arr.forEach(data => {
                console.log("data renaes ", data)
            })
            // this.setState({ DataEESSsend: dataNueva })
            console.log("dataNueva ", dataNueva)

        };


        if (rABS) reader.readAsBinaryString(file);
        else reader.readAsArrayBuffer(file);
    };

    handleChange = e => {
        const files = e.target.files;
        if (files && files[0]) {
            this.handleFile(files[0]);
        }
    };