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

User · Answer

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

User · Answer

podria ser algo asi en react y electron     xslToJson   workbook   gt              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      gt               Boilerplate to set up FileReader            const reader   new FileReader            const rABS     reader readAsBinaryString           reader onload   e   gt                   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   gt                    console log  data renaes    data                                this setState   DataEESSsend  dataNueva                console log  dataNueva    dataNueva                        if  rABS  reader readAsBinaryString file           else reader readAsArrayBuffer file               handleChange   e   gt            const files   e target files          if  files  amp  amp  files 0                 this handleFile files 0

User · Answer

here s angular 5 method version of this with unminified syntax for those who struggling with that y  z  tt in accepted answer  usage  parseXlsx   subscribe  data   gt          parseXlsx         let self   this      return Observable create observer   gt            this http get    assets input xlsx     responseType   arraybuffer     subscribe  data  ArrayBuffer    gt                const XLSX   require  xlsx                let file   new Uint8Array data               let workbook   XLSX read file    type   array                  let sheetNamesList   workbook SheetNames               let allLists                   sheetNamesList forEach function  sheetName                    let worksheet   workbook Sheets sheetName                   let currentWorksheetHeaders  object                       let data  Array lt any gt                        for  let cellName in worksheet     cellNames example   ref  margins A1 B1 C1                        skipping serviceCells  margins  ref                     if  cellName 0                                     continue                                               parse colName  rowNumber  and getting cellValue                     let numberPosition   self getCellNumberPosition cellName                       let colName   cellName substring 0  numberPosition                       let rowNumber   parseInt cellName substring numberPosition                        let cellValue   worksheet cellName  w     w is XLSX property of parsed worksheet                        treating  -  cells as empty on Spot Indices worksheet                     if  cellValue trim       -                             continue                                               storing header column names                     if  rowNumber    1  amp  amp  cellValue                            currentWorksheetHeaders colName    typeof  cellValue      string    cellValue toCamelCase     cellValue                          continue                                               creating empty object placeholder to store current row                     if   data rowNumber                             data rowNumber                                                     if header is date - for spot indices headers are dates                     data rowNumber  currentWorksheetHeaders colName     cellValue                                        dropping first two empty rows                 data shift                    data shift                    allLists sheetName toCamelCase      data                               this parsed   allLists               observer next allLists               observer complete

User · Answer

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

User · Answer

I found a better way of doing this    function genrateJSONEngine         var XLSX   require  xlsx        var workbook   XLSX readFile  test xlsx        var sheet name list   workbook SheetNames      sheet name list forEach function  y          var array   workbook Sheets y          var first   array 0  join         var headers   first split              var jsonData             for  var i   1  length   array length  i  lt  length  i               var myRow   array i  join            var row   myRow split                var data               for  var x   0  x  lt  row length  x                data headers x     row x                     jsonData push data

User · Answer

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  lt  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  amp  amp  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

[javascript] Parse XLSX with Node and create json

Examples related to javascript

Examples related to json

Examples related to node.js

Examples related to excel

Examples related to xlsx