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?

can I use readFile? I am using read(), what's the difference? — Mizlul, Dec 28 '17 at 14:08
if I change the read() to readFile() it throws an error: Cannot read property 'readFileSync' of undefined — Mizlul, Dec 28 '17 at 14:11
are you doing this in a browser? if so you will have to get a data blob from a drag/drop or file form field. — Daniel A. White, Dec 28 '17 at 14:16
yes @DanielA.White, I am opening the browser and then the javascript code is being executed, is it possible somehow that I can parse the file without having to have any input file element, I mean I would directly want to parse it , is this possible? — Mizlul, Dec 28 '17 at 14:28
browsers sandbox you. you will have to get it somehow. there's no direct file system access. — Daniel A. White, Dec 28 '17 at 14:29
@DanielA.White what would you suggest me to do in this case? — Mizlul, Dec 28 '17 at 14:36

MANOJ KUMAR · Answer 1 · 2018-08-05T08:12:15.983

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);
  });

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

1 Answers1