Excel导出-vue

1.自定义导出组件

点击查看代码

//
//TODO: 此组件功能为生成excel并下载。
//
//
//
//
//
<template>
  <div></div>
</template>
<script>
import XLSX from "xlsx-js-style";
// import XLSX from "xlsx";
// 还需下载模块包 xlsx
////ps  需要修改源码：在\node_modules\xlsx-style\dist\cpexcel.js 807行 的
//var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable 不然会报错;
//https://github.com/markatil/xlsx-style
export default {
  name: "xlsxdown",
  methods: {
    /** json:[{时间: "2020-01-22",新注册人数: "22", 在线人数: "22",累计用户数: "13"}]
     * @param {Array} json
     * @param {string} dataTitle
     * @param {string} fileName
     * @param {Object} paramStyle 样式参数
     */
    downloadExl(json, dataTitle, fileName = "导出文件",paramStyle) {
    if(json.length ==0) return;
      const type = {
        bookType: "xlsx",
        bookSST: true,
        type: "binary",
        cellStyles: true,
      };
      let col = []; // 列的样式集合
      let tmpdata = json[0];
      var keyMap = []; //获取keys

      json.unshift({});
      for (var k in tmpdata) {
        keyMap.push(k);
        col.push({ wpx: paramStyle.w || 150});
        json[0][k] = k;
      }
      // 是否有title，无titl从第一列开始部署数据
      let titleIndex = dataTitle ? 2 : 1;
      // 设置单元格样式
      let borderAll = {
        //单元格外侧框线
        top: {
          style: paramStyle.bdType||"thin",
          color: {
            rgb: paramStyle.bdColor||"00000000",
          },
        },
        bottom: {
          style: paramStyle.bdType||"thin",
          color: {
            rgb: paramStyle.bdColor||"00000000",
          },
        },
        left: {
          style: paramStyle.bdType||"thin",
          color: {
            rgb: paramStyle.bdColor||"00000000",
          },
        },
        right: {
          style: paramStyle.bdType||"thin",
          color: {
            rgb: paramStyle.bdColor||"00000000",
          },
        },
      };
      let styleCell = {
        border: borderAll,
        alignment: { vertical: "center", horizontal: "center" },
        // font: { sz: 14, bold: false},
        // fill: { bgColor: { rgb: "ffffffff" }, fgColor: { rgb: "E8E8E8" } },
      };
      json
        .map((v, i) => {
            let data = keyMap.map((k, j) => {
            return Object.assign(
              {},
              {
                v: v[k],
                position:
                  (j > 25 ? this.getCharCol(j) : String.fromCharCode(65 + j)) +
                  (i + titleIndex),
              }
            );
          });
          return data;
        })
        .reduce((prev, next) => prev.concat(next))
        .forEach((v, i) => {
          tmpdata[v.position] = { v: v.v, s: styleCell };
        });
      //是否加载标题
      var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
      outputPos = ["A1"].concat(outputPos); //输出范围
      if (dataTitle) {
        tmpdata["A1"] = { v: dataTitle, s: {
            border: borderAll,
            alignment: { vertical: "center", horizontal: "center" },
            font: { sz: 14, bold: true},
            fill: {fgColor: { rgb: "E8E8E8" } },
        } };
        tmpdata["!merges"] = [
          {
            s: { c: 0, r: 0 },
            e: { c: col.length-1, r: 0 },
          },
        ];
      }
      tmpdata["!cols"] = col;

      var tmpWB = {
        SheetNames: ["mySheet"], //保存的表标题
        Sheets: {
          // my:tmpdata,
          mySheet: Object.assign(
            {},
            tmpdata, //内容
            {
              "!ref": outputPos[0] + ":" + outputPos[outputPos.length - 1], //设置填充区域
            }
          ),
        },
      };
      var tmpDown = new Blob(
        [
          this.s2ab(
            XLSX.write(
              tmpWB,
              {
                bookType: type == undefined ? "xlsx" : type.bookType,
                bookSST: false,
                type: "binary",
              } //这里的数据是用来定义导出的格式类型
            )
          ),
        ],
        {
          type: "",
        }
      );
      this.saveAs(
        tmpDown,
        fileName + "." + (type.bookType == "biff2" ? "xls" : type.bookType)
      );
    },
    // 下载功能
    saveAs(obj, fileName) {
      var tmpa = document.createElement("a");
      tmpa.download = fileName || "未命名";
      // 兼容ie
      if ("msSaveOrOpenBlob" in navigator) {
        window.navigator.msSaveOrOpenBlob(obj, "导出文件" + ".xlsx");
      } else {
        tmpa.href = URL.createObjectURL(obj);
      }
      tmpa.click();
      setTimeout(function () {
        URL.revokeObjectURL(obj);
      }, 100);
    },
    // 获取26个英文字母用来表示excel的列
    getCharCol(n) {
      let temCol = "",
        s = "",
        m = 0;
      while (n > 0) {
        m = (n % 26) + 1;
        s = String.fromCharCode(m + 64) + s;
        n = (n - m) / 26;
      }
      return s;
    },
    s2ab(s) {
      if (typeof ArrayBuffer !== "undefined") {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var j = 0; j != s.length; ++j) {
          view[j] = s.charCodeAt(j) & 0xff;
        }
        return buf;
      } else {
        buf = new Array(s.length);
        for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff;
        return buf;
      }
    },
  },
};
</script>

