ExcelJS 合并表头,合并单元格,一sheet多表格

 导出效果

 

 代码部分

<template>
  <el-card>
    <div>
      <el-button type="primary" style="margin: 20px 0" @click="handleExcel">导出</el-button>
        <el-table 
        :data="tableData" 
        border 
        :header-cell-style = " {'text-align':'center' , 'color' : 'black'}"
        :cell-style = " {'text-align':'center'}"
        :span-method="objectSpanMethod"
        header-cell-class-name="headerBg" 
        style="display: inline-block;margin-right: 30px">
            <!-- <el-table-column type="index" :index="indexMethod"> -->
            <el-table-column prop="name" label="姓名"></el-table-column>
            <el-table-column prop="auditFee" label="审核费"></el-table-column>
            <el-table-column prop="basicSalary" label="底薪"></el-table-column>
            <el-table-column prop="communicationFee" label="通讯费"></el-table-column>
            <el-table-column prop="total" label="合计"></el-table-column>
            <el-table-column prop="IDCard" label="身份证"></el-table-column>
            <el-table-column prop="bankCard" label="银行卡号"></el-table-column>
        </el-table>
        <el-pagination
          background
          @size-change="sizeChange"
          @current-change="currentChange"
          :page-sizes="[10, 20, 30, 40, 50, 100]"
          :page-size="page.pageSize"
          :current-page="page.currentPage"
          layout="->, total, sizes, prev, pager, next, jumper"
          :total="page.total"
          style="padding: 20px 0">
        </el-pagination>
    </div>
  </el-card>
</template>

