easyexcel复杂表格---包含单元格合并，表格标题，以及自定义字段写入

工作当中，遇到一个需要复杂表格的设计，第一次用easyexcel，在这里记录一下，看到的小伙伴大家一起进步。

需求

话不多说直接上图，大概就是需要这种格式的excel，根据选择下载的个数分成多个sheet

代码

好了需求来了，废话不说，直接上代码。

1.引入pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>

2.创建excel，具体输出什么内容，是地址，还是文件名，自行选择

  //创建excel
        String fileWholeName = UUID.randomUUID().toString() + "_" + "预算申报单.xls";
        //具体的下载路径
        String downloadPath = AssetsMgsYiConfig.getDownloadPath() + fileWholeName;
        ExcelWriter excelWriter = EasyExcel.write(downloadPath, BudgetList.class).excelType(ExcelTypeEnum.XLS).build();
        try {
            int i = 0;
            for (String key : resultMap.keySet()) {
                //合并策略map
                Map<String, List<RowRangeDto>> strategyMap = MergeUtil.addMerStrategy(resultMap.get(key).getBudgetList());
                BudgetDeclareSheetWriteHandler budgetDeclareSheetWriteHandler = new BudgetDeclareSheetWriteHandler(resultMap.get(key));
                WriteSheet writeSheet = EasyExcel.writerSheet(i, key)
                        //设置表单头部信息
                        .head(BudgetList.class)
                        //设置拦截器和自定义样式
                        .registerWriteHandler(new BudgetMergeStrategy(strategyMap))
                        .registerWriteHandler(budgetDeclareSheetWriteHandler)
                        .registerWriteHandler(new HorizontalCellStyleStrategy(EasyExcelUtils.getHeadStyle(), EasyExcelUtils.getContentStyle()))
                        //设置样式及写入表单头信息开始的行数
                        .useDefaultStyle(true).relativeHeadRowIndex(5)
                        .build();
                excelWriter.write(resultMap.get(key).getBudgetList(), writeSheet);
                i++;
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        return fileWholeName;

3.方法里涉及到的几个实体类接下来一一说一下

3.1 BudgetList
这是表格的实体类

@Data
public class BudgetList {
    /**
     * 类别
     */
    @ExcelProperty(value = {"类别"}, index = 0)
    private String declareTypeString;
    /**
     * 名目
     */
    @ExcelProperty(value = {"名目"}, index = 1)
    private String name;
    /**
     * 价格
     */
    @ExcelProperty(value = {"金额(元)"}, index = 2)
    private BigDecimal price;
    /**
     * 计划购置原因
     */
    @ExcelProperty(value = {"计划购置原因"}, index = 3)
    private String reason;
    /**
     * 备注
     */
    @ExcelProperty(value = {"备注"}, index = 4)
    private String remark;
}

3.2 resultMap
这是表格数据，它是一个map，key为sheet名称，value为每个sheet的数据

 Map<String, BudgetDeclareExportVo> resultMap = new HashMap<>();

3.3 RowRangeDto
这是一个分段的起始位置DTO

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
    private int start;
    private int end;
}

3.4 BudgetMergeStrategy
这个类继承AbstractMergeStrategy抽象类，实现merge方法，进行自定义合并策略，传入自定义的合并策略map，解析此map，添加合并请求。

public class BudgetMergeStrategy extends AbstractMergeStrategy {
    private Map<String, List<RowRangeDto>> strategyMap;

    public BudgetMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
            Integer columnIndex = Integer.valueOf(entry.getKey());
            entry.getValue().forEach(rowRange -> {
                //添加一个合并请求
                sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                        rowRange.getEnd(), columnIndex, columnIndex));
            });
        }
    }
}

3.5 MergeUtil
这是一个合并的util，主要是看你希望从哪行开始合并，比如说我希望从第7行还是合并，i就是5，具体的看你自己

public class MergeUtil {

