How to parse, and iterate through, all rows from an Excel sheet file using JavaScript

Question

I am trying to parse and read every cell from an Excel sheet; it seems that I am parsing the file but I just can't go through each cell and display them.

I am using the following code:

    var workbook = XLSX.read('datasets/persons.xlsx', { type: 'binary' });
    var sheet_name_list = workbook.SheetNames;


    // console.log(sheet_name_list);
    sheet_name_list.forEach(function (y) { /* iterate through sheets */
      //Convert the cell value to Json
      console.log(y);
      var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
      console.log(roa);
      if (roa.length > 0) {
        result = roa;
      }
  });

I am getting an empty array when I try to print console.log(roa), any idea how I should iterate through each cell from the file?

Answer 1

you can use

XLSX.utils.sheet_to_row_object_array(workbook.Sheets[y])

to parse each cells in excel file.

Here is full code use to display Excel sheet file using JavaScript and JQuery.

    function handleFile(e) {
        //alert(e);
        var exceljsonObj = [];
        var files = e.target.files;
        var i, f;
        for (i = 0, f = files[i]; i != files.length; ++i) {
            var reader = new FileReader();
            var name = f.name;
            reader.onload = function (e) {
                var data = e.target.result;
                var result;
                var workbook = XLSX.read(data, { type: 'binary' });
                var sheet_name_list = workbook.SheetNames;
                sheet_name_list.forEach(function (y) { /* iterate through sheets */
                    //var exceljsonObj = [];
                    var rowObject  =  XLSX.utils.sheet_to_row_object_array(workbook.Sheets[y]);
                    exceljsonObj = rowObject;
                        for(var i=0;i<exceljsonObj.length;i++){
                        //var recordcount = exceljsonObj.length;
                        var data = exceljsonObj[i];
                        $('#myTable tbody:last-child').append("<tr><td>"+data.ID+"</td><td>"+data.Name+"</td><td>"+data.Months+"</td></tr>");
                        }
                    //alert(exceljsonObj.length);
                        $('#alermessage').each(function() {
                           //this points to item
                           alert('Record Count is '+exceljsonObj.length);
                        });
                });
            };
            reader.readAsArrayBuffer(f);
        }
    }
  $(document).ready(function(){
    $('#files').change(handleFile);
  });