vue项目前端实现将table导出成excel功能2

使用插件

xlsx、xlsx-style、file-saver
具体引入见文章：vue项目前端实现将table导出成excel功能1

方法一

exportExcelByData：将数据导出成excel，合并单元格需要另外设置

方法二

exportExcelByDom：将页面dom(el-table)导出成excel，页面有合并单元格，导出直接就合并了，但是有个瑕疵，如果导出的excel单元格有边框，有的情况下能看出来边框多加了一个单元格，如下图：

审查元素发现是因为el表头多了一个隐藏的th导致的，咱未找到解决办法，所以建议使用exportExcelByData来导出

新建ExcelUtil.js

import * as XLSX from 'xlsx';
import XLSXS from 'xlsx-style';
import FileSaver from 'file-saver';
function setExcelStyle(data, bgColorArr) {
  let borderAll = {
    //单元格外侧框线
    top: {
      style: "thin",
    },
    bottom: {
      style: "thin",
    },
    left: {
      style: "thin",
    },
    right: {
      style: "thin",
    },
  };

//添加表头zuo
  data['!cols'] = [];
  for(let key in data) {
    if(data[key].constructor === Object) {
      data[key].s = {
        border: borderAll,  // 边框
        alignment: {
          horizontal: "center", //水平居中对齐
          vertical: "center", // 垂直居中
          wrapText: 1,// 换行
          indent: 0
        },
        font: {
          sz: 10,
        },
        bold: true,
        numFmt: 0
      };
      // 给指定单元格添加背景色
      if(bgColorArr&&bgColorArr.indexOf(key) !== -1) {
        data[key].s.fill = {
          fgColor: {"rgb": "e5f6fc"}
        }
      }
      // data["!cols"].push({ wpx: 180 }); // 单元格宽度
    }
  }
}
function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
  return buf
}


export const exportExcelByData = (data, name = '导出表格', sheetName = 'Sheet1', bgColorArr, mergesArr = null ) => {
  //mergesArr 需要合并的单元格数据[{s: {r: 0, c: 0}, e: {r: 0, c: 11}}]
  // bgColorArr 需要添加背景色的单元格数据
  // data为二维数组时，导出一个sheet页
  // data为一个json对象，每个value值为一个二维数组，此时导出多个sheet页
  const wb = XLSX.utils.book_new();
  let ws = null;
  if(sheetName&&Array.isArray(sheetName)) {// 此时的data是以json对象形式传过来的,导出多个sheet页
    for(let key in data) {
      ws= XLSX.utils.aoa_to_sheet(data[key]);
      setExcelStyle(ws, bgColorArr);
      XLSX.utils.book_append_sheet(wb, ws, key+'年阈值表');
    }
  } else {// 导出一个sheet页
    ws= XLSX.utils.aoa_to_sheet(data);
    setExcelStyle(ws, bgColorArr);
    XLSX.utils.book_append_sheet(wb, ws, sheetName);
  }
  // 设置单元格合并
  if(mergesArr&&mergesArr.length > 0) {
    ws['!merges'] = mergesArr;
  }
  /* save to file */
  let wbout = XLSXS.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });
  // var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'array' })
  try {
    FileSaver.saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), name + '.xlsx')
  } catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
  // return wbout
  // XLSX.writeFile(wb, 'SheetJS.xlsx');
  // 设置导出Excel样式 这里主要是关注单元格宽度
};

export const exportExcelByDom = (domId, name = '导出表格', sheetName = 'Sheet1') => {
  console.log(XLSX);
  /* generate workbook object from table */
  // 设置导出的内容是否只做解析，不进行格式转换     false：要解析， true:不解析
  const xlsxParam = { raw: true };
  var wb = XLSX.utils.table_to_book(document.querySelector('#'+domId), xlsxParam);

  /* get binary string as output */
  setExcelStyle(wb['Sheets'][sheetName], '一级');
  let wbout = XLSXS.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });
  try {
    FileSaver.saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), name + '.xlsx')
  } catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
  return wbout
};

