Element ui复杂表格(多级表头、尾行求合、单元格合并)前端导出excel

效果展示

前端展示表格

导出表格

依赖安装

使用nmp安装依赖：xlsx、xlsx-style

 npm install xlsx --save
 npm install xlsx-style --save

安装xlsx-style的坑
用npm install xlsx-style --save命令可能会安装失败，所以推荐使用cnpm install xlsx-style --save命令进行安装，安装好后不出意外程序会报错Can‘t resolve ‘./cptable‘ in ‘xxx\node_modules_xlsx，解决方法网上搜索即可，如在vue.config.js中添加

configureWebpack: {
    externals:{
        './cptable': 'var cptable'
    },
}

工具模块

exportExcelUtil.js

点击查看代码

import *  as XLSX from "xlsx";
import *  as XLSX_STYLE from "xlsx-style";

const ALL_LETTER = ["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 DEFAULT_HEADER_WITH = 210;

/**
 * 去除多余的行数据
 * @param wb
 * @returns {*}
 */
const removeLastSumRow = (wb) => {
    let arr = wb['!merges'];
    let maxRow = parseInt(wb['!ref'].split(":")[1].replace(/[^0-9]/ig, ""));
    let removeIndex = [];
    for (let i = 0; i < arr.length; i++) {
        let startCell = arr[i].s;
        let endCell = arr[i].e;
        if (startCell.r + 1 >= maxRow || endCell.r + 1 >= maxRow) {
            removeIndex.push(i);
        }
    }
    wb['!merges'] = [];
    for (let i = 0; i < arr.length; i++) {
        if (removeIndex.indexOf(i) === -1) {
            wb['!merges'].push(arr[i]);
        }
    }
    return wb;
}
/**
 * 为合并项添加边框
 * @param range
 * @param ws
 * @returns {*}
 */
const addRangeBorder = (range, ws) => {
    if (range) {
        range.forEach(item => {
            let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
            let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
            const test = ws[ALL_LETTER[startRowNumber] + (startColNumber + 1)];
            for (let col = startColNumber; col <= endColNumber; col++) {
                for (let row = startRowNumber; row <= endRowNumber; row++) {
                    ws[ALL_LETTER[row] + (col + 1)] = test;
                }
            }
        })
    }
    return ws;
}
/**
 * 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
 * @param sheet
 * @param sheetName
 * @returns {Blob}
 */
const sheet2blob = (sheet, sheetName) => {
    sheetName = sheetName || 'sheet1';
    let workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    };
    workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
    let wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
    };
    let wbout = XLSX_STYLE.write(workbook, wopts);
    let blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }); // 字符串转ArrayBuffer
    function s2ab(s) {
        let buf = new ArrayBuffer(s.length);
        let view = new Uint8Array(buf);
        for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }

    return blob;
}
/**
 * 下载
 * @param url
 * @param saveName
 */
const openDownloadDialog = (url, saveName) => {
    if (typeof url == 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
    }
    let aLink = document.createElement('a');
    aLink.href = url;
    aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
    let event;
    if (window.MouseEvent) event = new MouseEvent('click');
    else {
        event = document.createEvent('MouseEvents');
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
    }
    aLink.dispatchEvent(event);
}
/**
 * 处理样式
 * @param wb
 */
const handleExcelStyleDefault = (wb, cellStyle, headerStyle, maxLineName) => {
    let maxLineIndex = excleLineNameToLineIndex(maxLineName);
    for (let i = 0; i < maxLineIndex; i++) {
        wb["!cols"][i] = headerStyle.default
    }
    for (let specialHeader of headerStyle.specialHeader) {
        wb["!cols"][specialHeader.index] = specialHeader.style;
    }
    for (const key in wb) {
        if (key.indexOf('!') === -1) {
            //列号
            let lineName = key.match(/[a-z,A-Z]/g)[0];
            if (excleLineNameToLineIndex(lineName) > maxLineIndex) {
                continue;
            }
            if (typeof wb[key].v === 'string' && !!cellStyle.specialCell[wb[key].v]) {
                wb[key].s = cellStyle.specialCell[wb[key].v];
            } else {
                wb[key].s = JSON.parse(JSON.stringify(cellStyle.default));
            }
        }
    }
    return wb;
}
/**
 * 24进制的表格列头名转数字
 * @param name
 * @returns {number}
 */
const excleLineNameToLineIndex = (name) => {
    let res = 0;
    for (let i = 0; i < name.length; i++) {
        let letter = name.charAt(i).toUpperCase();
        res += (ALL_LETTER.indexOf(letter) + 1) * Math.pow(24, i);
    }
    return res;
}
/**
 * 检查参数
 * @param cellStyle
 * @param headerStyle
 */
