EasyExcel 动态设置表格的背景颜色和排列

        项目中使用EasyExcel把数据以excel格式导出，其中设置某一行、某一列单元格的背景颜色、排列方式十分常用，记录下来方便以后查阅。

1. 导入maven依赖：

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.0</version><scope>compile</scope>
</dependency>

2. 导出字段实体类：

@Data
@NoArgsConstructor
@Accessors(chain = true)
public class AchievementParamExcelDto {/*** 字段是动态配置的，配置哪些显示哪些*/@ExcelProperty(value = "产品类别", converter = NullConverter.class)@AchievementParam("0001001")private String type;@ExcelProperty(value = "商品名称(开票名称)", converter = NullConverter.class)@AchievementParam("0001002")private String commodityName;@ExcelProperty(value = "品牌", converter = NullConverter.class)@AchievementParam("0001003")private String brand;@ExcelProperty(value = "型号", converter = NullConverter.class)@AchievementParam("0001004")private String model;@ExcelProperty(value = "技术规格", converter = NullConverter.class)@AchievementParam("0001005")private String technicalSpecifications;@ExcelProperty(value = "单位", converter = NullConverter.class)@AchievementParam("0001006")private String unit;@ExcelProperty(value = "数量", converter = IntegerNumberConverter.class)@AchievementParam("0001007")private Integer num;@ExcelProperty(value = "单价", converter = IntegerNumberConverter.class)@AchievementParam("0001008")private BigDecimal unitPrice;@ExcelProperty(value = "小计(元)", converter = IntegerNumberConverter.class)@AchievementParam("0001009")@NumberFormat("#.00")private BigDecimal subtotal;@ExcelProperty(value = "物料代码", converter = NullConverter.class)@AchievementParam("0001010")private String materialCode;@ExcelProperty("备注")@AchievementParam("0001011")private String remark;@ExcelProperty(value = "序号", converter = IntegerNumberConverter.class)@AchievementParam("0001012")private Integer index;@ExcelProperty(value = "销售项名称", converter = NullConverter.class)@AchievementParam("0001013")private String name;@ExcelProperty(value = "销售项编码", converter = NullConverter.class)@AchievementParam("0001014")private String itemCode;public AchievementParamExcelDto(int index, AchievementVo vo, String remark) {this.index = index;this.name = handleNull(vo.getName());this.type = handleNull(vo.getType());this.commodityName = handleNull(vo.getCommodityName());this.brand = handleNull(vo.getBrand());this.model = handleNull(vo.getModel());this.technicalSpecifications = handleNull(vo.getTechnicalSpecifications());this.unit = handleNull(vo.getUnit());this.num = StringUtils.isEmpty(vo.getNum()) ? 0: Integer.parseInt(vo.getNum());this.unitPrice = vo.getUnitPrice();this.subtotal = vo.getSubtotal();this.materialCode = StringUtils.isEmpty(vo.getMaterialCode()) ? "无固定物料" : vo.getMaterialCode();this.remark = remark;this.itemCode = StringUtils.isEmpty(vo.getItemCode()) ? (StringUtils.isEmpty(vo.getMaterialCode()) ? "— —" : vo.getMaterialCode()) : vo.getItemCode();}private String handleNull(String str) {return StringUtils.isEmpty(str) ? "— —" : str;}
}

3. 数据导出业务，并在业务中实现动态设置背景颜色和排列方式：

