vuejs用exceljs轻松解决excel导出表格出现的合并、居中等样式属性

今天介绍ExcelJS的基本用法，主要介绍设置每列的宽度、合并、居中、标签等样式属性。代码如下：

<template>
  <div class="wrap">
    <div>
      <el-button type="primary" @click="exportExcel">导出excel</el-button>
    </div>
    <div>
      <el-table
        border
        :data="tableData"
        style="width: 100%"
      >
        <template v-for="(item, index) in tableHeader">
          <el-table-column :key="index" v-bind="item"> </el-table-column>
        </template>
      </el-table>
    </div>
  </div>
</template>
<script>
import ExcelJS from "exceljs";
import FileSaver from "file-saver";
export default {
  name: "exceljs-demo",
  data() {
    return {
      tableHeader: [
        {
          prop: "date",
          label: "日期"
        },
        {
          prop: "name",
          label: "姓名"
        },
        {
          prop: "address",
          label: "地址"
        }
      ],
      tableData: [
        {
          date: "2016-05-01",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1519 弄"
        },
        {
          date: "2016-05-01",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1516 弄"
        },
        {
          date: "2016-05-02",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1518 弄"
        },
        {
          date: "2016-05-03",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1517 弄"
        },
        {
          date: "2016-05-04",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1519 弄"
        },
        {
          date: "2016-05-05",
          name: "王小虎",
          address: "上海市普陀区金沙江路 1516 弄"
        }
      ]
    };
  },
  methods: {
    // 是否为空
    $isNull(val) {
      return (
        val === null || val === void 0 || val === "" || val.toString() === "NaN"
      );
    },
    async exportExcel() {
      const workbook = new ExcelJS.Workbook();
      // 创建带有红色标签颜色的工作表
      const sheet = workbook.addWorksheet("My Sheet", {
        properties: { tabColor: { argb: "FFC0000" } }
      });

      // 生成columns
      let columns = [];
      this.tableHeader.map(item => {
        columns.push({
          name: item.label
          // width: 30 // 这里设置宽度不起作用，详情请看table 下 列属性，里面没有width 属性
        });
      });

      //设置宽度
      sheet.getColumn("A").width = 15;
      sheet.getColumn("B").width = 15;
      sheet.getColumn("C").width = 35;

      //合并
      sheet.mergeCells("A2:A3");
	  
      // 表头 居中并设置边框getCell/头、getColumn列表
      let h1 = sheet.getColumn("A");
      let h2 = sheet.getColumn("B");
      let h3 = sheet.getColumn("C");
      let arr = [h1, h2, h3];
      arr.map(item => {
        // 居中
        item.alignment = { vertical: "middle", horizontal: "center" };
        // 边框
        item.border = {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" }
        };
      });
      // 生成rows
      let rows = [];
      this.tableData.map(item => {
        let arr = [];
        this.tableHeader.map(sub => {
          arr.push(!this.$isNull(item[sub.prop]) ? item[sub.prop] : "");
        });
        rows.push(arr);
      });
      //数据表格位置
      sheet.addTable({
        name: "MyTestTable",
        ref: "A1", // 表格左上角的位置
        headerRow: true,
        totalsRow: false,
        style: {
          theme: "TableStyleLight1"
        },
        columns: columns,
        rows: rows
      });

      // 导出
      workbook.xlsx.writeBuffer().then(buffer => {
        FileSaver.saveAs(
          new Blob([buffer], { type: "application/octet-stream" }),
          `test.xlsx`
        );
      });
    }
  }
};
</script>