纯前端Vue框架实现Excel按需导出（按所选择列进行导出）

实现效果

 导出效果

 web页面效果

实现前准备：

1.安装所需依赖包

npm install file-saver -S                  //使用版本：^2.0.5

npm install script-loader -S             //使用版本：^0.7.2

npm install xlsx -S                          //使用版本：^0.17.3

npm install xlsx-populate -S           //使用版本：^1.21.0

2.引入excel数据库，其中有一个小坑，在最新版本导入XLSX时，会出现报错，handleExport函数中util未定义的情况，需要将导入方式进行修改

将其import XLSX from "xlsx";

改为import * as XLSX from "xlsx";

//导入 xlsx 模块
import XLSX from "xlsx";
//导入 xlsxPopulate 模块
import XlsxPopulate from "xlsx-populate";
// 用于统一设置报表的样式  “A" "B"  EXCEL 的列
const alphabetList = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",];

function workbook2blob(workbook) {
  // 生成excel的配置项
  const wopts = {
    // 要生成的文件类型
    bookType: "xlsx",
    // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    bookSST: false,
    type: "binary",
  };
  const wbout = XLSX.write(workbook, wopts);
  // 将字符串转ArrayBuffer
  function s2ab(s) {
    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;
  }
  const blob = new Blob([s2ab(wbout)], {
    type: "application/octet-stream",
  });
  return blob;
}
//导出 handleExport
export function handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo) {
  //表格数据——中英文版
  const finalDataZh = [...titleZh, ...tableZh];
  const finalDataEn = [...titleEn, ...tableEn];
  const wb = XLSX.utils.book_new();
  const sheetZh = XLSX.utils.json_to_sheet(finalDataZh, { skipHeader: true,});
  const sheetEn = XLSX.utils.json_to_sheet(finalDataEn, { skipHeader: true,});
  //第三个参数为生成excel sheet的名称
  XLSX.utils.book_append_sheet(wb, sheetZh, "Chinese");
  XLSX.utils.book_append_sheet(wb, sheetEn, "English");
  const workbookBlob = workbook2blob(wb);
  return addStyle(workbookBlob, dataInfo);
}
//添加样式的方法
function addStyle(workbookBlob, dataInfo) {
  return XlsxPopulate.fromDataAsync(workbookBlob).then((workbook) => {
    // 循环所有的表改变样式
    for (let index = 0; index < workbook._maxSheetId; index++) {
      // 设置行高 sheet(’sheet号’).row(行号).height('行高')
      workbook.sheet(index).row(1).height(25);
      // 取消垂直居中
      // workbook.sheet(index).printOptions("verticalCentered", undefined);
      //打印页边距预模板
      workbook.sheet(index).pageMarginsPreset("narrow");
      //可以给指定的格子添加内容并合并单元格
      // workbook.sheet(index).range("M43:P43").value('要写入的内容:').merged(true)
    }
    workbook.sheets().forEach((sheet) => {
      // 所有cell垂直居中,修改字体
      sheet.usedRange().style({
        fontFamily: "Arial",
        verticalAlignment: "center",
      });
      // 去除所有边框 (网格线)
      // sheet.gridLinesVisible(false);
      // 设置单元格宽度
      alphabetList.forEach((item) => {
        sheet.column(item).width(15);
      });
      // 合并单元格
      if(dataInfo.mergesRange){
        for(let i=0;i<dataInfo.mergesRange.length;i++){
          sheet.range(dataInfo.mergesRange[i]).merged(true).style({
          //水平居中
          horizontalAlignment: "center",
          //垂直居中
          verticalAlignment: "center",
          });
        }
      }
      // .style 是添加样式
      // title加粗合并及居中
      sheet.range(dataInfo.titleRange).merged(true).style({
        //加粗
        bold: true,
        //水平居中
        horizontalAlignment: "center",
        //垂直居中
        verticalAlignment: "center",
        //字号
        fontSize: 14,
      });
      sheet.range(dataInfo.tbodyRange).style({
        horizontalAlignment: "center",
        //内容放不下时候允许换行
        wrapText: true,
        fontSize: 10,
      });
      if(dataInfo.keystyle){
        for(let i=0;i<dataInfo.keystyle.length;i++){
          sheet.column(dataInfo.keystyle[i]).style({
            wrapText: true,
            horizontalAlignment: "left",
          });
          sheet.column(dataInfo.keystyle[i]).width(60)
        }
      }
      // 表头加粗及背景色
      sheet.range(dataInfo.theadRange).style({
        wrapText: true,
        fill: "C9C7C7",
        bold: true,
        horizontalAlignment: "center",
        fontSize: 10,
      });
      // 表格黑色细边框
      sheet.range(dataInfo.tableRange).style({
        border: {
          style: "thin",
          color: "000000",
          direction: "both",
        },
      });
    });
    return workbook.outputAsync().then(
      (workbookBlob) => URL.createObjectURL(workbookBlob) // 创建blob地址
    );
  });
}