const checkExportExcelParam = (cellStyle, headerStyle) => {
    if (!headerStyle) {
        headerStyle = {};
    }
    if (!headerStyle.default) {
        headerStyle.default = [];
    }
    if (!headerStyle.default.with) {
        headerStyle.default.with = DEFAULT_HEADER_WITH;
    }
    if (!cellStyle) {
        cellStyle = {}
    }
    if (!cellStyle.default) {
        cellStyle.default = [];
    }
    if (!cellStyle.default.font) {
        cellStyle.default.font = {
            sz: 13,
            bold: false,
            color: {
                rgb: '000000'//十六进制，不带#
            }
        }
    }
    if (!cellStyle.default.alignment) {
        cellStyle.default.alignment = {
            horizontal: 'center',
            vertical: 'center',
            wrap_text: true
        }
    }
    if (!cellStyle.default.border) {
        cellStyle.default.border = {
            top: {style: 'thin'},
            bottom: {style: 'thin'},
            left: {style: 'thin'},
            right: {style: 'thin'}
        }
    }
}
/**
 *
 * @param tableId  页面指定table的id值
 * @param cellStyle 单元格样式
 * @param headerStyle  表头样式
 *    {
 *        //默认列头
          default: {with:210},
          //特殊列设置
          specialHeader: [{
                  index: 3,
                  with : 300
              }
          ]
      }
 */
const exportExcel = (tableId, maxLineName, cellStyle, fileName, headerStyle, handleExcelStyle) => {
    //检查参数传递
    checkExportExcelParam(cellStyle, headerStyle);
    // 从表生成工作簿对象
    console.log(XLSX);
    let wb = XLSX.utils.table_to_sheet(document.querySelector(`#${tableId}`), {raw: true});
    //处理样式
    if (!!handleExcelStyle) {
        wb = handleExcelStyle(wb);
    } else {
        wb = handleExcelStyleDefault(wb, cellStyle, headerStyle, maxLineName);
    }
    //为合并项添加边框
    wb = addRangeBorder(wb['!merges'], wb)
    //去除最后的行合并
    // wb = removeLastSumRow(wb);
    //转换为二进制
    wb = sheet2blob(wb);
    //导出
    openDownloadDialog(wb, fileName);
}
/**
 * 表格同类型值合并--表格数据处理
 * @param data
 * @param isH
 * @returns {{}}
 */
const dataMerge = {
    //数据处理
    dataHandle: (data, mergeFieldArr) => {
        // 表格单元格合并多列
        let spanObj = [],
            pos = [];
        for (let index in data) {
            let rowObject = data[index];
            //循环数据内对象，查看有多少key
            for (let rowObjectKey in rowObject) {
                //如果只有一条数据时默认为1即可，无需合并
                if (parseInt(index) === 0) {
                    spanObj[rowObjectKey] = [1];
                    pos[rowObjectKey] = 0;
                } else {
                    let [currentRow, lastRow] = [rowObject, data[index - 1]];
                    //判断当前key是否需要判断合并
                    let filedArr = dataMerge.getMargeFileldArr(rowObjectKey, mergeFieldArr);
                    if (lastRow
                        && filedArr.length > 0
                        && dataMerge.isAllFiledSame(filedArr, currentRow, lastRow)) {
                        //如果上一级和当前一级相当，数组就加1 数组后面就添加一个0
                        spanObj[rowObjectKey][pos[rowObjectKey]] += 1;
                        spanObj[rowObjectKey].push(0)
                    } else {
                        spanObj[rowObjectKey].push(1);
                        pos[rowObjectKey] = index;
                    }
                }
            }
        }
        return spanObj;
    },
    getMargeFileldArr(key, arr) {
        for (let margeFieldStr of arr) {
            let margeFieldArr = margeFieldStr.split(",");
            if (margeFieldArr.includes(key)) {
                return margeFieldArr;
            }
        }
        return [];
    },
    //el-table->span-method
    arraySpanMethod: ({row, column, rowIndex, columnIndex}, spanObj) => {
        // console.log({ row, column, rowIndex, columnIndex },'合并表格')
        //列合并
        let _row = spanObj[column.property] ? spanObj[column.property][rowIndex] : 1;
        let _col = _row > 0 ? 1 : 0;
        return {
            rowspan: _row,
            colspan: _col
        }
    },
    isAllFiledSame(filedArr, currentRow, lastRow) {
        for (let field of filedArr) {
            // let field = filedArr[i];
            if (currentRow[field] !== lastRow[field]) {
                return false
            }
        }
        return true;
    }
};


