前言

记录使用EasyExcel导出Excel合并多行数据

一、话不多说上代码

代码如下：这是比较初始版本的、没做优化，如有大佬多多提点

package com.ffcs.oss.config;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class ExcelFileCellsMerge implements CellWriteHandler {


    /**
     * 用第几行开始合并 ，默认为1，因为第0行是标题，EasyExcel 的默认也是
     */
    private int mergeRowIndex = 1;

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }

    /*
     * 在对单元格的所有操作完成后调用
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list,
                                 Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            // 前五列需要合并 所以需要读取到第五列
            if (curColIndex == 5) {
                mergeWithPrevRow(writeSheetHolder, cell, curRowIndex);
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Cell curCell = cell.getSheet().getRow(curRowIndex).getCell(0);
        Cell curCell1 = cell.getSheet().getRow(curRowIndex).getCell(1);
        Cell curCell2 = cell.getSheet().getRow(curRowIndex).getCell(2);
        Cell curCell3 = cell.getSheet().getRow(curRowIndex).getCell(3);
        Cell curCell4 = cell.getSheet().getRow(curRowIndex).getCell(4);
        Cell curCell5 = cell.getSheet().getRow(curRowIndex).getCell(5);
        Object curData = curCell.getCellTypeEnum() == CellType.STRING ? curCell.getStringCellValue() : curCell.getNumericCellValue();
        Object curData1 = curCell1.getCellTypeEnum() == CellType.STRING ? curCell1.getStringCellValue() : curCell1.getNumericCellValue();
        Object curData2 = curCell2.getCellTypeEnum() == CellType.STRING ? curCell2.getStringCellValue() : curCell2.getNumericCellValue();
        Object curData3 = curCell3.getCellTypeEnum() == CellType.STRING ? curCell3.getStringCellValue() : curCell3.getNumericCellValue();
        Object curData4 = curCell4.getCellTypeEnum() == CellType.STRING ? curCell4.getStringCellValue() : curCell4.getNumericCellValue();
        Object curData5 = curCell5.getCellTypeEnum() == CellType.STRING ? curCell5.getStringCellValue() : curCell5.getNumericCellValue();
        // 获取上一行数据
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
        Cell preCell1 = cell.getSheet().getRow(curRowIndex - 1).getCell(1);
        Cell preCell2 = cell.getSheet().getRow(curRowIndex - 1).getCell(2);
        Cell preCell3 = cell.getSheet().getRow(curRowIndex - 1).getCell(3);
        Cell preCell4 = cell.getSheet().getRow(curRowIndex - 1).getCell(4);
        Cell preCell5 = cell.getSheet().getRow(curRowIndex - 1).getCell(5);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        Object preData1 = preCell1.getCellTypeEnum() == CellType.STRING ? preCell1.getStringCellValue() : preCell1.getNumericCellValue();
        Object preData2 = preCell2.getCellTypeEnum() == CellType.STRING ? preCell2.getStringCellValue() : preCell2.getNumericCellValue();
        Object preData3 = preCell3.getCellTypeEnum() == CellType.STRING ? preCell3.getStringCellValue() : preCell3.getNumericCellValue();
        Object preData4 = preCell4.getCellTypeEnum() == CellType.STRING ? preCell4.getStringCellValue() : preCell4.getNumericCellValue();
        Object preData5 = preCell5.getCellTypeEnum() == CellType.STRING ? preCell5.getStringCellValue() : preCell5.getNumericCellValue();
        String curString = String.valueOf(curData) + curData1 + curData2 + curData3 + curData4 + curData5;
        String preString = String.valueOf(preData) + preData1 + preData2 + preData3 + preData4 + preData5;

        // 比较当前行的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curString.equals(preString)) {
            Sheet sheet = writeSheetHolder.getSheet();
            // 获取合并信息
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            // 下标
            int index = 0;
            for (int i = 0; i < mergeRegions.size(); i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, index)) {
                    sheet.removeMergedRegion(mergeRegions.size() - 6);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
                index++;
                // 这个是第几列
                if (index > 5) {
                    index = 0;
                }
            }
            if (!isMerged) {
                for (int i = 0; i <= 5; i++) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, i, i);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }
    }

}

2.导出使用

代码如下：

	List<ExportExcel> list =new ArrayList<>();
	response.setContentType("application/vnd.ms-excel");
	response.setCharacterEncoding("utf-8");
	// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
	String fileName = URLEncoder.encode("Excel文件名", "utf-8").replaceAll("\\+", "%20");
	response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
	EasyExcel.write(response.getOutputStream(), ExportExcel.class)
	.registerWriteHandler(new ExcelFileCellsMerge())
	.sheet(0, "sheet页名称").doWrite(list);

3、导出效果如下

总结

找了很久没找到合并多行表格的代码，这是我根据查找的样例、结合自身项目需求进行修改而来的。使用范围有所限制，当然有大佬可以进行优化一下。万分感谢。

自定义方法： 

/**
     * datagrid 自定义合并单元格
     * @param $this 表格选择器
     * @param field_arr 合并列字段
     * @param judge 值不相同不合并
     */
    customMergeCells: function($this, field_arr, judge) {
        var rows = $this.datagrid("getRows");
        if ( (typeof (field_arr) === "undefined" || field_arr === "" || field_arr == null || field_arr === "null")
            || (typeof (field_arr) === "undefined" || field_arr === "" || field_arr == null || field_arr === "null")) {
            return;
        }
        for (var i = 1; i < rows.length; i++) {
            for (var k = 0; k < field_arr.length; k++) {
                var field = field_arr[k]; // 要排序的字段
                if(rows[i][field] === rows[i-1][field]){ // 相邻的上下两行
                    if(!(typeof (judge) === "undefined" || judge === "" || judge == null || judge === "null")){
                        if(rows[i][judge] !== rows[i-1][judge]){
                            continue;
                        }
                    }
                    var rowspan = 2;
                    for (var j = 2; i-j >= 0; j++) { // 判断上下多行内容一样
                        if(rows[i][field] !== rows[i-j][field]){
                            break;
                        }else{
                            if(!(typeof (judge) === "undefined" || judge === "" || judge == null || judge === "null")){
                                if(rows[i][judge] !== rows[i-j][judge]){
                                    break;
                                }
                            }
                            rowspan = j+1;
                        }
                    }
                    $this.datagrid("mergeCells",{ // 合并
                        index: i-rowspan+1,
                        field: field,
                        rowspan: rowspan
                    });
                }
            }
        }
    }

