excelJs导出表格的基本使用

<template>
  <div style="width: 100%;height: 100%;border: 5px solid palevioletred;">
    <button @click="export2">导出</button>
  </div>
</template>

<script>
  // 这两个是必须项
  require('script-loader!file-saver')
  import ExcelJS from 'exceljs'
  export default {
    data(){
      return{
        // sheet 2 3 附表
        selectBaseData: [
          {
            type: [{
              header: '教室',
              key: 'room',
              width: 20
            }],
            value: [
              {
                room: '是'
              }, {
                room: '否'
              }
            ]
          },
          {
            type: [{
              header: '类型',
              key: 'type',
              width: 20
            }],
            value: [
              {
                type: '管理员'
              }, {
                type: '学员'
              }, {
                type: '教员'
              }
            ]
          }
        ],
        // 表头
        // 数据是根据表头的key值判断
        titleColumns: [
          {
            header: '编号',
            key: 'no',
            width: 20
          }, // A1
          {
            header: '姓名',
            key: 'name',
            width: 20
          }, // B2
          {
            header: '类型',
            key: 'type',
            width: 20
          }, // B2
          {
            header: '短波教室',
            key: 'room1',
            width: 20
          }, // C3
          {
            header: '超短波教室',
            key: 'room2',
            width: 20
          }, // D4
        ],
        // 每一排的数据
        data: [
          {
            no: '1',
            name: '小红',
            type:'管理员',
            room1: '是',
            room2: '否',
          },
          {
            no: '2',
            name: '小黄',
            type:'学员',
            room1: '是',
            room2: '是',
          }
        ]
      }
    },
    methods:{
      export2() {
        const wb = new ExcelJS.Workbook();
        // 组装表1
        const Sheet1 = wb.addWorksheet('Sheet1');
        Sheet1.columns = this.titleColumns;
        const Sheet1_data = this.data;
        Sheet1.addRows(Sheet1_data);
        // 组装表2
        const Sheet2 = wb.addWorksheet('Sheet2');
        Sheet2.columns = this.selectBaseData[0].type;
        const Sheet2_data = this.selectBaseData[0].value;
        Sheet2.addRows(Sheet2_data);
        // 组装表3
        const Sheet3 = wb.addWorksheet('Sheet3');
        Sheet3.columns = this.selectBaseData[1].type;
        const Sheet3_data = this.selectBaseData[1].value;
        Sheet3.addRows(Sheet3_data);


        // 表1与表2和3相关联
        new Array(1000).fill(0).forEach((_, idx) => {
          const row = idx + 2;
          // 渲染部门下拉框
          // 表1的第三列开始,与表2和3的某个坐标相关联
          Sheet1.getCell(row, 3).dataValidation = {
            type: 'list',
            formulae: [`=Sheet3!$A$2:$A${Sheet3_data.length + 1}`]
          };
          // 渲染部门下拉框
          Sheet1.getCell(row, 4).dataValidation = {
            type: 'list',
            formulae: [`=Sheet2!$A$2:$A${Sheet2_data.length + 1}`]
          };
          // 使用indirect函数添加引用, 渲染性别值
          Sheet1.getCell(row, 5).dataValidation = {
            type: 'list',
            formulae: [`=Sheet2!$A$2:$A${Sheet2_data.length + 1}`]
          };
        });
        // 保存设置
        wb.xlsx.writeBuffer().then(buffer => {
          saveAs(new Blob([buffer], {
            type: 'application/octet-stream'
          }), `test.xlsx`)
        })
      },
    }
  }
</script>