Java导出Excel动态表头的示例详解

暗夜沉默

目录

• 前言
• 一、效果展示
• 二、代码实现
• 1.固定头实体类
• 2.动态头实现
• 3.导出动态头

前言

本文只记录大致思路以及做法，代码不进行详细输出

场景：

模板导出

1.按照模板内容类型分组(分sheet)：1.文本消息;2.文本卡片;3.富文本;4.图文

2.每个类型的动态参数不同，即为每个sheet的表头不同

一、效果展示

二、代码实现

1.固定头实体类

1. @Data
2. @ApiModel
3. @ExcelIgnoreUnannotated
4. // @HeadRowHeight(value = 50)
5. // @ContentRowHeight(value = 50)
6. @ColumnWidth(value = 20)
7. public class MsgModuleInfoDTO {
9. @ApiModelProperty(value = "模板id")
10. private Long id;
12. @ApiModelProperty(value = "模板ids")
13. private List<Long> ids;
15. @ApiModelProperty(value = "模板编码")
16. @ExcelProperty(value = "模板编码")
17. private String code;
19. @ApiModelProperty(value = "模板名称")
20. @ExcelProperty(value = "模板名称")
21. private String name;
23. @ApiModelProperty(value = "模板关联的渠道内容类型Code")
24. private String contentTypeCode;
26. @ApiModelProperty(value = "模板关联的渠道内容类型Value")
27. @ExcelProperty(value = "内容类型")
28. private String contentTypeValue;
30. @ApiModelProperty(value = "业务场景")
31. @ExcelProperty(value = "业务场景")
32. private String condition;
35. @ApiModelProperty(value = "所属应用id")
36. private Integer appId;
38. @ApiModelProperty(value = "所属应用名称")
39. @ExcelProperty(value = "所属应用")
40. private String appName;
42. @ApiModelProperty(value = "是否启用（1：启用 ；0：不启用）")
43. @ExcelProperty(value = "是否启用")
44. private Integer isEnable;
47. @ApiModelProperty(value = "app_消息跳转url")
48. private String appUrl;
50. @ApiModelProperty(value = "pc_消息跳转url")
51. private String pcUrl;
53. @ApiModelProperty(value = "模板标题")
54. @ExcelProperty(value = "模板标题")
55. private String title;
57. @ApiModelProperty(value = "模板内容")
58. @ExcelProperty(value = "模板内容")
59. private String content;
61. @ApiModelProperty(value = "富文本模板内容")
62. @ExcelProperty(value = "富文本模板内容")
63. private String richContent;
65. private MessageTemplateDynamicProperties dynamicProperties;
67. @ApiModelProperty(value = "修改时间")
68. private LocalDateTime lastUpdateTime;
70. @ApiModelProperty(value = "修改者用户ID")
71. private Long lastUpdateUser;
73. @ApiModelProperty(value = "修改者用户名称")
74. private String lastUpdateUserName;
76. @ApiModelProperty(value = "是否系统预设(1：是；0：不是)")
77. @ExcelProperty(value = "是否系统预设", converter = MsgSystemConverter.class)
78. private Integer isSystemType;
80. @ApiModelProperty(value = "模板类型编码")
81. private String msgFormCode;
83. @ApiModelProperty(value = "模板类型名称")
84. @ExcelProperty(value = "模板类型")
85. private String msgFormName;
87. }

复制代码

2.动态头实现

