javascript - read - Parse XLSX con Node y crea json

xlsx read nodejs

Question

Bien, entonces encontré este node_module realmente bien documentado llamado js-xlsx

Pregunta: ¿Cómo puedo analizar un xlsx para generar json ?

Aquí está cómo se ve la hoja de Excel:

Al final, el json debería verse así:

[
   {
   "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

Creo que este código hará lo que quieras. Almacena la primera fila como un conjunto de encabezados, luego almacena el resto en un objeto de datos que puede escribir en el disco como 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);
});

imprime

[ { 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 2

También puedes usar

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 3

Versión mejorada de la respuesta de "Josh Marinacci", leerá más allá de la columna Z (es decir, 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);
});