    public static Map<String, List<RowRangeDto>> addMerStrategy(List<BudgetList> excelDtoList) {
        Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
        BudgetList preExcelDto = null;
        for (int i = 5; i < excelDtoList.size() + 5; i++) {
            BudgetList currDto = excelDtoList.get(i-5);
            if (preExcelDto != null) {
                //第7行开始判断是否需要合并
                if (currDto.getDeclareType().equals(preExcelDto.getDeclareType())) {
                    //如果类目一样则合并
                    fillStrategyMap(strategyMap, "0", i);
                }
            }
            preExcelDto = currDto;
        }
        return strategyMap;
    }

    private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index) {
        List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
        boolean flag = false;
        for (RowRangeDto dto : rowRangeDtoList) {
            //分段list中是否有end索引是上一行索引的，如果有，则索引+1
            if (dto.getEnd() == index) {
                dto.setEnd(index + 1);
                flag = true;
            }
        }
        //如果没有，则新增分段
        if (!flag) {
            rowRangeDtoList.add(new RowRangeDto(index, index + 1));
        }
        strategyMap.put(key, rowRangeDtoList);
    }
}

3.6 BudgetDeclareSheetWriteHandler
这个类实现了SheetWriteHandler，主要就是在开始填充表格之前，设置标题和其他的一些信息

public class BudgetDeclareSheetWriteHandler implements SheetWriteHandler {

    private BudgetDeclareExportVo budgetDeclareExportVo;

    public BudgetDeclareSheetWriteHandler(BudgetDeclareExportVo budgetDeclareExportVo) {
        this.budgetDeclareExportVo = budgetDeclareExportVo;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = writeSheetHolder.getSheet();
        //设置表格宽度
        sheet.setColumnWidth(0, 30 * 256);
        sheet.setColumnWidth(1, 30 * 256);
        sheet.setColumnWidth(2, 30 * 256);
        sheet.setColumnWidth(3, 40 * 256);
        sheet.setColumnWidth(4, 30 * 256);

        //设置标题
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 800);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("预算申报单");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        //设置其他信息的单元格样式
        CellStyle cellStyleInfo = workbook.createCellStyle();
        cellStyleInfo.setVerticalAlignment(VerticalAlignment.CENTER);
        //合并单元格
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 4));
        //设置基本信息
        Row row2 = sheet.createRow(1);
        row2.setHeight((short) 400);
        Cell cell2 = row2.createCell(0);
        cell2.setCellValue("单据编号：" + budgetDeclareExportVo.getDocumentNo());
        cell2.setCellStyle(cellStyleInfo);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 1));
        Cell cell23 = row2.createCell(3);
        cell23.setCellValue("单据状态：" + ApproveStatusEnum.getDesc(budgetDeclareExportVo.getStatus()));
        cell23.setCellStyle(cellStyleInfo);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 3, 4));

        Row row3 = sheet.createRow(2);
        row3.setHeight((short) 400);
        Cell cell3 = row3.createCell(0);
        cell3.setCellValue("申请公司：" + budgetDeclareExportVo.getApplyCompanyName());
        cell3.setCellStyle(cellStyleInfo);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 0, 1));
        Cell cell33 = row3.createCell(3);
        cell33.setCellValue("申请部门：" + budgetDeclareExportVo.getApplyDeptName());
        cell33.setCellStyle(cellStyleInfo);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 3, 4));

        Row row4 = sheet.createRow(3);
        row4.setHeight((short) 400);
        Cell cell6 = row4.createCell(0);
        cell6.setCellValue("备注：" + budgetDeclareExportVo.getRemark());
        cell6.setCellStyle(cellStyleInfo);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(3, 4, 0, 4));
    }
}

3.7 EasyExcelUtils
这个util主要写了单元格的一些样式，具体需要自行定义

public class EasyExcelUtils {
    /**
     * excel内容样式
     *
     * @return
     */
    public static WriteCellStyle getContentStyle() {
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        //contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 8);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return contentWriteCellStyle;
    }
    /**
     * 表格頭部樣式
     *
     * @return
     */
    public static WriteCellStyle getHeadStyle() {
        WriteCellStyle headStyle = new WriteCellStyle();
        headStyle.setWrapped(false);
        return headStyle;
    }
}

这样，任务就完成了！