export default {
    exportExcel,
    dataMerge
}

工具使用实例

html代码块

点击查看代码

  <div :style="staticPageStyle">
    <el-row>
      <el-form :inline="true" :model="condition" size="mini" class="demo-form-inline">
        <el-button size="mini" type="primary" @click="exportExcel()" style="margin-left: 10px">导出excel</el-button>
      </el-form>
    </el-row>
    <el-table
        id="nscjbh-staticTable"
        :data="tableData"
        border
        sum-text="合计"
        show-summary
        :span-method="spanMethod"
        :summary-method="getSummaries"
        v-loading="tableLoading"
        style="width: 100%;border: 0">
      <el-table-column
          align="center"
          header-align="center"
          :show-overflow-tooltip=true
          :label="`${condition.year}计划表`">
        <el-table-column
            prop="项目性质"
            width="240"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="项目性质">
        </el-table-column>
        <el-table-column
            prop="区域"
            width="220"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="区域">
        </el-table-column>
        <el-table-column
            prop="项目面积(亩)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="项目面积(亩)">
        </el-table-column>
        <el-table-column
            prop="项目个数"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="项目个数">
        </el-table-column>
        <el-table-column
            prop="年度资金预算(万元)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="年度资金预算(万元)">
        </el-table-column>
        <el-table-column
            prop="计划完成拆迁面积"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="计划完成拆迁面积">
        </el-table-column>
        <el-table-column
            prop="计划形成可供经营性用地(亩)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="计划形成可供经营性用地(亩)">
        </el-table-column>
        <el-table-column
            prop="计划形成可供非经营性用地(亩)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="计划形成可供非经营性用地(亩)">
        </el-table-column>
      </el-table-column>
    </el-table>
  </div>

导出代码

点击查看代码

      //导出表id
      let tableId = 'nscjbh-staticTable';
      //单元格样式  样式的文档地址  https://www.npmjs.com/package/xlsx-style
      let cellStyle = {
        default: {
          font: {
            sz: 13,
            bold: false,
            color: {
              rgb: '000000'//十六进制，不带#
            }
          },
          alignment: {
            horizontal: 'center',
            vertical: 'center',
            wrap_text: true
          },
          border: {
            top: {style: 'thin'},
            bottom: {style: 'thin'},
            left: {style: 'thin'},
            right: {style: 'thin'}
          },
        },
        specialCell: {}
      };
      //导出表名
      let fileName = `计划表(${(new Date()).toDateString()}).xlsx`;
      //头部样式
      let headerStyle = {
        default: {
          wpx: 220
        },
        specialHeader: [{
          index: 2,
          style: {
            wpx: 320
          }
        }]
      };
      //自定义样式处理方法(按需求,可以不传)   
      let handleExcelStyle = (wb, cellStyle, headerStyle) => {
      };
      //列表最大列号 从1开始算
      let maxLineName = 'H';
      exportExcelUtil.exportExcel(tableId, maxLineName, cellStyle, fileName, headerStyle);
	  //自定义样式处理
	  //exportExcelUtil.exportExcel(tableId, null, null, fileName, null,handleExcelStyle);

其他功能

尾部求和

参考官方文档: https://element.eleme.io/#/zh-CN/component/table

同字段同值单元格合并

处理表格数据
其中参数1表示表格数据,参数2表示要合并单元格的字段数组

//如果需要判定在多个字段相同的情况下合并参数二一个元素加逗号隔开多个字段如['A1','F2','C2']
this.spanObj = exportExcelUtil.dataMerge.dataHandle(this.tableData, ['项目性质']);

自定义element-ui合并单元格方法

<el-table  :span-method="spanMethod">

合并方法

  spanMethod(param) {
      return exportExcelUtil.dataMerge.arraySpanMethod(param, this.spanObj);
    }

完整vue模块实例

点击查看代码