private void doDownloadAchievementExcelFile(List<AchievementsExportParamEntity> achievementExportParam,List<AchievementParamExcelDto> result, HttpServletResponse response, String fileName) {if (CollectionUtils.isEmpty(achievementExportParam)) {return;}// excel表头List<List<String>> header = new ArrayList<>();// 需要导出的数据字段List<String> includeColumnFiledNames = new ArrayList<>();// 导出实体类中所有的字段Field[] fields = ReflectUtil.getFields(AchievementParamExcelDto.class);achievementExportParam.sort(Comparator.comparing(AchievementsExportParamEntity::getSort));// 通过配置导出的字段来动态设置表头和行字段数据for (AchievementsExportParamEntity achievementsExportParamEntity : achievementExportParam) {String paramCode = achievementsExportParamEntity.getParamCode();//设置需要导出的头、行字段for (Field field : fields) {AchievementParam annotation = field.getAnnotation(AchievementParam.class);if (annotation == null) {continue;}if (StringUtils.equals(paramCode, annotation.value())) { // 判断字段是否有配置导出header.add(Lists.newArrayList(achievementsExportParamEntity.getParamName())); // 添加头数据includeColumnFiledNames.add(field.getName()); // 添加需要导出的行字段数据break;}}}/******************下面是设置背景颜色和排列方式的关键代码******************/// 按类型进行设置背景颜色Map<Integer, Short> rowBackColor = new HashMap<>();for (int i = 0; i < result.size(); i++) {AchievementParamExcelDto achievementParamExcelDto = result.get(i);String remark = achievementParamExcelDto.getRemark();if ("软件".equals(remark)) {// 表格的行索引从1开始，列索引从0开始rowBackColor.put(i + 1, IndexedColors.LEMON_CHIFFON.index);} else if ("硬件".equals(remark)) {rowBackColor.put(i + 1, IndexedColors.LIGHT_TURQUOISE.index);} else if ("模型".equals(remark)) {rowBackColor.put(i + 1, IndexedColors.LIGHT_GREEN.index);}else if ("实施服务".equals(remark)) {rowBackColor.put(i + 1, IndexedColors.LIGHT_CORNFLOWER_BLUE.index);}}// 导出时技术规格居左显示，其它剧中显示Map<Integer, HorizontalAlignment> horizontalAlignmentMap = new HashMap<>();for (int i = 0; i < header.size(); i++) {if ("技术规格".equals(header.get(i).get(0))) {horizontalAlignmentMap.put(i, HorizontalAlignment.LEFT);} else {horizontalAlignmentMap.put(i, HorizontalAlignment.CENTER);}}try {Class<? extends AchievementParamExcelDto> aClass = AchievementParamExcelDto.class;// 设置字段顺序Set<String> orderColumn = new LinkedHashSet<>(includeColumnFiledNames);for (Field field : fields) {orderColumn.add(field.getName());}setExcelIndex(aClass, new ArrayList<>(orderColumn));response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), aClass).registerWriteHandler(new CustomCellWriteHandler(rowBackColor, horizontalAlignmentMap)).registerWriteHandler(new WidthWriteHandler()).sheet("成果物清单").head(header).includeColumnFieldNames(includeColumnFiledNames).doWrite(result);} catch (Exception e) {log.error("download error", e);throw new BusinessException(ErrorCodes.FILE_DOWNLOAD_ERROR);}}

4. 在自定义样式的handle中根据配置数据动态显示样式：

public class CustomCellWriteHandler implements CellWriteHandler {// 一个表格最多创建6W个样式，把每行的背景色放到集合中，统一设置，避免设置失败Map<Integer, Short> rowBackColor = new HashMap<>();// 排列样式集合Map<Integer, HorizontalAlignment> horizontalAlignmentMap = new HashMap<>();public CustomCellWriteHandler() {}public CustomCellWriteHandler(Map<Integer, Short> rowBackColor, Map<Integer, HorizontalAlignment> horizontalAlignmentMap) {this.rowBackColor = rowBackColor;this.horizontalAlignmentMap = horizontalAlignmentMap;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {// 设置行高short height = 600;row.setHeight(height);}@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {Cell cell = context.getCell();// 当前事件会在 数据设置到poi的cell里面才会回调// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not trueif (!BooleanUtils.isNotTrue(context.getHead())) { // 表头Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUNDcellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 颜色// 设置rgb颜色byte[] rgb = new byte[]{(byte) 192, 0, 0};XSSFCellStyle xssfCellColorStyle = (XSSFCellStyle) cellStyle;xssfCellColorStyle.setFillForegroundColor(new XSSFColor(rgb, null));xssfCellColorStyle.setAlignment(HorizontalAlignment.CENTER);xssfCellColorStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 宽度// 边框xssfCellColorStyle.setBorderBottom(BorderStyle.THIN);xssfCellColorStyle.setBorderLeft(BorderStyle.THIN);xssfCellColorStyle.setBorderRight(BorderStyle.THIN);xssfCellColorStyle.setBorderTop(BorderStyle.THIN);// 字体Font font = workbook.createFont();font.setBold(true);font.setFontHeightInPoints((short) 11);font.setFontName("微软雅黑");font.setColor(IndexedColors.WHITE.getIndex());xssfCellColorStyle.setFont(font);cell.setCellStyle(xssfCellColorStyle);context.getFirstCellData().setWriteCellStyle(null);} else { // 单元格// 拿到poi的workbookWorkbook workbook = context.getWriteWorkbookHolder().getWorkbook();// 不同单元格尽量传同一个 cellStyleCellStyle cellStyle = workbook.createCellStyle();/***************下面是关键代码，根据表格的行号和列号与设置的数据进行匹配后动态设置***********/// 行号-重要***Integer rowIndex = context.getRowIndex();// 如果设置了每行的样式，动态设置；没有设置则统一设置为白色背景if (null != rowBackColor.get(rowIndex)) {// 拿到设置的颜色Short colorIndex = rowBackColor.get(rowIndex);cellStyle.setFillForegroundColor(colorIndex);} else {// 默认背景颜色cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());}// 列号-重要***Integer columnIndex = context.getColumnIndex();// 技术规格左对齐，其它剧中对齐if (null != horizontalAlignmentMap.get(columnIndex)) {cellStyle.setAlignment(horizontalAlignmentMap.get(columnIndex));} else {cellStyle.setAlignment(HorizontalAlignment.CENTER);}// 垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);// 字体Font font = workbook.createFont();font.setFontHeightInPoints((short) 8);font.setFontName("微软雅黑");cellStyle.setFont(font);// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUNDcellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cell.setCellStyle(cellStyle);// 由于这里没有指定dataformat 最后展示的数据 格式可能会不太正确// 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到// cell里面去 会导致自己设置的不一样（很关键）context.getFirstCellData().setWriteCellStyle(null);}}
}

5. 至此，主要的代码实现完了，实现的效果：

 

在第3步中，有涉及到几个方法或实体类，这里对应给出补充：

①入参result，是上一步从数据库中查询并封装后的数据，大致如下：

List<AchievementParamExcelDto> result = new ArrayList<>();
int index = 1;
for (AchievementVo achievementVo : distAchievementDto.getSaleList()) {result.add(new AchievementParamExcelDto(index++, achievementVo, localeMessage.getMessage(LanguageCn.PRODUCT_TYPE_SOFTWARE)));
}
for (AchievementVo achievementVo : distAchievementDto.getHardwareList()) {if (achievementVo.getSubtotal() != null && achievementVo.getSubtotal().compareTo(BigDecimal.ZERO) > 0) { // 只导出小计大于0的数据result.add(new AchievementParamExcelDto(index++, achievementVo, localeMessage.getMessage(LanguageCn.PRODUCT_TYPE_HARDWARE)));}
}
for (AchievementVo achievementVo : distAchievementDto.getModelList()) {result.add(new AchievementParamExcelDto(index++, achievementVo, localeMessage.getMessage(LanguageCn.PRODUCT_TYPE_MODEL)));
}
for (AchievementVo achievementVo : distAchievementDto.getServiceList()) {result.add(new AchievementParamExcelDto(index++, achievementVo, localeMessage.getMessage(LanguageCn.PRODUCT_TYPE_SERVICE)));
}

 distAchievementDto对应的数据结构如下（AchievementVo便是业务中数据实体类了，可参考效果图中的字段）：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class DistAchievementDto implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "软件销售清单", example = "[]")private List<AchievementVo> saleList = new ArrayList<>();@ApiModelProperty(value = "实施服务销售清单", example = "[]")private List<AchievementVo> serviceList = new ArrayList<>();@ApiModelProperty(value = "硬件销售清单", example = "[]")private List<AchievementVo> hardwareList = new ArrayList<>();@ApiModelProperty(value = "模型清单", example = "[]")private List<AchievementVo> modelList = new ArrayList<>();
}

