esayexcel导出并合并单元格

package com.example.demoexport.demos.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
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 org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;

import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
public class EsayExcelUtil {
    private static final ThreadLocal<Integer> EXPORT_DATA_SIZE = new ThreadLocal<>();

    /**
     * 导出excel并合并单元格
     * 调用示例 EsayExcelUtil.exportAndMergeCells(response, "导出", new int[]{0,1}, ExportModel.class, () -> queryList(wrapper), 5000);
     *
     * @param response         响应
     * @param fileName         文件名
     * @param mergeColumnIndex 需要合并的列
     * @param c                导出类
     * @param query            查询数据
     * @param pageSize         页容量,sheet页单页数据量也使用此参数
     */
    @SneakyThrows
    public static <T> void exportAndMergeCells(HttpServletResponse response, String fileName, int[] mergeColumnIndex, Class<T> c, MyBatisQuery<T> query, int pageSize) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String encodeFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodeFileName + ".xlsx");

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), c)
                .registerWriteHandler(new RowWriterStrategy(mergeColumnIndex))
                .autoCloseStream(Boolean.TRUE)
                .build();

        int line = 1;
        boolean flag = true;
        int sheet = 0;
        for (int i = 1; i <= line; i++) {
            Page<Object> page = PageHelper.startPage(i, pageSize);
            List<T> list = query.execute();
            if (CollectionUtils.isEmpty(list)) {
                throw new RuntimeException("没有数据");
            }

            EXPORT_DATA_SIZE.set(list.size());
            WriteSheet writeSheet = EasyExcel.writerSheet(sheet, "sheet" + sheet).build();
            sheet++;
            excelWriter.write(list, writeSheet);

            if (flag) {
                line = (int) Math.ceil((double) page.getTotal() / pageSize);
                flag = false;
            }
        }
        excelWriter.finish();
        EXPORT_DATA_SIZE.remove();
    }

    @FunctionalInterface
    public interface MyBatisQuery<T> {
        List<T> execute();
    }


    /**
     * 单元格合并策略（行处理）
     */
    public static class RowWriterStrategy implements RowWriteHandler {
        //合并行计数
        private int count;

        //要合并的列 从0开始
        private final int[] mergeColumnIndex;

        private int rowCount;

        public RowWriterStrategy(int[] mergeColumnIndex) {
            this.mergeColumnIndex = mergeColumnIndex;
        }

        @Override
        public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {

        }

        @Override
        public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

        }

        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
            //当前行索引
            int curRowNum = row.getRowNum();

            if (!isHead) {
                rowCount++;
                //当前行第一列单元格
                Cell curACell = row.getCell(0);
                Object curAData = curACell.getCellType() == CellType.STRING ? curACell.getStringCellValue() : curACell.getNumericCellValue();
                //当前行第二列单元格
                Cell curBCell = row.getCell(1);
                Object curBData = curBCell.getCellType() == CellType.STRING ? curBCell.getStringCellValue() : curBCell.getNumericCellValue();

                //上一行第一列单元格
                Cell preACell = row.getSheet().getRow(curRowNum - 1).getCell(0);
                Object preAData = preACell.getCellType() == CellType.STRING ? preACell.getStringCellValue() : preACell.getNumericCellValue();
                //上一行第二列单元格
                Cell preBCell = row.getSheet().getRow(curRowNum - 1).getCell(1);
                Object preBData = preBCell.getCellType() == CellType.STRING ? preBCell.getStringCellValue() : preBCell.getNumericCellValue();

                if (curAData.equals(preAData) && curBData.equals(preBData)) {
                    count++;
                    //如果为数据最后一行则合并
                    if ((relativeRowIndex + 1) % EXPORT_DATA_SIZE.get() == 0) {
                        for (int columnIndex : mergeColumnIndex) {
                            mergeSomeRow(writeSheetHolder, (curRowNum - count), curRowNum, columnIndex);
                        }
                        count = 0;
                    }
                } else {
                    if (count > 0) {
                        for (int columnIndex : mergeColumnIndex) {
                            mergeSomeRow(writeSheetHolder, (curRowNum - count - 1), (curRowNum - 1), columnIndex);
                        }
                        count = 0;
                    }
                }
            }
        }

        /**
         * 按列合并单元格
         *
         * @param writeSheetHolder
         * @param firstRowIndex    开始行
         * @param lastRowIndex     结束行
         * @param curColIndex      需要合并的列
         */
        private void mergeSomeRow(WriteSheetHolder writeSheetHolder, int firstRowIndex, int lastRowIndex, int curColIndex) {
            Sheet sheet = writeSheetHolder.getSheet();
            try {
                CellRangeAddress cellAddresses = new CellRangeAddress(firstRowIndex, lastRowIndex, curColIndex, curColIndex);
                Field sh = sheet.getClass().getDeclaredField("_sh");
                sh.setAccessible(true);
                XSSFSheet shSheet = (XSSFSheet) sh.get(sheet);
                CTWorksheet worksheet = shSheet.getCTWorksheet();
                CTMergeCells ctMergeCells = worksheet.getMergeCells() != null ? worksheet.getMergeCells() : worksheet.addNewMergeCells();
                CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
                ctMergeCell.setRef(cellAddresses.formatAsString());
            } catch (Exception e) {
                log.error("合并单元格失败,error:{}", e.getMessage());
            }
        }
    }

}