<template>
  <div :style="staticPageStyle">
    <el-row>
      <el-form :inline="true" :model="condition" size="mini" class="demo-form-inline">
        <el-button size="mini" type="primary" @click="exportExcel()" style="margin-left: 10px">导出excel</el-button>
      </el-form>
    </el-row>
    <el-table
        id="nscjbh-staticTable"
        :data="tableData"
        border
        sum-text="合计"
        show-summary
        :span-method="spanMethod"
        :summary-method="getSummaries"
        v-loading="tableLoading"
        style="width: 100%;border: 0">
      <el-table-column
          align="center"
          header-align="center"
          :show-overflow-tooltip=true
          :label="`${condition.year}计划表`">
        <el-table-column
            prop="项目性质"
            width="240"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="项目性质">
        </el-table-column>
        <el-table-column
            prop="区域"
            width="220"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="区域">
        </el-table-column>
        <el-table-column
            prop="项目面积(亩)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="项目面积(亩)">
        </el-table-column>
        <el-table-column
            prop="项目个数"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="项目个数">
        </el-table-column>
        <el-table-column
            prop="年度资金预算(万元)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="年度资金预算(万元)">
        </el-table-column>
        <el-table-column
            prop="计划完成拆迁面积"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="计划完成拆迁面积">
        </el-table-column>
        <el-table-column
            prop="计划形成可供经营性用地(亩)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="计划形成可供经营性用地(亩)">
        </el-table-column>
        <el-table-column
            prop="计划形成可供非经营性用地(亩)"
            align="center"
            header-align="center"
            :show-overflow-tooltip=true
            label="计划形成可供非经营性用地(亩)">
        </el-table-column>
      </el-table-column>
    </el-table>
  </div>
</template>

<script scoped>

import exportExcelUtil from "@/global/exportExcelUtil";