② 入参 achievementExportParam 是从数据库中查出的配置导出的字段：

@EqualsAndHashCode(callSuper = true)
@Data
public class AchievementsExportParamEntity extends AbstractEntity implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty("导出类别：1-SPS；2-供方案使用；5-海外版本")private Integer exportType;@ApiModelProperty("导出字段编码")private String paramCode;@ApiModelProperty("导出字段名称")private String paramName;@ApiModelProperty("是否选中：0-否 1-是 默认值是0")private String paramSelected;@ApiModelProperty("导出顺序")private Integer sort;
}

 ③ 排序方法 setExcelIndex

@SuppressWarnings("unchecked")
private synchronized void setExcelIndex(Class<?> aClass, List<String> columnNames) throws NoSuchFieldException, IllegalAccessException {//获取当前对象的字段ArrayList<Field> fields = Lists.newArrayList(aClass.getDeclaredFields());Class<?> superclass = aClass.getSuperclass();if (superclass != null) {//有父类则获取父类对象的字段fields.addAll(Lists.newArrayList(superclass.getDeclaredFields()));}for (int i = 0; i < columnNames.size(); i++) {for (Field field : fields) {if (!field.getName().equals(columnNames.get(i))) continue;ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation == null) continue;InvocationHandler handler = Proxy.getInvocationHandler(annotation);//获取 AnnotationInvocationHandler 的 memberValues 字段Field fieldMv = handler.getClass().getDeclaredField("memberValues");//因为这个字段是 private final 修饰，所以要打开权限fieldMv.setAccessible(true);Map<Object, Object> memberValues = (Map<Object, Object>) fieldMv.get(handler);if (!memberValues.containsKey("value")) continue;memberValues.put("index", i);}}
}

④ 自定义单元格宽度样式 WidthWriteHandler

public class WidthWriteHandler extends AbstractColumnWidthStyleStrategy {private final Map<String, Integer> map = new ImmutableMap.Builder<String, Integer>().put("序号", 9).put("名称", 18).put("产品类别", 32).put("商品名称（开票名称）", 22).put("品牌", 10).put("型号", 25).put("技术规格", 30).put("单位", 9).put("数量", 9).put("单价(元)", 9).put("小计(元)", 9).put("单价(美元)", 9).put("小计(美元)", 9).put("物料代码", 12).put("成果物类别", 11).put("销售项编码", 12).build();@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,Head head, Integer relativeRowIndex, Boolean isHead) {if (isHead) {String stringCellValue = cell.getStringCellValue();Integer integer = map.get(stringCellValue.trim());if (null == integer) {integer = 18;}Sheet sheet = writeSheetHolder.getSheet();int columnIndex = cell.getColumnIndex();// 列宽40sheet.setColumnWidth(columnIndex, integer * 256);}}
}