EasyExcel 自定义表头，表头合并，数据合并，自定义字体背景色
功能说明: 相当于导出一个计划安排，一条计划有一条数据，按照计划的计划完成时间显示， 根据数据状态导出背景色
思路：表头不固定，自己组合数据，格式为List<List<String>>, 数据也是这个格式
导出效果图
数据库信息
CREATE TABLE `sys_plan` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL COMMENT '计划名称', `description` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL COMMENT '计划描述', `status` tinyint NOT NULL DEFAULT '0' COMMENT '应用状态', `expect_start_at` datetime DEFAULT NULL COMMENT '计划开始时间', `expect_end_at` datetime NOT NULL COMMENT '计划完成时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_name` (`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2151158250246848513 DEFAULT CHARSET=utf8mb3 COMMENT='系统应用配置表';
总体代码
1 public void export(HttpServletResponse response, PlanExportQuery query) { 2 //查询所有数据 3 List<PlanEntity> list = this.dao.queryList(query); 4 if(null != list && !list.isEmpty()){ 5 PlanViewDTO dto = handleData(list, generateDefaultTitles());//生成数据 6 //获取表头 7 List<TitleVO> titleVOList= dto.getTitles(); 8 List<String> titlesCode = new ArrayList<>(); 9 List<List<String>> titles = generateTitle(titleVOList, titlesCode); 10 //样式信息 11 List<CellStyleModel> models = new ArrayList<>(); 12 //获取数据 13 try { 14 String name = "计划视图信息"; 15 List<List<Object>> datas = generateData(dto.getDatas(), models, query.getColorInfo(), name, titlesCode, dto.getTemporary()); 16 response.setContentType("application/vnd.ms-excel"); 17 response.setCharacterEncoding(StandardCharsets.UTF_8.name()); 18 response.addHeader("Content-disposition","attachment;filename="+ name +".xlsx"); 19 EasyExcel.write(response.getOutputStream()).head(titles).automaticMergeHead(false)//自动合并关闭 20 .registerWriteHandler(new CustomCellStyleHandler(models))//表格样式 21 .registerWriteHandler(new HeadMergeStrategy(2))//表头合并 22 .registerWriteHandler(new HeadCustomerWidthStrategy())//表头自定义宽度 23 .sheet(name).doWrite(datas); 24 25 } catch (Exception e) { 26 CheckHelper.check(1 != 1, new CustomException(ErrorType.BAD_REQUEST.getCode(), e.getMessage())); 27 // e.printStackTrace(); 28 } 29 30 } 31 32 } 33 34 /** 35 * 获取Excel数据 36 * @param datas 数据信息 37 * @param models 表格样式信息 38 * @param color 颜色信息 39 * @param sheetName 表单 40 * @param titlesCode 不固定表格的code 41 * @param addParam 添加的参数 42 * @return 43 * @throws Exception 44 */ 45 private List<List<Object>> generateData(List<PlanViewVO> datas, List<CellStyleModel> models, Map<Integer, String> color, 46 String sheetName, List<String> titlesCode, Map<String, Object> addParam) 47 throws Exception { 48 List<List<Object>> list = new ArrayList<>(); 49 for(int i=0; i<datas.size(); i++){ 50 List<Object> objects = new ArrayList<>(); 51 PlanViewVO vo = datas.get(i); 52 Integer status = vo.getStatus().getValue(); 53 for(int j=0; j<titlesCode.size(); j++){ 54 String code = titlesCode.get(j); 55 StringBuffer sb = new StringBuffer(); 56 sb.append("get"); 57 sb.append(code.substring(0, 1).toUpperCase()); 58 sb.append(code.substring(1)); 59 Method method = vo.getClass().getMethod(sb.toString()); 60 Object o = method.invoke(vo); 61 objects.add(o); 62 if (addParam.containsKey(code) && ObjectUtils.isNotEmpty(o)){ 63 String backgroudColor = ""; 64 if (null != color && color.containsKey(status)){ 65 backgroudColor = color.get(status); 66 67 } 68 if (StringUtils.isEmpty(backgroudColor)){ 69 backgroudColor = BackgroudColor.forDesc(status).getDesc(); 70 } 71 XSSFColor xssfColor = CellStyleModel.getRGBColor(backgroudColor); 72 models.add(CellStyleModel.createBackgroundColorCellStyleModel(sheetName, i+2, j, xssfColor)); 73 74 } 75 76 } 77 list.add(objects); 78 } 79 return list; 80 } 81 82 /** 83 * 获取Excel头部信息 84 * @param titles 固定表头信息 85 * @return 86 */ 87 private List<List<String>> generateTitle(List<TitleVO> titles, List<String> titlesCode) { 88 List<List<String>> list = new ArrayList<>(); 89 for(TitleVO titleVO : titles){ 90 String name = titleVO.getName(); 91 List<CodeName> sublist = titleVO.getSubTitle(); 92 if (null != sublist && !sublist.isEmpty()){ 93 for(CodeName codeName : sublist){ 94 String subName = codeName.getName(); 95 list.add(new ArrayList<>(Arrays.asList(name, subName))); 96 titlesCode.add(codeName.getCode()); 97 } 98 }else{ 99 list.add(new ArrayList<>(Arrays.asList(name))); 100 titlesCode.add(titleVO.getCode()); 101 } 102 } 103 return list; 104 } 105 106 /** 107 * 处理二级表头 108 * @param titleVOS 109 * @param list 110 * @return 返回值为需要添加的属性 key为yyyy.MM 111 */ 112 private Map<String, Object> handleTitle(List<TitleVO> titleVOS, List<PlanEntity> list) { 113 Map<String, Object> map = new HashMap<>(); 114 Map<String, Set<String>> dateInfo = list.stream().collect(Collectors.toMap(e-> LocalDateTimeUtils.parseYYYY(e.getExpectEndAt()), 115 e->{ 116 Set<String> a1 = new HashSet<>(); 117 a1.add(LocalDateTimeUtils.parseMM(e.getExpectEndAt())); 118 return a1; 119 }, 120 (Set<String> a1, Set<String> a2)->{ 121 a1.addAll(a2); 122 return a1; 123 } 124 125 )); 126 //获取二级表头信息 127 for(Map.Entry<String, Set<String>> entry : dateInfo.entrySet()){ 128 String year = entry.getKey(); 129 130 TitleVO titleVO = new TitleVO(); 131 titleVO.setName(year); 132 titleVO.setCode(year); 133 134 Set<String> months = entry.getValue(); 135 List<CodeName> subTitles = new ArrayList<>(); 136 for(String month : months){ 137 CodeName nameCode = new CodeName(); 138 String code = year+"."+month; 139 nameCode.setCode(code); 140 nameCode.setName(month); 141 subTitles.add(nameCode); 142 map.put(code, ""); 143 } 144 titleVO.setSubTitle(subTitles); 145 titleVOS.add(titleVO); 146 } 147 return map; 148 } 149 150 /** 151 * 获取固定的表头信息 152 */ 153 private List<TitleVO> generateDefaultTitles() { 154 Map<String, String> data = ParameterUtitles.achieveParam(PlanViewVO.class); 155 List<TitleVO> list = new ArrayList<>(); 156 for(Map.Entry<String, String> entry : data.entrySet()){ 157 TitleVO vo = new TitleVO(); 158 vo.setCode(entry.getKey()); 159 vo.setName(entry.getValue()); 160 list.add(vo); 161 } 162 return list; 163 } 164 165 /** 166 * 获取全部数据 167 * @param list 全部数据 168 * @param titleVOS 表头数据 169 * @return 170 */ 171 private PlanViewDTO handleData(List<PlanEntity> list, List<TitleVO> titleVOS) { 172 PlanViewDTO planViewDTO = new PlanViewDTO(); 173 //处理表头 174 Map<String, Object> addParam = handleTitle(titleVOS, list); 175 //处理数据 176 List<PlanViewVO> planViewVOS = new ArrayList<>(); 177 for(PlanEntity entity : list){ 178 LocalDate expectEndDate = entity.getExpectEndAt(); 179 String year = LocalDateTimeUtils.parseYYYY(expectEndDate); 180 String month = LocalDateTimeUtils.parseMM(expectEndDate); 181 String code = year+"."+month; 182 183 PlanViewVO planViewVO = convertor.converterTOVO(entity); 184 addParam.put(code, LocalDateTimeUtils.parseMMdd(expectEndDate)); 185 planViewVOS.add((PlanViewVO) BeanAddPropertiesUtil.getTarget(planViewVO, addParam)); 186 addParam.put(code, ""); 187 } 188 planViewDTO.setTitles(titleVOS); 189 planViewDTO.setDatas(planViewVOS); 190 planViewDTO.setTemporary(addParam); 191 return planViewDTO; 192 }
所用实体类信息
1 /** 2 * 3 * @author wqqing 4 * @date 2022/05/24 17:20 5 **/ 6 @Data 7 @NoArgsConstructor 8 @TableName(value = "sys_plan", keepGlobalPrefix = false) 9 @Validated 10 public class PlanEntity extends Model<PlanEntity> { 11 12 @TableId 13 private Long id; 14 15 private String name; 16 17 private String description; 18 19 private Integer seqno; 20 21 private PlanStatus status; 22 23 private LocalDate expectStartAt; 24 25 private LocalDate expectEndAt; 26 27 }
1 /** 2 * @author wqqing 3 * @date 2022/5/26 10:42 4 */ 5 @Data 6 public class PlanViewDTO { 7 @ApiModelProperty(value = "表格数据") 8 List<PlanViewVO> datas; 9 @ApiModelProperty(value = "表头信息") 10 List<TitleVO> titles; 11 @JsonIgnore 12 Map<String, Object> temporary; 13 }
1 /** 2 * @author wqqing 3 * @date 2022/5/26 10:55 4 */ 5 @Data 6 public class TitleVO extends CodeName{ 7 8 private List<CodeName> subTitle; 9 }
1 @Data 2 public class codeName{ 3 protected String code; 4 protected String name; 5 6 }
1 import org.apache.commons.lang3.StringUtils; 2 import org.apache.poi.ss.usermodel.*; 3 import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap; 4 import org.apache.poi.xssf.usermodel.XSSFColor; 5 6 import java.awt.Color; 7 8 /** 9 *@author wqqing 10 *@date 2022/5/25 10:36 11 * 样式信息类(Builder构建者模式) 12 */ 13 14 public class CellStyleModel { 15 /** 16 * sheet名称 17 */ 18 private String sheetName; 19 /** 20 * 列索引 21 */ 22 private int colIndex; 23 /** 24 * 行索引 25 */ 26 private int rowIndex; 27 /** 28 * 字体名称 29 */ 30 private String fontName; 31 /** 32 * 字体大小 33 */ 34 private Double fontHeight; 35 /** 36 * 字体颜色 37 */ 38 private Object fontColor; 39 /** 40 * 字体加粗 41 */ 42 private Boolean fontBold; 43 44 /** 45 * 背景颜色 46 */ 47 private Object backgroundColor; 48 49 public String getSheetName() { 50 return sheetName; 51 } 52 53 public int getColIndex() { 54 return colIndex; 55 } 56 57 public int getRowIndex() { 58 return rowIndex; 59 } 60 61 public String getFontName() { 62 return fontName; 63 } 64 65 public Double getFontHeight() { 66 return fontHeight; 67 } 68 69 public Object getFontColor() { 70 return fontColor; 71 } 72 73 public Boolean getFontBold() { 74 return fontBold; 75 } 76 77 public Object getBackgroundColor() { 78 return backgroundColor; 79 } 80 81 private CellStyleModel(Builder builder) { 82 this.sheetName = builder.sheetName; 83 this.colIndex = builder.colIndex; 84 this.rowIndex = builder.rowIndex; 85 this.fontName = builder.fontName; 86 this.fontHeight = builder.fontHeight; 87 this.fontColor = builder.fontColor; 88 this.fontBold = builder.fontBold; 89 this.backgroundColor = builder.backgroundColor; 90 } 91 92 public static class Builder { 93 /** 94 * sheet名称 95 */ 96 private String sheetName; 97 /** 98 * 列索引 99 */ 100 private int colIndex; 101 /** 102 * 行索引 103 */ 104 private int rowIndex; 105 /** 106 * 字体名称 107 */ 108 private String fontName; 109 /** 110 * 字体大小 111 */ 112 private Double fontHeight; 113 /** 114 * 字体加粗 115 */ 116 private Boolean fontBold; 117 /** 118 * 字体颜色 119 */ 120 private Object fontColor; 121 /** 122 * 背景颜色 123 */ 124 private Object backgroundColor; 125 126 public Builder(String sheetName, int colIndex, int rowIndex) { 127 this.sheetName = sheetName; 128 this.colIndex = colIndex; 129 this.rowIndex = rowIndex; 130 } 131 132 133 public Builder fontName(String fontName) { 134 this.fontName = fontName; 135 return this; 136 } 137 138 public Builder fontHeight(Double fontHeight) { 139 this.fontHeight = fontHeight; 140 return this; 141 } 142 143 public Builder fontColor(Object fontColor) { 144 this.fontColor = fontColor; 145 return this; 146 } 147 148 public Builder backgroundColor(Object backgroundColor) { 149 this.backgroundColor = backgroundColor; 150 return this; 151 } 152 153 public Builder fontBold(Boolean fontBold){ 154 this.fontBold = fontBold; 155 return this; 156 } 157 158 public CellStyleModel build() { 159 return new CellStyleModel(this); 160 } 161 } 162 163 164 /** 165 * 生成字体名称样式信息 166 * 167 * @param sheetName sheet页名称 168 * @param rowIndex 行号 169 * @param columnIndex 列号 170 * @param fontName 字体名称（默认宋体） 171 * @return 172 */ 173 public static CellStyleModel createFontNameCellStyleModel(String sheetName, int rowIndex, int columnIndex, String fontName) { 174 return createFontCellStyleModel(sheetName, rowIndex, columnIndex, fontName, null, null, null); 175 } 176 177 /** 178 * 生成字体名称大小信息 179 * 180 * @param sheetName sheet页名称 181 * @param rowIndex 行号 182 * @param columnIndex 列号 183 * @param fontHeight 字体大小 184 * @return 185 */ 186 public static CellStyleModel createFontHeightCellStyleModel(String sheetName, int rowIndex, int columnIndex 187 , Double fontHeight) { 188 return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, fontHeight, null, null); 189 } 190 191 /** 192 * 得到RBG自定义颜色 193 * 194 * @param redNum 红色数值 195 * @param greenNum 绿色数值 196 * @param blueNum 蓝色数值 197 * @return 198 */ 199 public static XSSFColor getRGBColor(int redNum, int greenNum, int blueNum) { 200 XSSFColor color = new XSSFColor(new byte[]{(byte) redNum, (byte) greenNum, (byte) blueNum}, new DefaultIndexedColorMap()); 201 return color; 202 } 203 /** 204 * 得到RBG自定义颜色 205 * @param rgbColor(red, green, blue) 206 * @return 207 */ 208 public static XSSFColor getRGBColor(String rgbColor) { 209 rgbColor = rgbColor.substring(4, rgbColor.length()-1); 210 String[] colors = rgbColor.split(","); 211 XSSFColor color = new XSSFColor(new Color(Integer.valueOf(colors[0].trim()), Integer.valueOf(colors[1].trim()), Integer.valueOf(colors[2].trim())), new DefaultIndexedColorMap()); 212 return color; 213 } 214 215 /** 216 * 生成字体颜色样式信息（支持自定义RGB颜色） 217 * 218 * @param sheetName sheet页名称 219 * @param rowIndex 行号 220 * @param columnIndex 列号 221 * @param redNum 红色数值 222 * @param greenNum 绿色数值 223 * @param blueNum 蓝色数值 224 * @return 225 */ 226 public static CellStyleModel createFontColorCellStyleModel(String sheetName, int rowIndex, int columnIndex 227 , int redNum, int greenNum, int blueNum) { 228 XSSFColor fontColor = getRGBColor(redNum, greenNum, blueNum); 229 return createFontColorCellStyleModel(sheetName, rowIndex, columnIndex, fontColor); 230 } 231 232 /** 233 * 生成字体颜色样式信息 234 * 235 * @param sheetName sheet页名称 236 * @param rowIndex 行号 237 * @param columnIndex 列号 238 * @param fontColor 字体颜色 239 * @return 240 */ 241 public static CellStyleModel createFontColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object fontColor) { 242 return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, fontColor, null); 243 } 244 245 /** 246 * 生成字体加粗样式信息 247 * 248 * @param sheetName sheet页名称 249 * @param rowIndex 行号 250 * @param columnIndex 列号 251 * @param fontBold 字体加粗 252 * @return 253 */ 254 public static CellStyleModel createFontBoldCellStyleModel(String sheetName, int rowIndex, int columnIndex, Boolean fontBold) { 255 return createFontCellStyleModel(sheetName, rowIndex, columnIndex, null, null, fontBold, null); 256 } 257 258 259 /** 260 * 生成字体样式信息 261 * 262 * @param sheetName sheet页名称 263 * @param rowIndex 行号 264 * @param columnIndex 列号 265 * @param fontName 字体名称（默认宋体） 266 * @param fontHeight 字体大小 267 * @param fontColor 字体颜色 268 * @param fontBold 字体加粗 269 * @return 270 */ 271 public static CellStyleModel createFontCellStyleModel(String sheetName, int rowIndex, int columnIndex 272 , String fontName, Double fontHeight, Object fontColor, Boolean fontBold) { 273 return createCellStyleModel(sheetName, rowIndex, columnIndex, fontName, fontHeight, fontColor, fontBold, null); 274 } 275 276 /** 277 * 生成背景颜色样式信息 278 * 279 * @param sheetName sheet页名称 280 * @param rowIndex 行号 281 * @param columnIndex 列号 282 * @param backgroundColor 背景颜色 283 * @return 284 */ 285 public static CellStyleModel createBackgroundColorCellStyleModel(String sheetName, int rowIndex, int columnIndex, Object backgroundColor) { 286 return createCellStyleModel(sheetName, rowIndex, columnIndex, null, null, null, null, backgroundColor); 287 } 288 289 /** 290 * 生成背景颜色样式信息（支持自定义RGB颜色） 291 * 292 * @param sheetName sheet页名称 293 * @param rowIndex 行号 294 * @param columnIndex 列号 295 * @param redNum 红色数值 296 * @param greenNum 绿色数值 297 * @param blueNum 蓝色数值 298 * @return 299 */ 300 public static CellStyleModel createBackgroundColorCellStyleModel(String sheetName, int rowIndex, int columnIndex 301 , int redNum, int greenNum, int blueNum) { 302 XSSFColor backgroundColor = getRGBColor(redNum, greenNum, blueNum); 303 return createBackgroundColorCellStyleModel(sheetName, rowIndex, columnIndex, backgroundColor); 304 } 305 306 /** 307 * 生成样式信息 308 * 309 * @param sheetName sheet页名称 310 * @param rowIndex 行号 311 * @param columnIndex 列号 312 * @param fontName 字体名称（宋体） 313 * @param fontHeight 字体大小 314 * @param fontColor 字体颜色 315 * @param fontBold 字体加粗 316 * @param backgroundColor 背景颜色 317 * @return 318 */ 319 public static CellStyleModel createCellStyleModel(String sheetName, int rowIndex, int columnIndex 320 , String fontName, Double fontHeight, Object fontColor, Boolean fontBold,Object backgroundColor) { 321 return new Builder(sheetName, columnIndex, rowIndex) 322 //设置字体样式 323 //字体名称（比如宋体） 324 .fontName(StringUtils.isEmpty(fontName) ? "宋体" : fontName) 325 //字体大小 326 .fontHeight(fontHeight != null && fontHeight <= 0 ? null : fontHeight) 327 //字体颜色 328 .fontColor(fontColor != null && (fontColor instanceof IndexedColors == false && fontColor instanceof XSSFColor == false) 329 ? null : fontColor) 330 //字体加粗 331 .fontBold(fontBold) 332 //背景颜色 333 .backgroundColor(backgroundColor != null && (backgroundColor instanceof IndexedColors == false && backgroundColor instanceof XSSFColor == false) 334 ? null : backgroundColor) 335 .build(); 336 } 337 } 338 //这段代码是网上搜的 原作者https://blog.csdn.net/qq_38974638/article/details/114841208
表格样式相关处理类
1 import com.alibaba.excel.write.handler.RowWriteHandler; 2 import com.alibaba.excel.write.metadata.holder.*; 3 import org.apache.commons.lang3.StringUtils; 4 import org.apache.poi.ss.usermodel.*; 5 import org.apache.poi.xssf.usermodel.*; 6 import org.springframework.util.CollectionUtils; 7 8 import java.util.*; 9 import java.util.stream.Collectors; 10 11 /** 12 * @author wqqing 13 * @date 2022/5/25 10:36 14 * 自定义单元格样式处理器（支持字体样式、背景颜色） 15 */ 16 public class CustomCellStyleHandler implements RowWriteHandler { 17 /** 18 * sheet页名称列表 19 */ 20 private List<String> sheetNameList; 21 /** 22 * 样式信息 23 */ 24 private List<CellStyleModel> cellStyleList = new ArrayList<>(); 25 26 /** 27 * 自定义样式适配器构造方法 28 * 29 * @param cellStyleList 样式信息 30 */ 31 public CustomCellStyleHandler(List<CellStyleModel> cellStyleList) { 32 if (CollectionUtils.isEmpty(cellStyleList)) { 33 return; 34 } 35 cellStyleList = cellStyleList.stream().filter(x -> x != null 36 //判断sheet名称KEY是否存在 37 && StringUtils.isNotBlank(x.getSheetName()) 38 //字体样式 39 //判断字体颜色KEY是否存在 40 && (x.getFontColor() == null || x.getFontColor() instanceof IndexedColors 41 || x.getFontColor() instanceof XSSFColor) 42 //判断背景颜色KEY是否存在 43 && (x.getBackgroundColor() == null || x.getBackgroundColor() instanceof IndexedColors 44 || x.getBackgroundColor() instanceof XSSFColor) 45 ).collect(Collectors.toList()); 46 this.cellStyleList = cellStyleList; 47 sheetNameList = this.cellStyleList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList()); 48 } 49 50 @Override 51 public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row 52 , Integer relativeRowIndex, Boolean isHead) { 53 Integer rowNum = row.getRowNum(); 54 Sheet sheet = writeSheetHolder.getSheet(); 55 //不需要添加样式，或者当前sheet页不需要添加样式 56 if (cellStyleList == null || cellStyleList.size() <= 0 || sheetNameList.contains(sheet.getSheetName()) == false) { 57 return; 58 } 59 //获取当前行的样式信息 60 List<CellStyleModel> rowCellStyleList = new ArrayList<>(); 61 for(CellStyleModel model : cellStyleList){ 62 String sheetName = model.getSheetName(); 63 Integer rowIndex = model.getRowIndex(); 64 if (sheetName.equals(sheet.getSheetName()) && rowIndex.equals(rowNum)){ 65 rowCellStyleList.add(model); 66 } 67 } 68 // List<CellStyleModel> rowCellStyleList = cellStyleList.stream().filter(x -> 69 // StringUtils.equals(x.getSheetName(), sheet.getSheetName()) && x.getRowIndex() == relativeRowIndex).collect(Collectors.toList()); 70 //该行不需要设置样式 71 if (rowCellStyleList == null || rowCellStyleList.size() <= 0) { 72 return; 73 } 74 for (CellStyleModel cellStyleModel : rowCellStyleList) { 75 //设置单元格样式 76 setCellStyle(cellStyleModel, row); 77 } 78 //删除已添加的样式信息 79 cellStyleList.removeAll(rowCellStyleList); 80 //重新获取要添加的sheet页姓名 81 sheetNameList = cellStyleList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList()); 82 } 83 84 /** 85 * 给单元格设置样式 86 * 87 * @param cellStyleModel 样式信息 88 * @param row 行对象 89 */ 90 private void setCellStyle(CellStyleModel cellStyleModel, Row row) { 91 //背景颜色 92 Object backgroundColor = cellStyleModel.getBackgroundColor(); 93 //列索引 94 int colIndex = cellStyleModel.getColIndex(); 95 //边框样式 96 Cell cell = row.getCell(colIndex); 97 if (cell == null) { 98 cell = row.createCell(colIndex); 99 } 100 XSSFCellStyle style = (XSSFCellStyle) cell.getRow().getSheet().getWorkbook().createCellStyle(); 101 // 克隆出一个 style 102 style.cloneStyleFrom(cell.getCellStyle()); 103 //设置背景颜色 104 if (backgroundColor != null) { 105 //使用IndexedColors定义的颜色 106 if (backgroundColor instanceof IndexedColors) { 107 style.setFillForegroundColor(((IndexedColors) backgroundColor).getIndex()); 108 } 109 //使用自定义的RGB颜色 110 else if (backgroundColor instanceof XSSFColor) { 111 style.setFillForegroundColor((XSSFColor) backgroundColor); 112 } 113 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 114 } 115 //设置字体样式 116 setFontStyle(row, style, cellStyleModel); 117 cell.setCellStyle(style); 118 } 119 120 /** 121 * 设置字体样式 122 * 123 * @param row 行对象 124 * @param style 单元格样式 125 * @param cellStyleModel 样式信息 126 */ 127 private void setFontStyle(Row row, XSSFCellStyle style, CellStyleModel cellStyleModel) { 128 //字体名称 129 String fontName = cellStyleModel.getFontName(); 130 //字体大小 131 Double fontHeight = cellStyleModel.getFontHeight(); 132 //字体颜色 133 Object fontColor = cellStyleModel.getFontColor(); 134 //字体加粗 135 Boolean fontBold = cellStyleModel.getFontBold(); 136 //不需要设置字体样式 137 if (fontName == null && fontHeight == null && fontColor == null && fontBold == null) { 138 return; 139 } 140 XSSFFont font = null; 141 //样式存在字体对象时，使用原有的字体对象 142 if (style.getFontIndex() != 0) { 143 font = style.getFont(); 144 } 145 //样式不存在字体对象时，创建字体对象 146 else { 147 font = (XSSFFont) row.getSheet().getWorkbook().createFont(); 148 //默认字体为宋体 149 font.setFontName("宋体"); 150 } 151 //设置字体名称 152 if (fontName != null) { 153 font.setFontName(fontName); 154 } 155 //设置字体大小 156 if (fontHeight != null) { 157 font.setFontHeight(fontHeight); 158 } 159 //设置字体颜色 160 if (fontColor != null) { 161 //使用IndexedColors定义的颜色 162 if (fontColor instanceof IndexedColors) { 163 font.setColor(((IndexedColors) fontColor).getIndex()); 164 } 165 //使用自定义的RGB颜色 166 else if (fontColor instanceof XSSFColor) { 167 font.setColor((XSSFColor) fontColor); 168 } 169 } 170 //设置字体加粗 171 if (fontBold != null) { 172 font.setBold(fontBold); 173 } 174 style.setFont(font); 175 } 176 //源码出自 https://blog.csdn.net/qq_38974638/article/details/114841208
1 import com.alibaba.excel.metadata.Head; 2 import com.alibaba.excel.metadata.data.WriteCellData; 3 import com.alibaba.excel.write.handler.CellWriteHandler; 4 import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; 5 import com.alibaba.excel.write.metadata.holder.WriteTableHolder; 6 import lombok.Data; 7 import org.apache.poi.ss.usermodel.Cell; 8 import org.apache.poi.ss.usermodel.CellType; 9 import org.apache.poi.ss.usermodel.Sheet; 10 import org.apache.poi.ss.util.CellRangeAddress; 11 12 import java.util.List; 13 14 /** 15 * 表头合并规则 16 * @author wqqing 17 * @date 2022/5/30 9:48 18 */ 19 @Data 20 public class HeadMergeStrategy implements CellWriteHandler { 21 22 private Integer headSize; 23 public HeadMergeStrategy(Integer headSize){ 24 this.headSize = headSize; 25 } 26 27 @Override 28 public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 29 mergerHead(cell); 30 } 31 /** 32 * 头部单元格合并 规则：先垂直合并，再水平合并 33 * @param cell 34 */ 35 private void mergerHead(Cell cell) { 36 //当前表格行数 37 int currentRow = cell.getRowIndex(); 38 if (currentRow > headSize-1) return; 39 //当前表格列数 40 int currentCol = cell.getColumnIndex(); 41 Sheet sheet = cell.getSheet(); 42 //第一行只左右合并 43 //当前表格数据 44 Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); 45 if(currentRow == 0 && currentCol != 0){ 46 //获取当前单元格的左边一个单元格中的数据 47 Cell leftCell = sheet.getRow(currentRow).getCell(currentCol - 1); 48 Object leftData = leftCell.getCellType() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue(); 49 if (curData.equals(leftData)){ 50 CellRangeAddress cellRangeAddress = new CellRangeAddress(currentRow, currentRow, currentCol-1, currentCol); 51 sheet.addMergedRegionUnsafe(cellRangeAddress); 52 } 53 }else{ 54 //先上下合并 55 //垂直上一表格数据 56 int currentUpPreRow = currentRow -1; 57 if (currentUpPreRow < 0) return; 58 Cell upCell = sheet.getRow(currentUpPreRow).getCell(currentCol); 59 Object upData = upCell.getCellType() == CellType.STRING ? upCell.getStringCellValue() : upCell.getNumericCellValue(); 60 if (curData.equals(upData)){//上下合并 61 CellRangeAddress cellRangeAddress = new CellRangeAddress(currentUpPreRow, currentRow, currentCol, currentCol); 62 sheet.addMergedRegionUnsafe(cellRangeAddress); 63 } 64 //左右合并时 需要判断上一级 65 //获取当前单元格的左边一个单元格中的数据 66 int currentLeftCol = currentCol - 1; 67 if (currentLeftCol < 0) return; 68 Cell leftCell = sheet.getRow(currentRow).getCell(currentLeftCol); 69 Object leftData = leftCell.getCellType() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue(); 70 //左上表格数据 71 Cell leftUpCell = sheet.getRow(currentUpPreRow).getCell(currentLeftCol); 72 Object leftUpData = leftUpCell.getCellType() == CellType.STRING ? leftUpCell.getStringCellValue() : leftUpCell.getNumericCellValue(); 73 if(upData.equals(leftUpData)){ 74 if (curData.equals(leftData)){ 75 CellRangeAddress cellRangeAddress = new CellRangeAddress(currentRow, currentRow, currentLeftCol, currentCol); 76 sheet.addMergedRegionUnsafe(cellRangeAddress); 77 } 78 } 79 } 80 81 82 } 83 }
1 import com.alibaba.excel.metadata.Head; 2 import com.alibaba.excel.metadata.data.WriteCellData; 3 import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; 4 import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; 5 import org.apache.poi.ss.usermodel.Cell; 6 7 import java.util.List; 8 9 /** 10 * @author wqqing 11 * @date 2022/5/27 16:22 12 */ 13 public class HeadCustomerWidthStrategy extends AbstractColumnWidthStyleStrategy { 14 private static final int MAX_COLUMN_WIDTH = 255; 15 @Override 16 protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 17 if (isHead) { 18 String str = cell.getStringCellValue(); 19 int columnWidth = str.getBytes().length; 20 if (columnWidth > MAX_COLUMN_WIDTH) { 21 columnWidth = MAX_COLUMN_WIDTH; 22 } else { 23 if (columnWidth <= str.length()){ 24 columnWidth = columnWidth * 8; 25 }else{ 26 columnWidth = columnWidth + 3; 27 } 28 29 } 30 writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); 31 32 33 34 } 35 36 } 37 }
工具类
1 import org.springframework.cglib.beans.BeanGenerator; 2 import org.springframework.cglib.beans.BeanMap; 3 4 import java.util.Map; 5 6 /** 7 * @author wqqing 8 * @date 2022/5/26 10:01 9 * 这段代码是网上找的 但是不记得作者了 作者看到了 介意就删除 10 */ 11 public class DynamicBean { 12 /** 13 * 目标对象 14 */ 15 private Object target; 16 17 /** 18 * 属性集合 19 */ 20 private BeanMap beanMap; 21 22 public DynamicBean(Class superclass, Map<String, Class> propertyMap) { 23 this.target = generateBean(superclass, propertyMap); 24 this.beanMap = BeanMap.create(this.target); 25 } 26 27 /** 28 * bean 添加属性和值 29 * 30 * @param property 31 * @param value 32 */ 33 public void setValue(String property, Object value) { 34 beanMap.put(property, value); 35 } 36 37 /** 38 * 获取属性值 39 * 40 * @param property 41 * @return 42 */ 43 public Object getValue(String property) { 44 return beanMap.get(property); 45 } 46 47 /** 48 * 获取对象 49 * 50 * @return 51 */ 52 public Object getTarget() { 53 return this.target; 54 } 55 56 57 /** 58 * 根据属性生成对象 59 * 60 * @param superclass 61 * @param propertyMap 62 * @return 63 */ 64 private Object generateBean(Class superclass, Map<String, Class> propertyMap) { 65 BeanGenerator generator = new BeanGenerator(); 66 if (null != superclass) { 67 generator.setSuperclass(superclass); 68 } 69 BeanGenerator.addProperties(generator, propertyMap); 70 return generator.create(); 71 } 72 }
1 import org.apache.commons.beanutils.PropertyUtilsBean; 2 3 import java.beans.PropertyDescriptor; 4 import java.util.HashMap; 5 import java.util.Map; 6 7 /** 8 * 给类添加数据属性 9 * @author wqqing 10 * @date 2022/5/26 10:02 11 * 这段代码网上找的 不记得作者了 介意删除 12 */ 13 public class BeanAddPropertiesUtil { 14 public static Object getTarget(Object dest, Map<String, Object> addProperties) { 15 // get property map 16 PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean(); 17 PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(dest); 18 Map<String, Class> propertyMap = new HashMap<>(); 19 for (PropertyDescriptor d : descriptors) { 20 if (!"class".equalsIgnoreCase(d.getName())) { 21 propertyMap.put(d.getName(), d.getPropertyType()); 22 } 23 } 24 // add extra properties 25 addProperties.forEach((k, v) -> propertyMap.put(k, v.getClass())); 26 // new dynamic bean 27 DynamicBean dynamicBean = new DynamicBean(dest.getClass(), propertyMap); 28 // add old value 29 propertyMap.forEach((k, v) -> { 30 try { 31 // filter extra properties 32 if (!addProperties.containsKey(k)) { 33 dynamicBean.setValue(k, propertyUtilsBean.getNestedProperty(dest, k)); 34 } 35 } catch (Exception e) { 36 e.printStackTrace(); 37 } 38 }); 39 // add extra value 40 addProperties.forEach((k, v) -> { 41 try { 42 dynamicBean.setValue(k, v); 43 } catch (Exception e) { 44 e.printStackTrace(); 45 } 46 }); 47 Object target = dynamicBean.getTarget(); 48 return target; 49 50 } 51 }
1 import java.time.LocalDate; 2 import java.time.format.DateTimeFormatter; 3 4 /** 5 * @author wqqing 6 * @date 2022/5/26 13:53 7 */ 8 public class LocalDateTimeUtils { 9 10 /** 11 * 获取年份 12 * @param time 13 * @return 14 */ 15 public static String parseYYYY(LocalDate time){ 16 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy"); 17 return formatter.format(time); 18 } 19 20 /** 21 * 获取月份 22 * @param time 23 * @return 24 */ 25 public static String parseMM(LocalDate time){ 26 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM"); 27 return formatter.format(time); 28 } 29 30 /** 31 * 获取月/日 32 * @param time 33 * @return 34 */ 35 public static String parseMMdd(LocalDate time){ 36 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM/dd"); 37 return formatter.format(time); 38 } 39 40 /** 41 * 获取年/月 42 * @param time 43 * @return 44 */ 45 public static String parseYYYYMM(LocalDate time){ 46 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy/MM"); 47 return formatter.format(time); 48 } 49 50 /** 51 * 获取日 52 * @param time 53 * @return 54 */ 55 public static String parseDD(LocalDate time){ 56 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd"); 57 return formatter.format(time); 58 } 59 60 }
1 import com.alibaba.excel.annotation.ExcelProperty; 2 3 import java.lang.reflect.Field; 4 import java.util.HashMap; 5 import java.util.Map; 6 7 /** 8 * @author wqqing 9 * @date 2022/5/26 11:41 10 * 处理参数 11 */ 12 public class ParameterUtitles { 13 14 /** 15 * 获取Excel注解的属性信息 16 * @param clazz 17 * @return 18 */ 19 public static Map<String, String> achieveParam(Class clazz){ 20 Map<String, String> paramsData = new HashMap<>(); 21 Field[] fields = clazz.getDeclaredFields(); 22 for(Field field : fields){ 23 field.setAccessible(true); 24 ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class); 25 if (null != excelProperty){ 26 paramsData.put(field.getName(), excelProperty.value()[0]); 27 } 28 } 29 return paramsData; 30 } 31 }
至于原理 建议自己看一下EaxyExcel的官方文档，万变不离其宗