使用实例： 

onLoadSuccess: function(data) {
                    if (data.rows && data.rows.length > 0) {
                        // 合并单元格
                        top.$.tools.customMergeCells($(this), ["itemName", "developUnitName"], "itemName");
                    }
                },

效果图：

参考来之https://blog.csdn.net/weixin_38516688/article/details/86021359

主要代码

    merageInit() { // 在下文的时候会用到，对数据进行初始化是很有必要的
      this.paragraph_merageArr = [];
      this.paragraph_meragePos = 0;
      this.project_merageArr = [];
      this.project_meragePos = 0;
      this.branch_merageArr = [];
      this.branch_meragePos = 0;
    },
    // 合并多列专用
    merage() {
      this.merageInit(); // 前文的初始化数据函数
      for (let i = 0; i < this.tableData.length; i++) {
        if (i === 0) {
          // 第一行必须存在
          this.paragraph_merageArr.push(1);
          this.paragraph_meragePos = 0;
          this.project_merageArr.push(1);
          this.project_meragePos = 0;
          this.branch_merageArr.push(1);
          this.branch_meragePos = 0;
        } else {
          // 判断当前元素与上一个元素是否相同,eg：this.paragraph_meragePos 是 this.typeNameArr序号
          // 第一列
          if (this.tableData[i].paragraph === this.tableData[i - 1].paragraph) {
            this.paragraph_merageArr[this.paragraph_meragePos] += 1;
            this.paragraph_merageArr.push(0);
          } else {
            this.paragraph_merageArr.push(1);
            this.paragraph_meragePos = i;
          }
          // 第二列
          if (this.tableData[i].project === this.tableData[i - 1].project && this.tableData[i].paragraph === this.tableData[i - 1].paragraph) {
            this.project_merageArr[this.project_meragePos] += 1;
            this.project_merageArr.push(0);
          } else {
            this.project_merageArr.push(1);
            this.project_meragePos = i;
          }
          // 第三列
          if (this.tableData[i].branch === this.tableData[i - 1].branch && this.tableData[i].project === this.tableData[i - 1].project && this.tableData[i].paragraph === this.tableData[i - 1].paragraph) {
            this.branch_merageArr[this.branch_meragePos] += 1;
            this.branch_merageArr.push(0);
          } else {
            this.branch_merageArr.push(1);
            this.branch_meragePos = i;
          }
        }
      }
    },
    // 合并专用
    objectSpanMethod({ row, column, rowIndex, columnIndex }) {
      if (columnIndex === 0) {
        // 第一列的合并方法
        const row1 = this.paragraph_merageArr[rowIndex];
        const col1 = row1 > 0 ? 1 : 0; // 如果被合并了row = 0; 则他这个列需要取消
        return {
          rowspan: row1,
          colspan: col1,
        };
      } else if (columnIndex === 1) {
        // 第二列的合并方法
        const row2 = this.project_merageArr[rowIndex];
        const col2 = row2 > 0 ? 1 : 0; // 如果被合并了row = 0; 则他这个列需要取消
        return {
          rowspan: row2,
          colspan: col2,
        };
      } else if (columnIndex === 2) {
        // 第三列的合并方法
        const row3 = this.branch_merageArr[rowIndex];
        const col3 = row3 > 0 ? 1 : 0; // 如果被合并了row = 0; 则他这个列需要取消
        return {
          rowspan: row3,
          colspan: col3,
        };
      }
    },