层级结构

Demos父级（可以根据实际业务需求修改）

<el-tab-pane label="按需导出Excel" name="first">
   // 按需导出按钮及选项弹出层
  <ExportExcel :totaltableList="totaltableList"></ExportExcel>
  <el-table :data="totaltableList" border>
    <el-table-column label="考核名称" prop="kpi_name"></el-table-column>
    <el-table-column label="归属用户" prop="user_name"></el-table-column>
    <el-table-column label="开始日期" prop="start_time"></el-table-column>
    <el-table-column label="操作" width="130px">
      <el-button type="primary" icon="el-icon-edit" size="mini"></el-button>&nbsp;
      <el-button type="danger" icon="el-icon-delete" size="mini"></el-button>
    </el-table-column>
  </el-table>
</el-tab-pane>




// 引入选择按列导出弹窗组件
import ExportExcel from "./ExportExcel";


// 注册组件
 components: {
    ExportExcel,
 },



// 要输出的的数据内容，实际数据来自接口
totaltableList: [
        {
          id: 1,
          kpi_name: "2022年4月月度考核表",
          kpi_name_en: "2022-04-monthly-check-form",
          user_name: "张三",
          start_time: "2022-04-01",
          goal_list: [
            {
              goal: "出勤率",
              goal_en: "attendance rate",
              kpi_method: "出勤率达到100%，迟到一次扣10元。",
              kpi_method_en:
                "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
              weight: 50,
              end_score: 99,
            },
            {
              goal: "工作态度",
              goal_en: "working attitude",
              kpi_method: "积极主动，态度端正。",
              kpi_method_en: "Initiative and good attitude.",
              weight: 50,
              end_score: 98,
            },
          ],
        },
        {
          id: 2,
          kpi_name: "2022年5月月度考核表",
          kpi_name_en: "2022-05-monthly-check-form",
          user_name: "李四",
          start_time: "2022-05-01",
          goal_list: [
            {
              goal: "出勤率",
              goal_en: "attendance rate",
              kpi_method: "出勤率达到100%，迟到一次扣10元。",
              kpi_method_en:
                "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
              weight: 50,
              end_score: 100,
            },
            {
              goal: "工作态度",
              goal_en: "working attitude",
              kpi_method: "积极主动，态度端正。",
              kpi_method_en: "Initiative and good attitude.",
              weight: 50,
              end_score: 100,
            },
          ],
        },
      ],

Demos子级

1.columnListModel列弹出框选项：

   name中文表头，name_en英文表头，filed_code当且列下的内容，

2.const isHasChildExcel = filterVal.includes('goal_list')

   // 判断某头部下是否还有子级

   // 根据goal_list长度新增行，导出Excel包含子表格时执行

