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);}}
}