1. @Getter
2. @RequiredArgsConstructor(staticName = "of")
3. public class CodeAndValue {
4. private final String code;
5. private final String name;
6. private final Object value;
7. }
8. /**
9. 渠道动态配置属性数据提供接口
10. */
11. public interface DynamicPropertiesGenerator {
12. /**
13. * 获取动态配置字段信息
14. *
15. * @return List<DynamicProperties>
16. */
17. @ApiModelProperty(hidden = true)
18. @JsonIgnore
19. List<CodeAndValue> getDynamicPropertiesList();
20. }
22. @ApiModel("模板动态字段配置数据")
23. @JsonTypeInfo(use = JsonTypeInfo.Id.NAME, property = "contentType")
24. @JsonSubTypes(value = {
25. @JsonSubTypes.Type(value = MessageRichTextConfigurationDynamicProperties.class, name = "richTextMessage"),
26. @JsonSubTypes.Type(value = MessageTextConfigurationDynamicProperties.class, name = "textMessage"),
27. @JsonSubTypes.Type(value = MessageCardConfigurationDynamicProperties.class, name = "textCardMessage"),
28. @JsonSubTypes.Type(value = MessagePictureConfigurationDynamicProperties.class, name = "pictureMessage")
29. })
30. public interface MessageTemplateDynamicProperties extends DynamicPropertiesGenerator {
31. }

复制代码

MessageRichTextConfigurationDynamicProperties 富文本动态参数

1. @Getter
2. public class MessageRichTextConfigurationDynamicProperties implements MessageTemplateDynamicProperties {
3. private final List<CodeAndValue> dynamicPropertiesList;
4. @JsonIgnore
5. private final MessageRichConfiguration messageCardConfiguration;
7. @JsonCreator
8. public MessageRichTextConfigurationDynamicProperties(@JsonProperty String messagePlatformRedirectUri,
9. @JsonProperty Boolean messagePlatformRedirectWithAgileUserInfo,
10. @JsonProperty String agileAppRedirectUri,
11. @JsonProperty String agileMainRedirectUri) {
12. this.messageCardConfiguration = new MessageRichConfiguration(messagePlatformRedirectUri, messagePlatformRedirectWithAgileUserInfo, agileAppRedirectUri, agileMainRedirectUri);
13. this.dynamicPropertiesList = DynamicValueUtil.configurationToDynamicProperties(messageCardConfiguration, Mapper.values());
14. }
16. public String getMessagePlatformRedirectUri() {
17. return messageCardConfiguration.getMessagePlatformRedirectUri();
18. }
20. public Boolean getMessagePlatformRedirectWithAgileUserInfo() {
21. return messageCardConfiguration.getMessagePlatformRedirectWithAgileUserInfo();
22. }
24. public String getAgileAppRedirectUri() {
25. return messageCardConfiguration.getAgileAppRedirectUri();
26. }
28. public String getAgileMainRedirectUri() {
29. return messageCardConfiguration.getAgileMainRedirectUri();
30. }
33. @Data
34. @AllArgsConstructor
35. @NoArgsConstructor
36. public static class MessageRichConfiguration {
37. @ExcelProperty(value = "第三方平台富文本消息链接跳转地址")
38. private String messagePlatformRedirectUri;
39. @ExcelProperty(value = "第三方平台富文本消息链接跳转是否携带agile用户信息")
40. private Boolean messagePlatformRedirectWithAgileUserInfo;
41. @ExcelProperty(value = "Agile H5跳转地址")
42. private String agileAppRedirectUri;
43. @ExcelProperty(value = "Agile PC跳转地址")
44. private String agileMainRedirectUri;
45. }
47. @Getter
48. @RequiredArgsConstructor
49. enum Mapper implements DynamicValueMapper {
50. MESSAGE_PLATFORM_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getMessagePlatformRedirectUri), "第三方平台富文本消息链接跳转地址"),
52. MESSAGE_PLATFORM_REDIRECT_WITH_AGILE_USERINFO(LambdaUtil.getFieldName(MessageRichConfiguration::getMessagePlatformRedirectWithAgileUserInfo), "第三方平台富文本消息链接跳转是否携带agile用户信息"),
54. AGILE_APP_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getAgileAppRedirectUri), "Agile H5跳转地址"),
56. AGILE_MAIN_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getAgileMainRedirectUri), "Agile PC跳转地址"),
57. ;
58. private final String code;
59. private final String name;
60. }
61. }

复制代码

MessageCardConfigurationDynamicProperties 文本卡片动态参数