<template>
  <div>
    <div>
      <el-button class="exportBtn"
                 size="small"
                 @click="selectcloumnDrawer = true">导出Excel</el-button>
    </div>
    <!-- 按需导出列 -->
    <el-dialog :visible.sync="selectcloumnDrawer"
               title="选择列"
               @close="closeDialog()">
       <ExportColumn :columnList="columnListModel"
                    @exportExcel="exportExcel"></ExportColumn>
    </el-dialog>
  </div>
</template>

<script>
// 引入选择按列导出弹窗组件
import ExportColumn from './ExportColumn'
import { handleExport } from '../../../../excel/export.js'

export default {
  components: {
    ExportColumn,
  },
  props: {
    totaltableList: {
      type: Array,
      default: () => [],
    },
  },
  data() {
    return {
      selectcloumnDrawer: false, //控制导出列弹窗显示
      columnList: [], //选中的列
      columnListModel: [
        //全部列
        { name: '考核名称', name_en: 'Check Name', field_code: 'kpi_name' },
        { name: '归属用户', name_en: 'belong user', field_code: 'user_name' },
        { name: '开始日期', name_en: 'start time', field_code: 'start_time' },
        { name: '目标详情', name_en: 'goal detail', field_code: 'goal_list' },
      ],
    }
  },
  methods: {
    //导出excel
    exportExcel(checkedColumn) {
      this.columnList = checkedColumn
      this.exportExcelfn()
    },
    exportExcelfn() {
      const alphabetList = [
        'A',
        'B',
        'C',
        'D',
        'E',
        'F',
        'G',
        'H',
        'I',
        'J',
        'K',
        'L',
        'M',
        'N',
        'O',
        'P',
        'Q',
        'R',
        'S',
        'T',
        'U',
        'V',
        'W',
        'X',
        'Y',
        'Z',
      ] //主表格数据
      const tHeaderZh = this.columnList.map((p) => {
        return p.name
      }) //选择导出列的中文表头
      const tHeaderEn = this.columnList.map((p) => {
        return p.name_en
      }) //选择导出列的英文表头
      const filterVal = this.columnList.map((p) => {
        return p.field_code
      }) //选择导出列的字段key //获取导出表格的原始数据
      var list = JSON.parse(JSON.stringify(this.totaltableList)) //totaltableList是表格json数据
      const isHasChildExcel = filterVal.includes('goal_list') //判断是否导出目标详情子表格 //根据goal_list长度新增行，导出Excel包含子表格时执行
      if (isHasChildExcel) {
        //子表格数据
        const cHeaderZh = ['考核项目', '考核办法', '比重(%)', '最后得分']
        const cHeaderEn = [
          'Check Item',
          'Check Target',
          'weight(%)',
          'Check Method',
          'score',
        ]
        const cfilterVal = ['goal', 'kpi_method', 'weight', 'end_score']
        const endData = {
          goalValue: cHeaderZh,
          goalEnValue: cHeaderEn,
          keyValue: cfilterVal,
        }
        var cHeaderLen = cHeaderZh.length //根据goal_list长度新增行
        list.forEach((p, index) => {
          var ret = []
          if (p.goal_list.length === 0) {
            ret.push(p)
          } else {
            p.goal_list.unshift(endData)
            for (var i = 0; i < p.goal_list.length; i++) {
              ret.push(p)
            }
          }
          list.splice(index, 1, ret)
        })
        list = [].concat(...list) //包含嵌套表格数据时导出的表头数据
        for (var i = 1; i < cHeaderZh.length; i++) {
          tHeaderZh.push('目标详情')
          tHeaderEn.push('goal detail')
        }
        filterVal.splice(-1, 1, ...cfilterVal)
      } //将数组转为二维数组
      const dataZh = this.formatJson(filterVal, list)
      const dataEn = this.formatJson(filterVal, list, 'language') //将数组中的对象转为{A:'',B:''}格式
      const tableZh = this.changecolmuntokey(tHeaderZh, dataZh, alphabetList)
      const tableEn = this.changecolmuntokey(tHeaderEn, dataEn, alphabetList) //设置合并单元格行
      if (isHasChildExcel) {
        var rowSpanList = []
        let rowSpan = {}
        const goallength = this.totaltableList.map((p) => p.goal_list.length)
        const columnlen = isHasChildExcel
          ? tHeaderZh.length - cHeaderLen
          : tHeaderZh.length
        for (let j = 0; j < columnlen; j++) {
          let index = 0
          let i = 0
          for (; i < goallength.length; ) {
            for (; index < dataZh.length; ) {
              rowSpan = [
                [j, index + 3],
                [j, index + 3 + goallength[i]],
              ] //+3:从第三行开始才需要行合并
              rowSpanList.push(rowSpan)
              index = index + goallength[i] + 1
              i++
            }
          }
        } //单元格行合并
        var mergesRange = rowSpanList.map((p) => {
          var mergeslist = []
          for (let index = 0; index < columnlen; index++) {
            var data = p.map((q) => {
              const ret = alphabetList[index].toString() + q[1].toString()
              return ret
            })
            data = data.join(':')
            mergeslist.push(data)
          }
          return mergeslist
        }) //单元格列合并
        mergesRange.unshift(
          `${alphabetList[columnlen]}2:${alphabetList[tHeaderZh.length - 1]}2`
        )
      } //指定Excel样式和合并范围等属性
      const dataInfo = {
        titleCell: 'A1',
        titleRange: `A1:${alphabetList[tHeaderZh.length - 1]}1`,
        theadRange: `A2:${alphabetList[tHeaderZh.length - 1]}2`,
        tbodyRange: `A3:${alphabetList[tHeaderZh.length - 1]}${
          dataZh.length + 2
        }`,
        tableRange: `A2:${alphabetList[tHeaderZh.length - 1]}${
          dataZh.length + 2
        }`,
      } //有子表格时才有合并单元格
      if (isHasChildExcel)
        this.$set(dataInfo, 'mergesRange', mergesRange.flat()) //表格第一行标题
      const titleZh = [{ A: '员工考核表' }]
      const titleEn = [{ A: 'Employee Evaluation Table' }] //传入得到的数据，导出引用handleExport方法导出
      handleExport(tableZh, tableEn, titleZh, titleEn, dataInfo).then((url) => {
        const downloadAnchorNode = document.createElement('a')
        downloadAnchorNode.setAttribute('href', url)
        downloadAnchorNode.setAttribute(
          'download',
          'ExportList-Kpis.xlsx' //自定义导出文件的名称
        )
        downloadAnchorNode.click()
        downloadAnchorNode.remove()
      })
      setTimeout(() => {
        this.closeDialog('exportexcel')
      })
    }, //将数组中的对象转为{A:'',B:''}格式
    changecolmuntokey(tHeaderZh, dataZh, alphabetList) {
      const table = []
      const list1 = [] //表头转为{A:'',B:''}格式
      tHeaderZh.map((p, i) => {
        this.$set(list1, alphabetList[i], p)
      })
      table.push(list1) //表身转为{A:'',B:''}格式
      dataZh.map((p) => {
        const list = {}
        for (let i = 0; i < p.length; i++) {
          this.$set(list, alphabetList[i], p[i])
        }
        table.push(list)
      })
      return table
    }, //自定义二维数组数据格式
    formatJson(filterVal, jsonData, language) {
      var result1 = jsonData.map((v, vIndex) => {
        var result = filterVal.map((j) => {
          if (j === 'kpi_name') {
            if (language) {
              return v[j + '_en']
            } else {
              return v[j]
            }
          } else if (
            j === 'goal' ||
            j === 'kpi_method' ||
            j === 'weight' ||
            j === 'end_score'
          ) {
            //有子表格数据时根据索引显示
            var goalIndex = 0
            if (vIndex !== 0) {
              for (var i = 1; i <= v.goal_list.length; i++) {
                if (vIndex >= i) {
                  if (jsonData[vIndex].id === jsonData[vIndex - i].id) {
                    goalIndex = i
                  }
                }
              }
            }
            if (v.goal_list[goalIndex]) {
              if ('keyValue' in v.goal_list[goalIndex]) {
                for (var i in v.goal_list[goalIndex].keyValue) {
                  if (v.goal_list[goalIndex].keyValue[i] === j) {
                    if (language) {
                      return v.goal_list[goalIndex].goalEnValue[i]
                    } else {
                      return v.goal_list[goalIndex].goalValue[i]
                    }
                  }
                }
              }
              if (
                language &&
                (j === 'goal' || j === 'kpi_method' || j === 'goal_str')
              ) {
                return v.goal_list[goalIndex][j]
                  ? v.goal_list[goalIndex][j + '_en']
                  : ''
              } else {
                return v.goal_list[goalIndex][j]
                  ? v.goal_list[goalIndex][j]
                  : ''
              }
            } else {
              return ''
            }
          } else {
            return v[j]
          }
        })
        return result
      })
      return result1
    },
    closeDialog() {
      this.selectcloumnDrawer = false
      this.$bus.$emit('refershcolumn')
    },
  },
}
</script>
<style scoped>
.exportBtn {
  float: right;
  margin: 4px auto;
}
::v-deep .el-dialog__header {
  background-color: #ecf1f6;
  margin-bottom: 4px;
}
::v-deep .el-dialog__body {
  padding: 2px 20px 20px;
}
</style>

