使用插件

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",},};//添加表头zuodata['!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-tableref="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-columnv-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数据格式如下：

导出结果如下：