1. @Getter
2. @SuppressWarnings("unused")
3. public class MessageCardConfigurationDynamicProperties implements MessageTemplateDynamicProperties {
4. private final List<CodeAndValue> dynamicPropertiesList;
5. @JsonIgnore
6. private final MessageCardConfiguration messageCardConfiguration;
8. @JsonCreator
9. public MessageCardConfigurationDynamicProperties(@JsonProperty Boolean enableOauth2Link,
10. @JsonProperty String btnTxt,
11. @JsonProperty String messagePlatformRedirectUri,
12. @JsonProperty Boolean messagePlatformRedirectWithAgileUserInfo,
13. @JsonProperty String agileAppRedirectUri,
14. @JsonProperty String agileMainRedirectUri) {
15. this.messageCardConfiguration = new MessageCardConfiguration(enableOauth2Link, btnTxt, messagePlatformRedirectUri, messagePlatformRedirectWithAgileUserInfo,
16. agileAppRedirectUri, agileMainRedirectUri);
17. this.dynamicPropertiesList = DynamicValueUtil.configurationToDynamicProperties(messageCardConfiguration, Mapper.values());
18. }
20. public Boolean getEnableOauth2Link() {
21. return messageCardConfiguration.getEnableOauth2Link();
22. }
24. public String getBtnTxt() {
25. return messageCardConfiguration.getBtnTxt();
26. }
28. public String getMessagePlatformRedirectUri() {
29. return messageCardConfiguration.getMessagePlatformRedirectUri();
30. }
32. public Boolean getMessagePlatformRedirectWithAgileUserInfo() {
33. return messageCardConfiguration.getMessagePlatformRedirectWithAgileUserInfo();
34. }
36. public String getAgileAppRedirectUri() {
37. return messageCardConfiguration.getAgileAppRedirectUri();
38. }
40. public String getAgileMainRedirectUri() {
41. return messageCardConfiguration.getAgileMainRedirectUri();
42. }
44. @Data
45. @AllArgsConstructor
46. @NoArgsConstructor
47. public static class MessageCardConfiguration {
48. @ExcelProperty(value = "是否开启跳转链接")
49. private Boolean enableOauth2Link;
50. @ExcelProperty(value = "卡片消息跳转描述")
51. private String btnTxt;
52. @ExcelProperty(value = "平台跳转链接")
53. private String messagePlatformRedirectUri;
54. @ExcelProperty(value = "是否携带Agile用户信息")
55. private Boolean messagePlatformRedirectWithAgileUserInfo;
56. @ExcelProperty(value = "Agile H5跳转地址")
57. private String agileAppRedirectUri;
58. @ExcelProperty(value = "Agile PC跳转地址")
59. private String agileMainRedirectUri;
60. }
62. @Getter
63. @RequiredArgsConstructor
64. enum Mapper implements DynamicValueMapper {
65. ENABLE_OAUTH2_LINK(LambdaUtil.getFieldName(MessageCardConfiguration::getEnableOauth2Link), "跳转链接开启Oauth2授权"),
66. BTN_TXT(LambdaUtil.getFieldName(MessageCardConfiguration::getBtnTxt), "卡片消息跳转描述"),
67. MESSAGE_PLATFORM_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getMessagePlatformRedirectUri), "平台跳转链接"),
68. MESSAGE_PLATFORM_REDIRECT_WITH_AGILE_USERINFO(LambdaUtil.getFieldName(MessageCardConfiguration::getMessagePlatformRedirectWithAgileUserInfo), "是否携带Agile用户信息"),
69. AGILE_APP_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getAgileAppRedirectUri), "Agile H5 跳转路由"),
70. AGILE_MAIN_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getAgileMainRedirectUri), "Agile PC 跳转路由"),
71. ;
72. private final String code;
73. private final String name;
74. }
75. }

复制代码

3.导出动态头

