Java整合EasyExcel实战——3(上下列相同合并单元格策略)

参考：https://juejin.cn/post/7322156759443095561?searchId=202405262043517631094B7CCB463FDA06https://juejin.cn/post/7322156759443095561?searchId=202405262043517631094B7CCB463FDA06

准备条件

依赖

        
            com.alibaba
            easyexcel
            2.2.6
        

工具类

package co.yixiang.exam.listener;
 
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 ExcelFillCellMergeStrategy implements CellWriteHandler {
 
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
 
    public ExcelFillCellMergeStrategy() {
    }
 
    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }
    @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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
 
    }
 
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
 
        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }
 
 
 
    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
 
        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        //
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
 
}

ServiceImpl层应用

sql 数据的重复数据，合并单元格

SELECT
		eq.id,eq.question_title,eo.options_content,eq.options_correct,eq.question_answer,eq.question_score,eq.question_subject,eq.question_title_zi,eq.question_type
FROM
	ex_question eq
LEFT JOIN
  ex_options eo
	on eq.id = eo.question_id where eq.is_del = 0 and eo.is_del = 0;

    @Override
    public R exportExQuestionOptions(HttpServletResponse response) throws IOException {
        // 定义导出的Excel文件名
        String fileName = "test.xlsx";
 
        // 设置响应的内容类型为二进制流，这是文件下载的标准设置
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
 
        // 设置响应头的Content-Disposition，使用"attachment"指示浏览器这是一个需要下载的文件
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString()));
 
        // 查询需要导出的数据 (包含复杂数据)
        List questionsOptionsAll = exQuestionMapper.getQuestionsOptionsAll();
 
        // 假设我们要合并第1行和第2列到第4列的数据（索引从0开始）
        int mergeRowIndex = 0; // 行索引 
        int[] mergeColumnIndex = new int[]{0,1,2,3,4,5,6}; // 列索引数组 要合并的列
        ExcelFillCellMergeStrategy excelFillCellMergeStrategy = new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex);
 
        EasyExcel.write(response.getOutputStream(), ExExcelQuestionOptionsDto.class)
                .registerWriteHandler(excelFillCellMergeStrategy)
                .sheet("测试")
                .doWrite(questionsOptionsAll);
 
        return R.success();
    }