前端导出excel表格

1. vue-json-excel

1. 安装依赖

npm install vue-json-excel

2. 在main.js文件全局引入

import JsonExcel from "vue-json-excel";
Vue.component("downloadExcel", JsonExcel);

3. 页面中使用

<template>
  <div class="hello">
    <h2>vue-json-excel插件文件下载</h2>
    <download-excel class="export-excel-wrapper" title="标题名称" :data="excelpage" :fields="json_fields" name="人员信息.xls">
      <button>download-excel下载</button>
    </download-excel>
  </div>
</template>

<script>
export default {
  name: "HelloWorld",
  data() {
    return {
      excelpage: [
        { id: 1, name: "王五" },
        { id: 2, name: "张三" },
        { id: 3, name: "李四" },
        { id: 4, name: "赵六" },
      ], // 存放用于导出excel的数据
      json_fields: {
        id: "id", //常规字段
        姓名: "name", //支持嵌套属性
      },
      json_meta: [
        [
          {
            " key ": " charset ",
            " value ": " utf- 8 ",
          },
        ],
      ],
    };
  },
  mounted() {
  },
  methods: {  },
};
</script>

<!-- Add "scoped" attribute to limit CSS to this component only -->
<style scoped>
</style>

2. xlsx xlsx-style

1. 安装依赖

npm install xlsx-style --save
npm install xlsx--save

注意： 修改xlsx-style 源码 解决报错

在\node_modules\xlsx-style\dist\cpexcel.js  
var cpt = require('./cpt' + 'able');  改为   var cpt = cptable; 

2. 在页面中使用

<template>
  <div class="hello">
    <h2>xlsx xlsx-style插件文件下载</h2>
    <button @click="downExcelLoad">xlsx下载</button>

    <ThreeOne />
  </div>
</template>

<script>
import { exportExcel } from "../api/excelDownload/excelUtils.js";
export default {
  name: "HelloWorld",
  data() {
    return {
      headers: [
        { title: "学员名字", dataIndex: "name", width: 140 },
        { title: "联系⽅式", dataIndex: "phone", width: 140 },
        { title: "状态", dataIndex: "status", width: 140 },
        { title: "扣除课时", dataIndex: "deduct", width: 100 },
        { title: "已完成/总课时", dataIndex: "number", width: 100 },
      ],
      datasource: [
        {
          name: "张三",
          phone: "12345678909",
          status: "已签到",
          deduct: 1,
          number: "1/10",
        },
        {
          name: "李四",
          phone: "12345678909",
          status: "旷课",
          deduct: 1,
          number: "1/10",
        },
        {
          name: "王⼩⼆",
          phone: "12345678909",
          status: "请假",
          deduct: "-",
          number: "0/10",
        },
        {
          name: "赵钱",
          phone: "12345678909",
          status: "已签到",
          deduct: 1,
          number: "1/10",
        },
        {
          name: "孙李",
          phone: "12345678909",
          status: "已签到",
          deduct: 1,
          number: "1/10",
        },
        {
          name: "马上飘",
          phone: "12345678909",
          status: "已签到",
          deduct: 1,
          number: "1/10",
        },
      ],
      options: [
        { title: "学员信息" },
        { title: "单位：万、千、元" },
      ],
      type: {
        bookType: "xlsx",
        bookSST: true,
        type: "binary",
        cellStyles: true,
      },
    };
  },
  mounted() {
  },
  methods: {
    downExcelLoad() {
      exportExcel(
        this.headers,
        this.datasource,
        this.options,
        this.type,
        "学员信息"
      );
    },
  },
};
</script>

<!-- Add "scoped" attribute to limit CSS to this component only -->
<style scoped>
#container {

  height: 400px;

}

h3 {
  margin: 40px 0 0;
}

ul {
  list-style-type: none;
  padding: 0;
}

li {
  display: inline-block;
  margin: 0 10px;
}

a {
  color: #42b983;
}
</style>

excelUtils.js

import XLSX from "xlsx-style";
/* @param {数据源 } headers
 * @param {表格副标题 } datasource
 * @param {配置⽂件类型 } options
 * @param {导出的⽂件名 } type
 * @param { fileName}
 */