ExportColumn组件（其中有一个小坑：就是this.$bus.$on出现报错，原因是因为当前main.js文件中没有定义$bus，需要给main.js文件中添加Vue.prototype.$bus = new Vue();在vue的原型中添加一个$bus，就解决这个问题了）

<template>
    <div>
        <div class="dialog_body">
            <el-checkbox :indeterminate="isIndeterminate" v-model="checkAll" @change="handleCheckAllChange" style="float:left">全选</el-checkbox>
            <div style="margin: 15px 0;"></div>
            <el-checkbox-group v-model="checkedColumn" @change="handleCheckedColumnChange" class="flexcolumn">
                <el-checkbox style="display:flex;white-space:normal;width:142px;margin-right:10px;word-break:break-word;height:30px;" v-for="itemKey in columnList" :label="itemKey" :key="itemKey.name">{{itemKey.name}}</el-checkbox>
            </el-checkbox-group>
        </div>
        <div class="right_sub_btn">
            <el-button type="primary" @click="exportExcel">确定导出</el-button>
            <el-button @click="resetexportColumn">重置</el-button>
        </div>
    </div>
</template>

<script>
export default {
    name: 'ExportColumn',
    props:{columnList:Array},
    data() {
        return {
            checkAll:false,
            checkedColumn:[],
            isIndeterminate:false
        }
    },
    mounted(){
        this.$bus.$on('refershcolumn',this.resetexportColumn)
        console.log(this.columnList);
    },
    methods:{
        handleCheckAllChange(val) {
            this.checkedColumn = val ? [...this.columnList] : [];
            this.isIndeterminate = false;
        },
        handleCheckedColumnChange(value) {
            let checkedCount = value.length;
            this.checkAll = checkedCount === this.columnList.length;
            this.isIndeterminate = checkedCount > 0 && checkedCount < this.columnList.length;
        },
        exportExcel(){
            if(this.checkedColumn.length === 0){
                this.openmessage()
            }else{
                this.$emit('exportExcel',this.checkedColumn)
            }
        },
        openmessage(){
            this.$message({message: '请选择导出列',type: 'error',offset:200,duration:2000})
        },
        resetexportColumn(){
            this.checkedColumn.splice(0,this.checkedColumn.length)
            this.checkAll = false
            this.isIndeterminate = false
        },
    }
}
</script>
<style scoped>
.dialog_body{
    margin-bottom: 50px;
}
.right_sub_btn{
    position: absolute;
    right: 10px;
    bottom: 20px;
}
.flexcolumn{
    width: 100%;
    display: flex;
    flex-wrap: wrap;
    justify-content: left;
}
</style>