load excel and jqgrid demo

perfect

by ysuper

HTML

<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/themes/redmond/jquery-ui.min.css">
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/free-jqgrid/4.15.5/css/ui.jqgrid.min.css">
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/free-jqgrid/4.15.5/jquery.jqgrid.min.js"></script>
<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/xlsx/0.14.3/xlsx.full.min.js"></script>

<input type="file" id="file">

<table id="list2"></table>
<div id="pager2"></div>

<input type="button" id="button" style="display: none;" value="下一步">

JavaScript

function sum_total() {
  var total_qty = 0
  var total_prize = 0
  var gridData = $("#list2").getRowData();
  gridData.forEach(function(rowData) {
    total_qty = total_qty + parseFloat(rowData.Qty)
    total_prize = total_prize + parseFloat(rowData.採購金額)
  });
  /* console.log(total_qty)
  console.log(total_prize) */
}

$("#button").click(function() {
  var gridData = $("#list2").getRowData();
  var postData = JSON.stringify(gridData);
  if (postData.indexOf('\\') != -1) {
    alert('「採購價格」尚未輸入完成！')
    event.preventDefault();
    return
  }
  /* console.log(gridData) */
  console.log(postData)
})

$('#file').change(function(e) {
  $("#list2").jqGrid('GridUnload');
  var files = e.target.files;
  var records_array = []; // 存储获取到的数据

  var fileReader = new FileReader();
  // 以二进制方式打开文件
  fileReader.readAsBinaryString(files[0]);
  fileReader.onload = function(ev) {
    try {
      var data = ev.target.result,
        workbook = XLSX.read(data, {
          type: 'binary'
        }) // 以二进制流方式读取得到整份excel表格对象
    } catch (e) {
      console.log('文件类型不正确');
      return;
    }

    // 表格的表格范围，可用于判断表头数量是否正确
    var fromTo = '';
    // 遍历每张表读取
    for (var sheet in workbook.Sheets) {
      if (workbook.Sheets.hasOwnProperty(sheet)) {
        fromTo = workbook.Sheets[sheet]['!ref'];
        // console.log(fromTo); //打印表格范围
        records_array = records_array.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
        /*使用XLSX.utils.sheet_to_json方法解析表格对象返回相应的JSON数据 */

        break; // 如果只取第一张表，就取消注释这行
      }
    }

    //console.log(records_array); //打印获取的数据
    if (records_array.length > 1000) {
      alert("資料筆數不得大於1000！")
      return
    } else {
      $("#button").show();
    }

    $("#list2").jqGrid({
      /*       colNames: ['SN','品號','品名','商品描述','ABC等級','最近進價-本幣單價','售價定價一','售價定價三','匯入單價','庫別','組成用量','預計可用','Qty','最小包裝數','原始料號','產地','備註','組合件售價'], */
      colNames: ['SN', '品號', '品名', '銷售單價', '採購單價', 'Qty', '庫別', '銷售金額', '採購金額', '備註'],
      colModel:...