前端方式导入导出xlsx

直接上代码： 所需的插件(xlsx) 提取码：gfg4

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>xlsx导入导出</title>
</head>

<body>
  <input type="file" onchange="importFile(event)">
  <br>
  <button onclick="downXLSX()">下载</button>
  <script src="./xlsx.core.min.js"></script>
  <script>
// 如果有先使用导入功能将数据缓存下来，作为下载数据
    var importJson = [];
// 导入
    function importFile(e) {
      let render = new FileReader();
      render.onload = function () {
        var workbook = XLSX.read(this.result, { type: "binary" });
        filterSheet(workbook);
      }
      render.readAsBinaryString(e.target.files[0])
    }
    function filterSheet(workbook) {
      var sheetNames = workbook.SheetNames; // 工作表名称集合
      var worksheet = workbook.Sheets[sheetNames[0]];
      let arr = [];
      let obj = {};
      for (var key in worksheet) {
        // v是读取单元格的原始值
        // console.log(key, key[0] === "!" ? worksheet[key] : worksheet[key].v);
        if (key[0] !== "!") {
          if (key[0] == "A") {
            obj["mobile"] = worksheet[key].v;
          } else if (key[0] == "B") {
            obj["province"] = worksheet[key].v;
          } else if (key[0] == "C") {
            obj["city"] = worksheet[key].v;
            arr.push(obj);
            obj = {};
          }
        }
      }
      console.log(arr)
      arr.shift(); // 去除标题
      importJson = arr;
      console.log(importJson) // 现在这个数据可以上传都后端
    }
// 导出
    function json_to_array(key, jsonData) {
      return jsonData.map(v => key.map(j => { return v[j] }));
    }

    function auto_width(ws, data) {
      /*set worksheet max width per col*/
      const colWidth = data.map(row => row.map(val => {
        /*if null/undefined*/
        if (val == null) {
          return { 'wch': 10 };
        }
        /*if chinese*/
        else if (val.toString().charCodeAt(0) > 255) {
          return { 'wch': val.toString().length * 2 };
        } else {
          return { 'wch': val.toString().length };
        }
      }))
      /*start in the first row*/
      let result = colWidth[0];
      for (let i = 1; i < colWidth.length; i++) {
        for (let j = 0; j < colWidth[i].length; j++) {
          if (result[j]['wch'] < colWidth[i][j]['wch']) {
            result[j]['wch'] = colWidth[i][j]['wch'];
          }
        }
      }
      ws['!cols'] = result;
    }

    function export_array_to_excel({ key, data, title, filename, autoWidth }) {
      const wb = XLSX.utils.book_new();
      const arr = json_to_array(key, data);
      arr.unshift(title);
      const ws = XLSX.utils.aoa_to_sheet(arr);
      if (autoWidth) {
        auto_width(ws, arr);
      }
      XLSX.utils.book_append_sheet(wb, ws, filename);
      XLSX.writeFile(wb, filename + '.xlsx');
    }
    function downXLSX() {
      let tempTitle = ['手机', '省份', '城市'];
      let tempKey = ['mobile', 'province', 'city'];
      let tempData = importJson.length > 0 ? importJson :
        [
          {
            mobile: 13512345678,
            province: '广东省',
            city: '广州市'
          },
          {
            mobile: 13612345678,
            province: '广西省',
            city: '南宁市'
          },
          {
            mobile: 13712345678,
            province: '福建省',
            city: '福州市'
          },
        ];
      const params = {
        title: tempTitle,
        key: tempKey,
        data: tempData,
        autoWidth: true,
        filename: "测试列表"
      };
      export_array_to_excel(params);
    }

  </script>
</body>

</html>

 

代码里面有简单的注释

 

posted @ 2020-03-09 16:22  Tsingtree  阅读(705)  评论(0)  编辑  收藏  举报