1. /**
2. * 按照模板内容类型分组(分sheet)：1.文本消息;2.文本卡片;3.富文本;4.图文
3. *
4. * @param queryDto 查询条件
5. * @param response 响应
6. * @author zhumq
7. * @date 2025/01/22 14:30:17
8. */
9. @Override
10. @SneakyThrows
11. public void exportExcel(QueryMsgModuleInfoDTO queryDto, HttpServletResponse response) {
13. log.info("【模板导出】导出模板数据,传入参数[queryDto = {}]", queryDto);
15. // 获取模板数据
16. Page<MsgModuleInfoEntity> page = convertPageBean(new Paging(1, -1));
17. List<MsgModuleInfoDTO> moduleList = msgModuleInfoMapper.selectPageInfo(queryDto, page);
19. if (CollUtil.isEmpty(moduleList)) {
20. log.info("【模板导出】查询不到可导出的模板,传入参数[queryDto = {}]", JsonUtil.toJson(queryDto));
21. response.setContentType("application/json");
22. response.setCharacterEncoding("utf-8");
23. response.getWriter().println(JSONUtil.toJsonStr(ApiResult.failMessage("没有数据可以导出")));
24. return;
25. }
27. // 设置响应
28. response.setCharacterEncoding("UTF-8");
29. response.setContentType("application/vnd.ms-excel");
30. String fileName = URLEncoder.encode("消息模板_" + System.currentTimeMillis(), "UTF-8");
31. response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
33. ServletOutputStream outputStream = null;
34. ExcelWriter excelWriter = null;
36. try {
37. outputStream = response.getOutputStream();
38. excelWriter = EasyExcel.write(outputStream).build();
40. // 按内容类型分组
41. Map<String, List<MsgModuleInfoDTO>> contentMap = moduleList.stream()
42. .collect(Collectors.groupingBy(MsgModuleInfoDTO::getContentTypeCode));
44. for (Map.Entry<String, List<MsgModuleInfoDTO>> entry : contentMap.entrySet()) {
45. String contentType = entry.getKey();
46. String sheetName = MsgEnum.ContentType.getLabelByKey(contentType);
48. List<MsgModuleInfoDTO> items = entry.getValue();
50. // 动态生成表头
51. Map<String, Field> headMap = this.generateHeader(items);
52. List<List<String>> head = headMap.keySet().stream()
53. .map(Collections::singletonList)
54. .collect(Collectors.toList());
56. // 提取数据行
57. List<List<Object>> dataList = this.obtainExportData(items, headMap);
58. // 写入数据到不同的 sheet 使用动态生成的表头
59. WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
60. .head(head)
61. .registerWriteHandler(new CustomColumnWidthStyleStrategy())
62. .build();
63. excelWriter.write(dataList, writeSheet);
65. }
67. // 刷新输出流
68. outputStream.flush();
69. } catch (IOException e) {
70. log.error("导出模板数据失败", e);
71. response.reset();
72. response.setContentType("application/json");
73. response.setCharacterEncoding("utf-8");
74. response.getWriter().println(JSONUtil.toJsonStr(ApiResult.failMessage("下载文件失败")));
75. } finally {
76. // 关闭 ExcelWriter
77. if (excelWriter != null) {
78. excelWriter.finish();
79. }
80. // 关闭输出流
81. if (outputStream != null) {
82. try {
83. outputStream.close();
84. } catch (IOException e) {
85. log.error("关闭输出流失败", e);
86. }
87. }
88. }
89. }
91. /**
92. * 提取数据行
93. *
94. * @param items
95. * @param headMap
96. * @return {@link List }<{@link List }<{@link Object }>>
97. * @author zhumq
98. * @date 2025/01/22 14:59:11
99. */
100. private List<List<Object>> obtainExportData(List<MsgModuleInfoDTO> items, Map<String, Field> headMap) {
101. List<List<Object>> dataList = new ArrayList<>();
102. for (MsgModuleInfoDTO item : items) {
103. List<Object> dataListRow = new ArrayList<>();
104. // 填充固定字段
105. for (Map.Entry<String, Field> entryField : headMap.entrySet()) {
106. String fieldName = entryField.getKey();
107. Field field = entryField.getValue();
108. if (field != null) {
109. // 固定字段通过反射获取
110. try {
111. field.setAccessible(true);
112. Object value = field.get(item);
113. dataListRow.add(this.convertValue(value));
114. } catch (Exception e) {
115. log.error("反射获取字段值失败: {}", fieldName, e);
116. dataListRow.add("");
117. }
118. } else {
119. // 动态字段通过getDynamicProperties获取
120. Object value = Optional.ofNullable(item.getDynamicProperties())
121. .map(MessageTemplateDynamicProperties::getDynamicPropertiesList)
122. .orElse(Collections.emptyList())
123. .stream()
124. .filter(cv -> cv.getName().equals(fieldName))
125. .findFirst()
126. .map(CodeAndValue::getValue)
127. .orElse("");
128. dataListRow.add(this.convertValue(value));
129. }
130. }
131. dataList.add(dataListRow);
132. }
133. return dataList;
134. }
136. /**
137. * 生成Excel表头结构
138. *
139. * @param items 模板数据
140. * @return {@link Map }<{@link String }, {@link Field }>
141. * @author zhumq
142. * @date 2025/01/22 14:30:06
143. */
144. private Map<String, Field> generateHeader(List<MsgModuleInfoDTO> items) {
146. // 1. 固定字段（通过反射获取DTO的@ExcelProperty）
147. Map<String, Field> headerMap = new LinkedHashMap<>(this.getExcelHeader(MsgModuleInfoDTO.class));
149. // 2. 动态字段（直接从dynamicPropertiesList提取code）
150. if (CollUtil.isNotEmpty(items)) {
151. MsgModuleInfoDTO firstItem = items.get(0);
152. MessageTemplateDynamicProperties dynamicProperties = firstItem.getDynamicProperties();
153. if (dynamicProperties != null && CollUtil.isNotEmpty(dynamicProperties.getDynamicPropertiesList())) {
154. // 去重处理code，避免重复表头
155. dynamicProperties.getDynamicPropertiesList().stream()
156. .map(CodeAndValue::getName)
157. .distinct()
158. .forEach(name -> headerMap.putIfAbsent(name, null));
159. }
160. }
161. return headerMap;
162. }
164. /**
165. * 工具方法：获取类中带有@ExcelProperty注解的字段
166. *
167. * @param clazz 类
168. * @return {@link Map }<{@link String }, {@link Field }>
169. * @author zhumq
170. * @date 2025/01/22 14:29:55
171. */
172. private Map<String, Field> getExcelHeader(Class<?> clazz) {
173. Map<String, Field> fieldMap = new LinkedHashMap<>();
174. Field[] fields = clazz.getDeclaredFields();
175. for (Field field : fields) {
176. if (field.isAnnotationPresent(ExcelProperty.class)) {
177. ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
178. // 获取注解中的字段名称
179. fieldMap.put(excelProperty.value()[0], field);
180. }
181. }
182. return fieldMap;
183. }
185. /**
186. * 转换字段值为字符串
187. *
188. * @param value
189. * @return {@link Object }
190. * @author zhumq
191. * @date 2025/01/22 14:50:16
192. */
193. private Object convertValue(Object value) {
194. if (value instanceof Boolean) {
195. return (Boolean) value ? "是" : "否";
196. } else if (value instanceof Integer) {
197. return (Integer) value == 1 ? "是" : "否";
198. } else if (value == null) {
199. return "";
200. }
201. return value;
202. }

复制代码

到此这篇关于Java导出Excel动态表头的示例详解的文章就介绍到这了,更多相关Java导出Excel动态表头内容请搜索晓枫资讯以前的文章或继续浏览下面的相关文章希望大家以后多多支持晓枫资讯！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

雅法海

感谢楼主分享。

温哥华月光屎

路过，支持一下