Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Parse XLSX avec Node and create json

ayi

Ok so I found this really well documented <code>node_module</code> called <a href="https://github.com/SheetJS/js-xlsx" rel="noopener nofollow ugc">js-xlsx</a>
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:
<pre><code class="lang-auto">[
{
"id": 1,
"Headline": "Team: Sally Pearson",
"Location": "Austrailia",
"BodyText": "...",
"Media: "..."
},
{
"id": 2,
"Headline": "Team: Rebeca Andrade",
"Location": "Brazil",
"BodyText": "...",
"Media: "..."
}
]

</code></pre>
index.js:
<pre><code class="lang-auto">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');

</code></pre>

ayi_2

Improved Version of “Josh Marinacci” answer , it will read beyond Z column (i.e. AA1).
<pre><code class="lang-auto">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)) {
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);

});

</code></pre>