Import Excel to Html Table Using Jquery and Ajax

Question

I am trying to import excel file to HTML Table using Jquery. I have tried the code below. Do I have to do any other thing? The file uploaded is not showing in the table yet. Please assist.

Below is the Script

var ExcelToJSON = function () {

            this.parseExcel = function (file) {
                var reader = new FileReader();

                reader.onload = function (e) {
                    var data = e.target.result;
                    var workbook = XLSX.read(data, {
                        type: 'binary'
                    });
                    workbook.SheetNames.forEach(function (sheetName) {
                        // Here is your object
                        var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                        var json_object = JSON.stringify(XL_row_object);
                        productList = JSON.parse(json_object);

                        var rows = $('#tblItems tbody tr',);
                        console.log(productList)
                        for (i = 0; i < productList.length; i++) {

                            var columns = Object.values(productList[i])
                            rows.eq(i).find('td').text(columns[0]);
                            rows.eq(i).find('td').text(columns[1]);
                            rows.eq(i).find('td').text(columns[2]);
                            rows.eq(i).find('td').text(columns[3]);
                            rows.eq(i).find('td').text(columns[4]);
                           
                        }

                    })
                };
                reader.onerror = function (ex) {
                    console.log(ex);
                };

                reader.readAsBinaryString(file);



            };
        };

        function handleFileSelect(evt) {

            var files = evt.target.files; // FileList object
            var xl2json = new ExcelToJSON();
            xl2json.parseExcel(files[0]);
        }

        document.getElementById('fileupload').addEventListener('change', handleFileSelect, false);

Below is the HTML

<div class="w3-col w3-half">
                    <div class="form-group">
                       

                        <input id="fileupload" type=file name="files[]">
                    </div>
                </div>

<div class="w3-responsive">
                    @*<h2 class="w3-center"><strong>All Items in Stock </strong></h2>*@
                    <table id="tblItems" class="table w3-table-all w3-hoverable">
                        <thead>
                            <tr>

                                <th>Item ID</th>
                                <th>Item Name</th>
                                <th>Cost Price</th>
                                <th>Sales Price</th>
                                <th>Item Discontinued?</th>

                            </tr>
                        </thead>
                        <tbody></tbody>
                    </table>
                </div>

After trying to upload an excel file, it does nothing and the excel file been imported does not display on the Table designated for it.

ruleboy21 · Accepted Answer · 2022-02-01 16:28:57Z

Kindly try this code

var ExcelToJSON = function() {
  this.parseExcel = function(file) {
    var reader = new FileReader();

    reader.onload = function(e) {
      var data = e.target.result;
      var workbook = XLSX.read(data, {
        type: 'binary'
      });
      workbook.SheetNames.forEach(function(sheetName) {
        var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
        var productList = JSON.parse(JSON.stringify(XL_row_object));

        var rows = $('#tblItems tbody');
        // console.log(productList)
        for (i = 0; i < productList.length; i++) {
          var columns = Object.values(productList[i])
          rows.append(`
                        <tr>
                            <td>${columns[0]}</td>
                            <td>${columns[1]}</td>
                            <td>${columns[2]}</td>
                            <td>${columns[3]}</td>
                            <td>${columns[4]}</td>
                        </tr>
                    `);
        }

      })
    };
    reader.onerror = function(ex) {
      console.log(ex);
    };

    reader.readAsBinaryString(file);
  };
};

function handleFileSelect(evt) {
  var files = evt.target.files; // FileList object
  var xl2json = new ExcelToJSON();
  xl2json.parseExcel(files[0]);
}

document.getElementById('fileupload').addEventListener('change', handleFileSelect, false);

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.0/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/xlsx.full.min.js"></script>
<div class="w3-col w3-half">
  <div class="form-group">
    <input id="fileupload" type=file name="files[]">
  </div>
</div>
<div class="w3-responsive">
  <h2 class="w3-center"><strong>All Items in Stock </strong></h2>
  <table id="tblItems" class="table w3-table-all w3-hoverable">
    <thead>
      <tr>
        <th>Item ID</th>
        <th>Item Name</th>
        <th>Cost Price</th>
        <th>Sales Price</th>
        <th>Item Discontinued?</th>
      </tr>
    </thead>
    <tbody></tbody>
  </table>
</div>

Collectives™ on Stack Overflow

Import Excel to Html Table Using Jquery and Ajax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related