工作当中，遇到一个需要复杂表格的设计，第一次用easyexcel，在这里记录一下，看到的小伙伴大家一起进步。
需求
话不多说直接上图，大概就是需要这种格式的excel，根据选择下载的个数分成多个sheet
代码
好了需求来了，废话不说，直接上代码。
1.引入pom
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency>
2.创建excel，具体输出什么内容，是地址，还是文件名，自行选择
//创建excelString 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()) {//合并策略mapMap<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
这是表格的实体类
@Datapublic 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@ToStringpublic 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;}@Overrideprotected 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索引是上一行索引的，如果有，则索引+1if (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;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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;}}
这样，任务就完成了！