function exportExcel(headers, datasource, options, type, fileName = "未命名") {
    debugger
    // 处理列宽
    const cloWidth = headers.map((item) => ({ wpx: item.width || 60 }));
    // 处理附加表头
    const _options = options
        .map((item, i) =>
            Object.assign(
                {},
                {
                    title: item.title,
                    position: String.fromCharCode(65) + (i + 1),
                }
            )
        )
        .reduce(
            (prev, next) =>
                Object.assign({}, prev, {
                    [next.position]: { v: next.title },
                }),
            {}
        );
    // 处理表头
    const _headers = headers
        .map((item, i) =>
            Object.assign(
                {},
                {
                    key: item.dataIndex,
                    title: item.title,
                    position: String.fromCharCode(65 + i) + (options.length + 1),
                }
            )
        )
        .reduce(
            (prev, next) =>
                Object.assign({}, prev, {
                    [next.position]: { v: next.title, key: next.key },
                }),
            {}
        );
    // 处理数据源
    const _data = datasource
        .map((item, i) =>
            headers.map((col, j) =>
                Object.assign(
                    {},
                    {
                        content: item[col.dataIndex],
                        position: String.fromCharCode(65 + j) + (options.length + i + 2),
                    }
                )
            )
        )
        .reduce((prev, next) => prev.concat(next))
        .reduce(
            (prev, next) =>
                Object.assign({}, prev, {
                    [next.position]: { v: next.content },
                }),
            {}
        );
    const output = Object.assign({}, _options, _headers, _data);

    const outputPos = Object.keys(output); // 设置表格渲染区域如从到,A1C8
    // 设置单元格样式仅⽣效，写了也不⽣效 xlsx-stylejs-xlsx
    //  这⾥对每个单元格设置样式是写死的，每次改样式改都要改这⾥有点鸡肋
    output.A1.s = {
        font: { sz: 14, bold: true, vertAlign: true },
        alignment: { vertical: "center", horizontal: "center" },
        fill: { bgColor: { rgb: "E8E8E8" }, fgColor: { rgb: "E8E8E8" } },
    };
    output.A2.s = {
        // font: { sz: 12, bold: true, vertAlign: true },
        font: { sz: 12, bold: true, vertAlign: true },
        alignment: { vertical: "center", horizontal: "right" },
    };
    // output.A3.s = {
    //     font: { sz: 12, bold: true, vertAlign: true },
    //     alignment: { vertical: "center", horizontal: "bottom" },
    // };
    // output.A4.s = {
    //     font: { sz: 12, bold: true, vertAlign: true },
    //     alignment: { vertical: "center", horizontal: "bottom" },
    // };
    //合并单元格;
    const merges = options.map((item, i) => ({
        s: { c: 0, r: i },
        e: { c: headers.length - 1, r: i },
    }));
    const wb = {
        SheetNames: ["mySheet"], // 保存的表标题
        Sheets: {
            mySheet: Object.assign(
                {},

                output, // 导出的内容
                {
                    "!ref": `${outputPos[0]}:${outputPos[outputPos.length - 1]}`, // 设置填充区域（表格渲染区域）
                    "!cols": [...cloWidth],
                    "!merges": [...merges],
                }
            ),
        },
    };

    // 这种导出⽅法只适⽤于js - xlsx，且设置的单元格样式不⽣效，
    // 直接打开下⾯这两⾏就⾏了，后⾯的可以省略
    // XLSX.writeFile(wb,`${fileName}.xlsx`);
    // return;
    /**
     * 以下这种导出⽅法对于js-xlsx/xlsx-style都适⽤
     * 区别在于import XLSX from 'xlsx-style';可以设置单元格样式
     * import XLSX from 'xlsx';不⽀持设置单元格样式
     *
     * new Blob转换成⼆进制类型的对象
     */
    const tmpDown = new Blob(
        [
            s2ab(
                XLSX.write(
                    wb,
                    {
                        bookType: type == undefined ? "xlsx" : type.bookType,
                        bookSST: false,
                        type: "binary",
                    } // 这⾥的数据是⽤来定义导出的格式类型
                )
            ),
        ],
        {
            type: "",
        }
    );
    // 数据都准备完成，可以开始下载excel了
    downExcel(
        tmpDown,
        `${fileName + "."}${type.bookType == "biff2" ? "xls" : type.bookType}`
    );
}
/**

    * <a>标签下载excel
    * @param { Blob对象:⼆进制的数据} obj
 * @param { Blob对象:⼆进制的数据} obj
 * @param { ⽂件名+⽂件类型后缀} fileName
 **/
function downExcel(obj, fileName) {
    const a_node = document.createElement("a");
    a_node.download = fileName;
    // 兼容ie
    if ("msSaveOrOpenBlob" in navigator) {
        window.navigator.msSaveOrOpenBlob(obj, fileName);
    } else {
        // URL.createObjectURL根据传⼊的参数创建⼀个指向该参数对象的   URL  这个URL的⽣命仅存在于它被创建的这个⽂档⾥
        // 新的对象URL指向执⾏的File对象或者是Blob对象
        a_node.href = URL.createObjectURL(obj);
    }
    a_node.click();

    // 每次调⽤createObjectURL的时候⼀个新的URL对象就被创建了即使你已经为同⼀个⽂件创建过⼀个URL
    // 如果你不再需要这个对象 要释放它 需要使⽤ ⽅法 URL.revokeObjectURL()
    // 当页⾯被关闭 浏览器会⾃动释放它 但是为了最佳性能和内存使⽤ 当确保不再⽤得到它的时候 就应该释放它
    setTimeout(() => {
        URL.revokeObjectURL(obj);
    }, 100);
}
// 字符串转字符流---转化为⼆进制的数据流

function s2ab(s) {
    if (typeof ArrayBuffer !== "undefined") {
        const buf = new ArrayBuffer(s.length);
        const view = new Uint8Array(buf);
        for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
        return buf;
    } else {
        const buf = new Array(s.length);
        for (let i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff;
        return buf;
    }
}
export { exportExcel };