EasyExcel基于2.2.6版本自定义合并单元格自定义样式下载多个sheet

首先创建工作输出流

OutputStream outputStream = getOutputStream(request, response, fileName);

创建写Excel文件

ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz)
        .registerWriteHandler(new MergeStrategy(mergeMap))
        .registerWriteHandler(new CustomWriteCellStyle(sheetMap))
        .build();

写入多个sheet 这里可以循环写入

WriteSheet writeSheet = EasyExcel.writerSheet(key).build();

最后关闭

excelWriter.finish();

对此读者应该还有疑问MergeStrategy、CustomWriteCellStyle 这两个类是干什么用的

MergeStrategy 是自定义的合并策略

public class MergeStrategy implements CellWriteHandler {
    //合并策略map   string：sheet名字 Integer:行数 List：合并的列
    private Map<String,Map<Integer,List<ColumnRangeDto>>> listMap;
    private static final int ROWINDEX =3; //在本行下执行
    private static final int COLUMNINDEX =0;//在本列下执行
    public MergeStrategy(Map<String,Map<Integer,List<ColumnRangeDto>>> listMap) {
        this.listMap = listMap;
    }


    @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 relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (cell.getRowIndex() == ROWINDEX && cell.getColumnIndex() == COLUMNINDEX) {
            Map<Integer, List<ColumnRangeDto>> integerListMap = listMap.get(writeSheetHolder.getSheet().getSheetName());
            if (!CollectionUtils.isEmpty(integerListMap)){
                integerListMap.forEach((key,value)->{
                    if(!CollectionUtils.isEmpty(value)){
                        value.forEach(it->{
                            writeSheetHolder.getSheet().addMergedRegionUnsafe(new CellRangeAddress(key, key, it.getStart(), it.getEnd()));
                        });
                    }
                });
            }
        }
    }
}

CustomWriteCellStyle 自定义样式

public class CustomWriteCellStyle implements CellWriteHandler {

    Logger log = LoggerFactory.getLogger(MergeStrategy.class);
    //sheel和行做对应
    private Map<String,Integer> sheetMap;

    public CustomWriteCellStyle(Map<String, Integer> sheetMap) {
        this.sheetMap = sheetMap;
    }

    @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 relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Map<String,Object> map =new HashMap<>();
        Integer integer = sheetMap.get(writeSheetHolder.getSheet().getSheetName());
        if(integer ==null){
            //设置默认样式
            map.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
            map.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.GREY_25_PERCENT.getIndex());
            map.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.GREY_25_PERCENT.getIndex());
        }else{
            //当前sheet和提前设定好的相同按照设定的设置样式
            //
            if(cell.getRowIndex()<7){
                //7行前的样式
                map.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
                map.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.GREY_25_PERCENT.getIndex());
                map.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.GREY_25_PERCENT.getIndex());
            }else if(cell.getRowIndex()<11){
                
                if(cell.getRowIndex()==8){
                    //模板名称这行样式居中
                    map.put(CellUtil.ALIGNMENT,HorizontalAlignment.CENTER);
                }
                map.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
                map.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.WHITE1.getIndex());
                map.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.WHITE1.getIndex());
            }else if(cell.getRowIndex()>(11+integer)){
                //表格下面刷白并且右对齐
                map.put(CellUtil.ALIGNMENT,HorizontalAlignment.RIGHT);
                map.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
                map.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.WHITE1.getIndex());
                map.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.WHITE1.getIndex());
            }else {
                //保养内容和完成情况列加边框
                if(cell.getColumnIndex()==1||cell.getColumnIndex()==2||cell.getColumnIndex()==3||cell.getColumnIndex()==4){
                    map.put(CellUtil.ALIGNMENT,HorizontalAlignment.CENTER);
                    map.put(CellUtil.BORDER_BOTTOM, BorderStyle.THIN);
                    map.put(CellUtil.BORDER_LEFT, BorderStyle.THIN);
                    map.put(CellUtil.BORDER_RIGHT, BorderStyle.THIN);
                    map.put(CellUtil.BORDER_TOP, BorderStyle.THIN);
                }else{
                    //其他地方背景白色
                    map.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
                    map.put(CellUtil.FILL_BACKGROUND_COLOR, IndexedColors.WHITE1.getIndex());
                    map.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.WHITE1.getIndex());
                }
            }
        }

        CellUtil.setCellStyleProperties(cell,map);

    }

最后附上效果图