使用

  import { exportExcelByData } from '@/utils/ExcelUtil'
  // html
	<el-table
	  ref="residualRiskTable"
	  id="residualRiskTable"
	  class="risk-assess-table"
	  :data="list"
	  style="width: 100%"
	  border
	  :span-method="objectSpanMethod"
	  :header-cell-style="{
	          background: '#DBE0F2',
	          color: '#333333',
	          padding: '12px 0',
	          'text-align': 'center',
	        }"
		>
	  <el-table-column
	    v-for="(o, idx) in tableHeader"
	    :key="o.prop"
	    :label="o.name"
	    align="center"
	    :prop="o.prop"
	    :width="o.width?o.width: ''">
	    <template slot-scope="scope">
	      <template v-if="o.isEdit">
	        <el-input v-model="scope.row[o.prop]" size="small"></el-input>
	        <span class="hideNum">{{ scope.row[o.prop] }}</span>
	      </template>
	      <span v-else>{{ scope.row[o.prop] }}</span>
	    </template>
	  </el-table-column>
	</el-table>

  data() {
	return {
		tableHeader: [
		  { name: '一级业务流程', prop: 'firstProcess', width: '' },
          { name: '二级业务流程', prop: 'secondProcess', width: '' },
          { name: '剩余风险等级', prop: 'currentResidualRiskLevel', width: '' },
          { name: '剩余风险等级', prop: 'beforeFiResidualRiskLevel', width: '' },
          { name: '剩余风险等级', prop: 'beforeSeResidualRiskLevel', width: '' },
          { name: '剩余风险等级', prop: 'beforeThResidualRiskLevel', width: '' },
          { name: '剩余风险等级', prop: 'beforeFoResidualRiskLevel', width: '' },
          { name: '剩余风险等级', prop: 'beforeFiveResidualRiskLevel', width: '' },
          { name: '近三年各单位审计覆盖率', prop: 'lastThreeAuditCov', width: '' },
          { name: '2020年统计的覆盖率', prop: 'beforeThirdCov', width: '', isEdit: true },
          { name: '2021年统计的覆盖率', prop: 'beforeSecondCov', width: '', isEdit: true },
          { name: '2022年统计的覆盖率', prop: 'beforeFirstCov', width: '', isEdit: true }
		],
		list: [
			{
            taskId: 1,
            firstProcess: '1.战略规划管理流程',
            secondProcess: 5,
            currentResidualRiskLevel: 5,
            beforeFiResidualRiskLevel: 50,
            beforeSeResidualRiskLevel: 1,
            beforeThResidualRiskLevel: 4,
            beforeFoResidualRiskLevel: 2,
            beforeFiveResidualRiskLevel: 2,
            lastThreeAuditCov: 1,
            beforeThirdCov: 1,
            beforeSecondCov: 3,
            beforeFirstCov: 4,
          }
          .......
		],
		exportData: [],
		spanArr: []// 合并单元格用到的
	}
  },
  mounted() {
	this.filterData(this.list);
  },
  methods: {
   // 格式化数据为可导出的格式
      formatterData() {
      	// 根据自己的数据，拼出this.exportData，this.exportData是个二维数组
        let header = [];
        let title = ['二业务流程剩余风险水平', '', '', '', '', '', '', '', '', '', '', ''];
        // title为导出时给excel添加的标题，原页面中是没有的
        let keysArr = [];
        this.tableHeader.forEach(item => {
          header.push(item.name);
          keysArr.push(item.prop);
        });
        this.exportData = [title, header];
        this.list.forEach(item => {
          let arr = [];
          keysArr.forEach(o => {
            arr.push(item[o]);
          });
          this.exportData.push(arr)
        });
      },
      exportExcelByDataFn() {
		this.formatterData();
        let mergesArr = [
          {s: {r: 0, c: 0}, e: {r: 0, c: 11}}
        ];
        let bgColorArr = ['A1', 'A2', 'B2', 'C2', 'D2', 'E2', 'F2', 'G2', 'H2', 'I2', 'J2', 'K2', 'L2'];
        exportExcelByData(this.exportData, '剩余风险水平表', '剩余风险水平表', bgColorArr, mergesArr)
	},
	objectSpanMethod({ row, column, rowIndex, columnIndex }) {
        if (columnIndex === 0) {
          if(this.spanArr[rowIndex]){
            return {
              rowspan: this.spanArr[rowIndex],
              colspan: 1
            }
          }else{
            return {
              rowspan: 0,
              colspan: 0
            }
          }
        }else {
          return {
            rowspan: 1,
            colspan: 1
          }
        }
      },
      filterData(data) {
        let contactDot = 0;
        let spanArr = [];
        data.forEach((item, index) => {
          if (index === 0) {
            spanArr.push(1)
          } else {
            //注释：firstProcess  是对应体系,data 对应table绑定的数据源
            if (item.firstProcess === data[index - 1].firstProcess) {
              spanArr[contactDot] += 1;
              spanArr.push(0)
            } else {
              contactDot = index;
              spanArr.push(1)
            }
          }
        })
        this.spanArr = spanArr;
      },
}

exportData数据格式如下：

导出结果如下：