2.安装插件依赖（xlsx、xlsx-js-style）

（1）例：npm i xlsx@0.18.5 --save
（2）例：npm i xlsx-js-style@1.2.0 -- save

3.utils封装函数

点击查看代码

export function formatDate(date, str) {
  date = new Date(date)
  str = str ? str : 'yyyy/MM/dd hh:mm:ss'
  if (/(y+)/.test(str)) {
    str = str.replace(RegExp.$1, String(date.getFullYear()).substr(4 - RegExp.$1.length))
  }
  let o = {
    'M+': date.getMonth() + 1,
    'd+': date.getDate(),
    'h+': date.getHours(),
    'm+': date.getMinutes(),
    's+': date.getSeconds()
  }
  for (let k in o) {
    if (new RegExp(`(${k})`).test(str)) {
      let stry = String(o[k])
      str = str.replace(RegExp.$1, RegExp.$1.length === 1 ? stry : ('00' + stry).substr(stry.length))
    }
  }
  return str
}

export function excelDataFormat(json, forma) {
  //处理一下导出数据
  let data = []
  for (let i = 0; i < json.length; i++) {
    const jd = json[i]
    let obj = {}
    for (const key in forma) {
      if (forma[key] == 'Results') {
        obj[key] = jd[forma[key]] ? '达标' : '未达标'
      } else if (forma[key] == 'AnswerType') {
        obj[key] =
          jd[forma[key]] == '1'
            ? '纯文本'
            : jd[forma[key]] == '2'
            ? 'URL链接'
            : jd[forma[key]] == '3'
            ? '图片信息'
            : jd[forma[key]] == '4'
            ? '视频音频信息'
            : '链接与描述'
      } else if (forma[key] == 'I') {
        obj[key] = jd[forma[key]] == '-1' ? '--' : jd[forma[key]]
      } else {
        obj[key] = jd[forma[key]] ? jd[forma[key]] : ''
      }
    }
    data.push(obj)
  }
  return data
}

4.vue文件使用

点击查看代码

<template>
	<div>
		<Button @click="exportHandleClick">导出</Button>
	    <DownExcel ref="downExcel"></DownExcel>
	</div>
</template>
<script>
import { excelDataFormat, formatDate } from '../../utils/tool'
import DownExcel from '../../components/downExcel/downExcel'
export default {
  components: {
    DownExcel
  },
  data(){
  	return {
  		      tableData: [
        {
          id: 1,
          EscapeName: '122107工作面火灾避灾路线',
          DisasterType_Text: '火灾',
          Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
          Status: '0'
        },
        {
          id: 2,
          EscapeName: '122107工作面火灾避灾路线',
          DisasterType_Text: '火灾',
          Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
          Status: '0'
        },
        {
          id: 3,
          EscapeName: '122107工作面火灾避灾路线',
          DisasterType_Text: '火灾',
          Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
          Status: '1'
        },
        {
          id: 4,
          EscapeName: '122107工作面火灾避灾路线',
          DisasterType_Text: '火灾',
          Location: '[{"x":109.84383812459045,"y":38.623645834572514},{"x":109.85748794186557,"y":38.61695210008022}]',
          Status: '1'
        }
      ],
  	}
  },
  methos: {
      exportHandleClick() {
      console.log('导出')
      let foramText = {
        路线名称: 'EscapeName',
        灾害类型: 'DisasterType_Text',
        路线坐标: 'Location',
        状态: 'Status'
      }
      let list = excelDataFormat(this.tableData, foramText)
      this.$refs.downExcel.downloadExl(list, '', formatDate(new Date(), 'yyyy-MM-dd hh:mm:ss') + '避灾路线数据', {
        w: 300
      })
    },
  }
}
</script>