前端在网页上通过xlsx.js导入excel后并解析展示

前端在网页上通过xlsx.js导入excel后并解析展示：

首先下载xlsx.js,然后引入该文件夹下的xlsx.js;
npm install xlsx
html部分：

<style>
    #import{
      cursor: pointer; color: red;
    }
    #ul tr{
      color: gray;
    }
    #ul tr:hover{
      color: red;
    }
    #ul td,#ul th{
      border-bottom: 1px solid darkcyan; width: 100px;
    }
</style>

<div>
    <input style="display: none;" id="import" type="file" accept=".xls,.xlsx" name="12"/>
    <label for="import" style="cursor: pointer;" class="i18n" i18nName="Import"></label>
    <a href="./file/importTemplate.xlsx" style="color: #000;" class="i18n" i18nName="Export"></a>
</div>
<table id="ul" cellspacing="0" cellpadding="0" border-collapse="collapse"></table>

首先通过input[file]，上传文件然后或许到文件流数据：

$("#import").change(function(e){
  $("#ul").empty();
  var file = e.target.files[0];
  parseExcel(file).then(function(result){
    console.log(result)
    // 经过xlsx处理过的数据，按需处理渲染到table里。
    if(result.length>2){
      $("#ul").append(`<tr value1=${result[0].A.replace("'",'')}>${th(result[0])}</tr>`);
      for(var i=1; i<result.length;i++){
        $("#ul").append(`<tr value1=${result[i].A.replace("'",'')}>${td(result[i])}</tr>`);
      };
    }else{
      for(var i=0; i<result.length;i++){
        $("#ul").append(`<tr value1=${result[i].A.replace("'",'')}>${td(result[i])}</tr>`);
      };
    }
    $("#import").val(null);
  });
});

function th(arr){
  var str = ''
  for(var i in arr){
    str+=`<th>${arr[i].replace("'",'')}</th>`
  };
  return str;
}
function td(arr){
  var str = ''
  for(var i in arr){
    str+=`<td>${arr[i].replace("'",'')}</td>`
  };
  return str;
}

parseExcel调用xlsx.js方法：

function parseExcel(file) {
  return new Promise(function (resolve, reject) {
      var reader = new FileReader();
      var result=[];
      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.get_formulae(workbook.Sheets[sheetName]);
              console.log(XL_row_object)
              var currentRowNumber = 1;
              var currentRow = {};
              for (var i = 0; i < XL_row_object.length; i++) {
                  var temp = XL_row_object[i];//A1='a
                  var column = (temp.split("=")[0] + '') || '';//A1
                  var value = (temp.split("=")[1] + '') || '';//a
                  var rowNumber = parseInt(column.replace(/[^0-9]+/g,''));//1
                  var columnName = column.replace(/[^a-zA-Z]+/g,'');//A
                  // console.log(temp)
                  // console.log(currentRowNumber,columnName,rowNumber,column,value)
                  if (currentRowNumber == rowNumber) {
                      currentRow[columnName] = value;
                  } else {
                      result.push(currentRow);
                      currentRowNumber = rowNumber;
                      currentRow = {};
                      currentRow[columnName] = value;
                  }
              }
              if (XL_row_object.length > 0)
                  result.push(currentRow);
          });
          resolve(result);
      };

      reader.onerror = function (ex) {
          reject(new Error(ex));
      };

      reader.readAsBinaryString(file);
  });
};

验证：

$("#ul").on("click","tbody>tr",function(e){
  console.log(e)
});