export default {
  name: "testTable",
  props: {},
  components: {},
  computed: {},
  data() {
    return {
      staticPageStyle: {
        height: (window.innerHeight - 107) + 'px'
      },
      tableLoading: false,
      tableData: [{
        "SORT": "1",
        "区域": "锦江区",
        "年度资金预算(万元)": "5993.3",
        "计划完成拆迁面积": "0",
        "计划形成可供经营性用地(亩)": "0",
        "计划形成可供非经营性用地(亩)": "0",
        "项目个数": "8",
        "项目性质": "完结项目",
        "项目面积(亩)": "3221.27"
      }, {
        "SORT": "1",
        "区域": "青羊区",
        "年度资金预算(万元)": "1",
        "计划完成拆迁面积": "0",
        "计划形成可供经营性用地(亩)": "0",
        "计划形成可供非经营性用地(亩)": "0",
        "项目个数": "1",
        "项目性质": "完结项目",
        "项目面积(亩)": "13"
      }, {
        "项目性质": "完结项目",
        "区域": "金牛区",
        "计划形成可供经营性用地(亩)": 0,
        "项目面积(亩)": 0,
        "项目个数": 0,
        "计划完成拆迁面积": 0,
        "年度资金预算(万元)": 0,
        "计划形成可供非经营性用地(亩)": 0,
        "SORT": -1
      }, {
        "SORT": "1",
        "区域": "成华区",
        "年度资金预算(万元)": "426",
        "计划完成拆迁面积": "0",
        "计划形成可供经营性用地(亩)": "0",
        "计划形成可供非经营性用地(亩)": "0",
        "项目个数": "1",
        "项目性质": "完结项目",
        "项目面积(亩)": "237"
      }, {
        "项目性质": "完结项目",
        "区域": "合计",
        "计划形成可供经营性用地(亩)": 0,
        "项目面积(亩)": 3301,
        "项目个数": 10,
        "计划完成拆迁面积": 0,
        "年度资金预算(万元)": 1020,
        "计划形成可供非经营性用地(亩)": 0,
        "SORT": -1
      }, {
        "SORT": "2",
        "区域": "锦江区",
        "年度资金预算(万元)": "0",
        "计划完成拆迁面积": "0",
        "计划形成可供经营性用地(亩)": "0",
        "计划形成可供非经营性用地(亩)": "0",
        "项目个数": "1",
        "项目性质": "新增项目",
        "项目面积(亩)": "10"
      }, {
        "项目性质": "新增项目",
        "区域": "青羊区",
        "计划形成可供经营性用地(亩)": 0,
        "项目面积(亩)": 0,
        "项目个数": 0,
        "计划完成拆迁面积": 0,
        "年度资金预算(万元)": 0,
        "计划形成可供非经营性用地(亩)": 0,
        "SORT": -1
      }, {
        "项目性质": "新增项目",
        "区域": "金牛区",
        "计划形成可供经营性用地(亩)": 0,
        "项目面积(亩)": 0,
        "项目个数": 0,
        "计划完成拆迁面积": 0,
        "年度资金预算(万元)": 0,
        "计划形成可供非经营性用地(亩)": 0,
        "SORT": -1
      }, {
        "项目性质": "新增项目",
        "区域": "成华区",
        "计划形成可供经营性用地(亩)": 0,
        "项目面积(亩)": 0,
        "项目个数": 0,
        "计划完成拆迁面积": 0,
        "年度资金预算(万元)": 0,
        "计划形成可供非经营性用地(亩)": 0,
        "SORT": -1
      }, {
        "项目性质": "新增项目",
        "区域": "合计",
        "计划形成可供经营性用地(亩)": 0,
        "项目面积(亩)": 100,
        "项目个数": 1,
        "计划完成拆迁面积": 0,
        "年度资金预算(万元)": 0,
        "计划形成可供非经营性用地(亩)": 0,
        "SORT": -1
      }],
      yearItems: [],
      condition: {
        year: 1996
      },
      spanObj: [],
    }
  },
  methods: {
    spanMethod(param) {
      return exportExcelUtil.dataMerge.arraySpanMethod(param, this.spanObj);
    },
    handleExcelStyle(wb) {
      for (let i = 0; i < 11; i++) {
        wb["!cols"][i] = {wpx: 130}
      }
      //项目性质
      wb["!cols"][0] = {wpx: 220}
      //区域
      wb["!cols"][1] = {wpx: 220}
      //计划形成可供经营性用地(亩)
      wb["!cols"][6] = {wpx: 250}
      //计划形成可供非经营性用地(亩)
      wb["!cols"][7] = {wpx: 260}
      // 样式的文档地址
      // https://www.npmjs.com/package/xlsx-style
      for (const key in wb) {
        if (key.indexOf('!') === -1) {
          //特殊处理 数据有超出列不加边框
          if (key.indexOf("I") >= 0) {
            continue;
          }
          let font = {
            sz: 13,
            bold: false,
            color: {
              rgb: '000000'//十六进制，不带#
            }
          }
          if (wb[key].v.indexOf('年储备土地拟收储计划表') > 0) {
            font = {
              sz: 20,
              bold: false,
              color: {
                rgb: '000000'//十六进制，不带#
              }
            }
          }
          wb[key].s = {
            //字体设置
            font: font,
            alignment: {//文字居中
              horizontal: 'center',
              vertical: 'center',
              wrap_text: true
            },
            border: { // 设置边框
              top: {style: 'thin'},
              bottom: {style: 'thin'},
              left: {style: 'thin'},
              right: {style: 'thin'}
            }
          }
        }
      }
      return wb;
    },
    exportExcel() {
      //导出表id
      let tableId = 'nscjbh-staticTable';
      //单元格样式
      let cellStyle = {
        default: {
          font: {
            sz: 13,
            bold: false,
            color: {
              rgb: '000000'//十六进制，不带#
            }
          },
          alignment: {
            horizontal: 'center',
            vertical: 'center',
            wrap_text: true
          },
          border: {
            top: {style: 'thin'},
            bottom: {style: 'thin'},
            left: {style: 'thin'},
            right: {style: 'thin'}
          },
        },
        specialCell: {}
      };
      //导出表名
      let fileName = `计划表(${(new Date()).toDateString()}).xlsx`;
      //头部样式
      let headerStyle = {
        default: {
          wpx: 220
        },
        specialHeader: [{
          index: 2,
          style: {
            wpx: 320
          }
        }]
      };
      //自定义样式处理方法(按需求,可以不传)    样式的文档地址  https://www.npmjs.com/package/xlsx-style
      let handleExcelStyle = (wb, cellStyle, headerStyle) => {
      };
      //列表最大列号 从1开始算
      let maxLineName = 'H';
      exportExcelUtil.exportExcel(tableId, maxLineName, cellStyle, fileName, headerStyle);
    },
    getSummaries(param) {
      const {columns, data} = param;
      const sums = [];
      let ignoreIndesItems = [0, 1];
      columns.forEach((column, index) => {
        if (column.label === '项目性质') {
          sums[index] = '合计';
          return;
        }
        if (ignoreIndesItems.indexOf(index) >= 0) {
          sums[index] = '/';
          return;
        }
        const values = data.map(item => Number(item[column.property]));
        if (!values.every(value => isNaN(value))) {
          sums[index] = values.reduce((prev, curr) => {
            const value = Number(curr);
            if (!isNaN(value)) {
              return prev + curr;
            } else {
              return prev;
            }
          }, 0);
        } else {
          sums[index] = 'N/A';
        }
      });
      return sums;
    },
  },
  mounted() {
  },
  created() {
    this.spanObj = exportExcelUtil.dataMerge.dataHandle(this.tableData, ['项目性质']);
    console.log(this.spanObj);
  }
}
</script>

<style scoped>

</style>