EasyExcel 自定义表头，表头合并，数据合并，自定义字体背景色

功能说明: 相当于导出一个计划安排，一条计划有一条数据，按照计划的计划完成时间显示， 根据数据状态导出背景色

思路：表头不固定，自己组合数据，格式为List>, 数据也是这个格式

导出效果图

 数据库信息

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 list = this.dao.queryList(query);
  4         if(null != list && !list.isEmpty()){
  5             PlanViewDTO dto = handleData(list, generateDefaultTitles());//生成数据
  6             //获取表头
  7             List  titleVOList= dto.getTitles();
  8             List titlesCode = new ArrayList<>();
  9             List> titles = generateTitle(titleVOList, titlesCode);
 10             //样式信息
 11             List models = new ArrayList<>();
 12             //获取数据
 13             try {
 14                 String name = "计划视图信息";
 15                 List> 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> generateData(List datas, List models, Map color,
 46                                             String sheetName, List titlesCode, Map addParam)
 47             throws Exception {
 48         List> list = new ArrayList<>();
 49         for(int i=0; i){
 50             List objects = new ArrayList<>();
 51             PlanViewVO vo = datas.get(i);
 52             Integer status = vo.getStatus().getValue();
 53             for(int j=0; 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> generateTitle(List titles, List titlesCode) {
 88         List> list = new ArrayList<>();
 89         for(TitleVO titleVO : titles){
 90             String name = titleVO.getName();
 91             List 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 handleTitle(List titleVOS, List list) {
113         Map map = new HashMap<>();
114         Map> dateInfo = list.stream().collect(Collectors.toMap(e-> LocalDateTimeUtils.parseYYYY(e.getExpectEndAt()),
115                 e->{
116                  Set a1 = new HashSet<>();
117                  a1.add(LocalDateTimeUtils.parseMM(e.getExpectEndAt()));
118                  return a1;
119                 },
120                 (Set a1, Set a2)->{
121                     a1.addAll(a2);
122                     return a1;
123                 }
124 
125         ));
126         //获取二级表头信息
127         for(Map.Entry> 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 months = entry.getValue();
135             List 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 generateDefaultTitles() {
154         Map data = ParameterUtitles.achieveParam(PlanViewVO.class);
155         List list = new ArrayList<>();
156         for(Map.Entry 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 list, List titleVOS) {
172         PlanViewDTO planViewDTO = new PlanViewDTO();
173         //处理表头
174         Map addParam = handleTitle(titleVOS, list);
175         //处理数据
176         List 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 {
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 datas;
 9     @ApiModelProperty(value = "表头信息")
10     List titles;
11     @JsonIgnore
12     Map 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 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 sheetNameList;
 21     /**
 22      * 样式信息
 23      */
 24     private List cellStyleList = new ArrayList<>();
 25 
 26     /**
 27      * 自定义样式适配器构造方法
 28      *
 29      * @param cellStyleList 样式信息
 30      */
 31     public CustomCellStyleHandler(List 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 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 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> 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> 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 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 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 addProperties) {
15         // get property map
16         PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
17         PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(dest);
18         Map 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 achieveParam(Class clazz){
20         Map 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的官方文档，万变不离其宗

						  
					  
						
							


						

							
							
			easyExcel							
						



						

						
						


						

						
					
					

					
					

						
相关

						

				
						
			

																
								

										
解决docker中Easyexcel因缺少字体无法导出的问题

									
									

								 

        

								

							
							

																
								

										
POI和EasyExcel

									
									

								 

        

								

							
							

																
								

										
EasyExcel初体验

									
									

								 

        

								

							
							

																
								

										
EasyExcel导出小结：动态标题、标题格式、相同值合并

									
									

								 

        

								

							
							

																
								

										
EasyExcel · 填充Excel

									
									

								 

        

								

							
							

																
								

										
基于EasyExcel的大数据量导入并去重

									
									

								 

        

								

							
							
            
            
            
                                    
           
  			
						

					

					
				

				

														
					
					

					
									

					
									   
标签

										

										
    
										

									

										
										

									

					

					
				


			
		
		
		
			


						





	

	
一品网 冀ICP备14022925号-6