<script>
import * as ExcelJS from 'exceljs/dist/exceljs';
import FileSaver from 'file-saver';
export default {
data() {
        return {
          page: {
          pageSize: 10,
          currentPage: 1,
          total: 4,
        },
        spanArr:[],
            tableData: [
                {
                    name: '小明1',
                    auditFee: 321,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 3455366,
                    bankCard:12332211111
                },
                {
                    name: '小明1',
                    auditFee: 133,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 62131236,
                    bankCard:12332234
                },
                {
                    name: '小明1',
                    auditFee: 133,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 61321316,
                    bankCard:12332234
                },
                {
                    name: '小绿1',
                    auditFee: 13,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 613213236,
                    bankCard:12332234
                },
            ],
            tableData2: [
                {
                    name: '小明2',
                    auditFee: 123,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 6653453534,
                    bankCard:12332234
                },
                {
                    name: '小红2',
                    auditFee: 13,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 635345356,
                    bankCard:12332234
                },
                {
                    name: '小红2',
                    auditFee: 321,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 63535356,
                    bankCard:12332234
                },
                {
                    name: '小绿2',
                    auditFee: 21,
                    basicSalary: 80,
                    communicationFee: 70,
                    total: 60,
                    IDCard: 63553536,
                    bankCard:12332234
                },
            ],
        }
  },
  methods: {
      handleExcel() {
        let columns = [                
                { name: '姓名' },
                { name: '审核费' },
                { name: '底薪' },
                { name: '通讯费' },
                { name: '合计' },
                { name: '身份证' },
                { name: '银行卡号'}]
        // 创建工作簿
        const workbook = new ExcelJS.Workbook();
        workbook.creator = 'Me';
        workbook.lastModifiedBy = 'Her';
        workbook.created = new Date();
        workbook.modified = new Date();
        // 将工作簿日期设置为 1904 年日期系统
        workbook.properties.date1904 = true;

        // 添加sheet页
        const worksheet = workbook.addWorksheet('审核费统计表');
        worksheet.properties.defaultColWidth = 16; // 默认列宽
        worksheet.properties.defaultRowHeight = 16; // 默认行高
        let rowCount = 1;   //记录单元格被使用的长度
        const step = 2;

        // 全职
        const collectRow = worksheet.getRow(rowCount); // 获取单元格内第一行
        collectRow.height = 30;
        const collectcell = worksheet.getCell(`A1`);
        worksheet.mergeCells(`A1:H1`);  // 合并单元格，将A1与H1合并
        collectcell.value = 'XXX年XX月审核费统计表'; // 表格标题内容，A1与H1合并后填充的内容
        collectcell.font = { name: '宋体', family: 4, size: 14, bold: true }; // 字体
        collectcell.alignment = { vertical: 'middle', horizontal: 'center' };
        //次级表头
        const collectcell2 = worksheet.getCell(`A2`);
        worksheet.mergeCells(`A2:H2`);
        collectcell2.value = '全职';
        collectcell2.font = { name: '宋体', family: 4, size: 12, bold: true }; // 字体
        collectcell2.alignment = { vertical: 'middle', horizontal: 'center' };
        rowCount++;

        // 数据处理，写入Excel的数据需要将json数据转换成二维数据的形式
        const ExcelRows = [];   //导入sheet页的表格数据
        this.tableData.forEach((item) => {
            const excelRow = [];
            Object.values(item).forEach(it => { // Object.values获取对象属性按对象定义顺序输出，这里表格内容值需要和表头对应
                excelRow.push(it);
            });
            ExcelRows.push(excelRow);
        });

        // 将sheet页添加到Excel工作簿
        worksheet.addTable({
            name: 'studentScore',
            ref: 'A3',  // 表内容区域
            headerRow: true, // 在表格顶部显示标题
            filterButton: false, // 切换标题中的过滤器控件
            totalsRow: false, // 在表格底部显示总计
            style: {
                theme: ''
            },
            columns: columns, //设置表头，必须和内容对应，顺序不能变
            rows: ExcelRows //表内容
        });
        rowCount += ExcelRows.length + step;

        // 兼职
        const secondRow = worksheet.getRow(rowCount); // 获取单元格内第一行
        secondRow.height = 30;
        const secondCell = worksheet.getCell(`A${rowCount}`);
        worksheet.mergeCells(`A${rowCount}:H${rowCount}`);  // 合并单元格，将A1与H1合并
        secondCell.value = '兼职'; // 表格标题内容，A1与H1合并后填充的内容
        secondCell.font = { name: '宋体', family: 4, size: 12, bold: true }; // 字体
        secondCell.alignment = { vertical: 'middle', horizontal: 'center' };
        rowCount++;

        // 数据处理，写入Excel的数据需要将json数据转换成二维数据的形式
        const secondExcelRows = [];   //导入sheet页的表格数据
        this.tableData2.forEach((item) => {
            const excelRow = [];
            Object.values(item).forEach(it => { // Object.values获取对象属性按对象定义顺序输出，这里表格内容值需要和表头对应
                excelRow.push(it);
            });
            secondExcelRows.push(excelRow);
        });

        // 将sheet页添加到Excel工作簿
        worksheet.addTable({
            name: 'SecondeStudentScore',
            ref: `A${rowCount}`,  // 表内容区域
            headerRow: true, // 在表格顶部显示标题
            filterButton: false, // 切换标题中的过滤器控件
            totalsRow: false, // 在表格底部显示总计
            style: {
                theme: ''
            },
            columns: columns,
            rows: secondExcelRows //表内容
        });
        
        columns.forEach((item, i) => {    
          // 设置样式
          if(item.name=="身份证"||item.name=="银行卡号"){
            worksheet.getColumn(i + 1).width = 30
          }
          worksheet.getColumn(i + 1).alignment = { vertical: 'middle', horizontal: 'center' }
        })
        // 导出Excel文件，这里指定Excel名称
        workbook.xlsx.writeBuffer().then((buffer) => {
            FileSaver.saveAs(
                new Blob([buffer], {
                    type: 'application/octet-stream'
                }),
                `XXX年XX月审核费统计表.xlsx`
            );
        });
      },
      getSpanArr(data) {
        let that = this
        that.spanArr = []
        that.pos = 0
            for (var i = 0; i < data.length; i++) {
              if (i === 0) {
                this.spanArr.push(1);
                this.pos = 0
              } else {
                // 判断当前元素与上一个元素是否相同
              if (data[i].name === data[i - 1].name) {
                  this.spanArr[this.pos] += 1;
                  this.spanArr.push(0);
                } else {
                  this.spanArr.push(1);
                  this.pos = i;
                }
              }
          }
      },
      objectSpanMethod({rowIndex, columnIndex }) {
          if (columnIndex === 0) {
            const _row = this.spanArr[rowIndex];
            const _col = _row > 0 ? 1 : 0;
            return {
                  rowspan: _row,
                  colspan: _col
            }
          }else{
            return false
          }
      },
      currentChange(currentPage) {
        this.page.currentPage = currentPage;
      },
      sizeChange(pageSize) {
        this.page.pageSize = pageSize;
      },
  },
  created() {
    this.getSpanArr(this.tableData)
  }
}
</script>

<style scoped>
.el-card{
  margin: 0 10px;
}
</style>