EasyExcle导出合并单元格的文件

1、代码

private void buildCentralizedBiddingTempletFile(HttpServletResponse response, List<String> companyNames) {
        String fileName = "xxxx";
        List<CellWriteHandler> cellWriteHandlerList = new ArrayList<>();
        List<CustomMergeStrategy> customMergeStrategyList = new ArrayList<>();


        //表数据
        List<List<String>> tableData = new ArrayList<>();
        int row = 2;
        for (String p : TimeConstant.TWENTY_FOUR_TIME_FRAMES) {
            List<String> datas = new ArrayList<>();
            datas.add(p);
            for (int i = 1; i <= companyNames.size(); i++) {
                datas.add("");
                datas.add("");
                customMergeStrategyList.add(new CustomMergeStrategy(row, (i * 2) - 1, i * 2, "sheet0"));
            }
            tableData.add(datas);
            row++;
        }

        //表头
        List<List<String>> heads = new ArrayList<>();
        List<String> firstHead = new ArrayList<>();
        firstHead.add("用户名称");
        firstHead.add("时段");
        heads.add(firstHead);
        int index = 1;
        for (String p : companyNames) {
            List<String> cell1 = new ArrayList<>();
            List<String> cell2 = new ArrayList<>();
            //相同字段合并单元格
            cell1.add(p);
            cell2.add("#这里填写价差#");
            cell1.add("电量（MWh）");
            cell2.add("电量（MWh）");
            heads.add(cell1);
            heads.add(cell2);
            cellWriteHandlerList.add(new CustomTotalRowMergeStrategy(1, (index * 2) - 1, index * 2));
            index++;
        }

        DownloadUtil.downloadDynamicHeadExcel(fileName, heads, tableData, cellWriteHandlerList, null, response, customMergeStrategyList);
    }

2、使用到的类

/**
 * @author mz
 * 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法
 */
public class CustomMergeStrategy extends AbstractMergeStrategy {

    /**
     * 合并行数index
     */
    private int mergeRowIndex;

    /**
     * 合并开始列
     */
    private int mergeColumnStartIndex;

    /**
     * 合并结束列
     */
    private int mergeColumnEndIndex;

    /**
     * 需要合并的sheetName
     */
    private String sheetName;


    // exportDataList为待合并目标列的值
    public CustomMergeStrategy(Integer mergeRowIndex, Integer mergeColumnStartIndex, Integer mergeColumnEndIndex, String sheetName) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnStartIndex = mergeColumnStartIndex;
        this.mergeColumnEndIndex = mergeColumnEndIndex;
        this.sheetName = sheetName;
    }


    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        //合并2列好用
        if (curRowIndex == mergeRowIndex && curColIndex >= mergeColumnStartIndex && curColIndex < mergeColumnEndIndex) {
            sheet.addMergedRegion(new CellRangeAddress(mergeRowIndex, mergeRowIndex, mergeColumnStartIndex, mergeColumnEndIndex));
        }

    }

}

public class CustomTotalRowMergeStrategy implements CellWriteHandler {

    /**
     * 合并行数index
     */
    private int mergeRowIndex;

    /**
     * 合并开始列
     */
    private int mergeColumnStartIndex;

    /**
     * 合并结束列
     */
    private int mergeColumnEndIndex;


    public CustomTotalRowMergeStrategy(int mergeRowIndex, int mergeColumnStartIndex, int mergeColumnEndIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnStartIndex = mergeColumnStartIndex;
        this.mergeColumnEndIndex = mergeColumnEndIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer integer, Boolean aBoolean) {

    }

    @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<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        //合并2列好用
        if (curRowIndex == mergeRowIndex && curColIndex >= mergeColumnStartIndex && curColIndex < mergeColumnEndIndex) {
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.addMergedRegion(new CellRangeAddress(mergeRowIndex, mergeRowIndex, mergeColumnStartIndex, mergeColumnEndIndex));
        }

    }

}


public static void downloadDynamicHeadExcel(String fileName, List<List<String>> heads, List<List<String>> dataList
            , List<CellWriteHandler> cellWriteHandlerList, AbstractRowHeightStyleStrategy rowHeightStyleStrategy, HttpServletResponse response, List<CustomMergeStrategy> customMergeStrategyList) {
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLUtil.encode(fileName) + ".xlsx");
            response.setHeader("fileName", URLUtil.encode(fileName) + ".xlsx");
            response.setContentType("application/json");
            ExcelWriterBuilder builder = EasyExcel.write(outputStream).head(heads);
            builder.registerWriteHandler(new AbstractColumnWidthStyleStrategy() {
                @Override
                protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), 5000);
                }
            }).registerWriteHandler(rowHeightStyleStrategy);
            if (CollectionUtil.isNotEmpty(customMergeStrategyList)) {
                for (CustomMergeStrategy customMergeStrategy : customMergeStrategyList) {
                    builder.registerWriteHandler(customMergeStrategy);
                }
            }
            if (CollectionUtil.isNotEmpty(cellWriteHandlerList)) {
                for (CellWriteHandler cellWriteHandler : cellWriteHandlerList) {
                    builder.registerWriteHandler(cellWriteHandler);
                }
            }
            //automaticMergeHead禁用easyExcle自动合并相同表格内容
            builder.automaticMergeHead(false).sheet("sheet0").doWrite(dataList);
        } catch (Exception e) {
            log.error(">>> 下载文件异常，具体信息为：{}", e.getMessage());
            throw new FxServiceException("OperatorServiceCodeEnum.DOWNLOAD_FILE_ERROR");
        }
    }

最后

该功能目前只支持列合并，行合并需要进一步开发，代码也有不规范的地方。