完整代码 vue单文件

summary-report.vue

<!-- 检测汇报->汇总 -->
<template lang="pug">
.summary1
  .title_exportBox(style="display: flex")
    .title 汇总表
  .boxTable
    el-table(
      :data="tableData",
      border,
      height="calc(100vh - 285px)",
      :header-cell-style="{ background: '#fff', color: '#555555', fontSize: '16px' }",
      :span-method="objectSpanMethod"
      v-loading="tableLoading"
    )
      el-table-column(v-if="paragraph_show === '1'" prop="paragraph", label="标段", align="center")
      el-table-column(prop="project", label="单位工程", align="center")
      el-table-column(prop="branch", label="分部工程", align="center")
      el-table-column(prop="name", label="检测项目", align="center")
      el-table-column(prop="constructionNum", label="施工完成数量", align="center")
      el-table-column(prop="inspectionNum", label="报检数量", align="center")
</template>

<script>
import { mapGetters } from "vuex";
export default {
  name: "summary-report",
  components: {},
  props: {
  },
  data() {
    return {
      exportParams: {},
      tableLoading: false,
      pos: 0,
      spanArr: [],
      page_export:0,

      
      tableData: [ // 假数据
        {
          id: 1,
          paragraph: 'TJ01',
          project: '单位工程',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 2,
          paragraph: 'TJ01',
          project: '单位工程',
          branch: '分部工程',
          billing: '月/季/年制度',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 3,
          paragraph: 'TJ01',
          project: '单位工程1',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 4,
          paragraph: 'TJ01',
          project: '单位工程2',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 5,
          paragraph: 'TJ02',
          project: '单位工程2',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 6,
          paragraph: 'TJ02',
          project: '单位工程1',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 7,
          paragraph: 'TJ02',
          project: '单位工程1',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
        {
          id: 8,
          paragraph: 'TJ02',
          project: '单位工程',
          branch: '分部工程',
          name: '--',
          constructionNum: '--',
          inspectionNum: '--',
        },
      ],
      paragraph_merageArr: [],  // 第一列进行合并操作时存放的数组变量
      paragraph_meragePos: 0, // 上面的数组的下标值
      project_merageArr: [],  // 第二列进行合并操作时存放的数组变量
      project_meragePos: 0,// 上面的数组的下标值
      branch_merageArr: [], // 第三列进行合并操作时存放的数组变量
      branch_meragePos: 0,// 上面的数组的下标值
    };
  },
  computed: { ...mapGetters(["account_searchForm","inspectionDetection",'paragraph_show']) },
  watch: {
  },
  methods: {
    merageInit() { // 在下文的时候会用到，对数据进行初始化是很有必要的
      this.paragraph_merageArr = [];
      this.paragraph_meragePos = 0;
      this.project_merageArr = [];
      this.project_meragePos = 0;
      this.branch_merageArr = [];
      this.branch_meragePos = 0;
    },
    // 合并多列专用
    merage() {
      this.merageInit(); // 前文的初始化数据函数
      for (let i = 0; i < this.tableData.length; i++) {
        if (i === 0) {
          // 第一行必须存在
          this.paragraph_merageArr.push(1);
          this.paragraph_meragePos = 0;
          this.project_merageArr.push(1);
          this.project_meragePos = 0;
          this.branch_merageArr.push(1);
          this.branch_meragePos = 0;
        } else {
          // 判断当前元素与上一个元素是否相同,eg：this.paragraph_meragePos 是 this.typeNameArr序号
          // 第一列
          if (this.tableData[i].paragraph === this.tableData[i - 1].paragraph) {
            this.paragraph_merageArr[this.paragraph_meragePos] += 1;
            this.paragraph_merageArr.push(0);
          } else {
            this.paragraph_merageArr.push(1);
            this.paragraph_meragePos = i;
          }
          // 第二列
          if (this.tableData[i].project === this.tableData[i - 1].project && this.tableData[i].paragraph === this.tableData[i - 1].paragraph) {
            this.project_merageArr[this.project_meragePos] += 1;
            this.project_merageArr.push(0);
          } else {
            this.project_merageArr.push(1);
            this.project_meragePos = i;
          }
          // 第三列
          if (this.tableData[i].branch === this.tableData[i - 1].branch && this.tableData[i].project === this.tableData[i - 1].project && this.tableData[i].paragraph === this.tableData[i - 1].paragraph) {
            this.branch_merageArr[this.branch_meragePos] += 1;
            this.branch_merageArr.push(0);
          } else {
            this.branch_merageArr.push(1);
            this.branch_meragePos = i;
          }
        }
      }
    },
    // 合并专用
    objectSpanMethod({ row, column, rowIndex, columnIndex }) {
      if (columnIndex === 0) {
        // 第一列的合并方法
        const row1 = this.paragraph_merageArr[rowIndex];
        const col1 = row1 > 0 ? 1 : 0; // 如果被合并了row = 0; 则他这个列需要取消
        return {
          rowspan: row1,
          colspan: col1,
        };
      } else if (columnIndex === 1) {
        // 第二列的合并方法
        const row2 = this.project_merageArr[rowIndex];
        const col2 = row2 > 0 ? 1 : 0; // 如果被合并了row = 0; 则他这个列需要取消
        return {
          rowspan: row2,
          colspan: col2,
        };
      } else if (columnIndex === 2) {
        // 第三列的合并方法
        const row3 = this.branch_merageArr[rowIndex];
        const col3 = row3 > 0 ? 1 : 0; // 如果被合并了row = 0; 则他这个列需要取消
        return {
          rowspan: row3,
          colspan: col3,
        };
      }
    },
  },
  created() {
    this.page_export = this.inspectionDetection.record_account.page_export;
  },
  mounted() {
    this.merage()
  }
};
</script>
<style lang="scss" scoped>
.title_exportBox {
  .title {
    width: calc(100% - 56px);
    font-size: 20px;
    font-weight: bold;
    text-align: center;
    line-height: 34px;
    text-align: center;
  }
}
</style>