Java导出Excel动态表头的示例详解，
分享于 点击 44230 次 点评：70
Java导出Excel动态表头的示例详解，
目录
- 前言
- 一、效果展示
- 二、代码实现
- 1.固定头实体类
- 2.动态头实现
- 3.导出动态头
前言
本文只记录大致思路以及做法，代码不进行详细输出
场景：
模板导出
1.按照模板内容类型分组(分sheet)：1.文本消息;2.文本卡片;3.富文本;4.图文
2.每个类型的动态参数不同，即为每个sheet的表头不同
一、效果展示
二、代码实现
1.固定头实体类
@Data
@ApiModel
@ExcelIgnoreUnannotated
// @HeadRowHeight(value = 50)
// @ContentRowHeight(value = 50)
@ColumnWidth(value = 20)
public class MsgModuleInfoDTO {
@ApiModelProperty(value = "模板id")
private Long id;
@ApiModelProperty(value = "模板ids")
private List<Long> ids;
@ApiModelProperty(value = "模板编码")
@ExcelProperty(value = "模板编码")
private String code;
@ApiModelProperty(value = "模板名称")
@ExcelProperty(value = "模板名称")
private String name;
@ApiModelProperty(value = "模板关联的渠道内容类型Code")
private String contentTypeCode;
@ApiModelProperty(value = "模板关联的渠道内容类型Value")
@ExcelProperty(value = "内容类型")
private String contentTypeValue;
@ApiModelProperty(value = "业务场景")
@ExcelProperty(value = "业务场景")
private String condition;
@ApiModelProperty(value = "所属应用id")
private Integer appId;
@ApiModelProperty(value = "所属应用名称")
@ExcelProperty(value = "所属应用")
private String appName;
@ApiModelProperty(value = "是否启用（1：启用 ；0：不启用）")
@ExcelProperty(value = "是否启用")
private Integer isEnable;
@ApiModelProperty(value = "app_消息跳转url")
private String appUrl;
@ApiModelProperty(value = "pc_消息跳转url")
private String pcUrl;
@ApiModelProperty(value = "模板标题")
@ExcelProperty(value = "模板标题")
private String title;
@ApiModelProperty(value = "模板内容")
@ExcelProperty(value = "模板内容")
private String content;
@ApiModelProperty(value = "富文本模板内容")
@ExcelProperty(value = "富文本模板内容")
private String richContent;
private MessageTemplateDynamicProperties dynamicProperties;
@ApiModelProperty(value = "修改时间")
private LocalDateTime lastUpdateTime;
@ApiModelProperty(value = "修改者用户ID")
private Long lastUpdateUser;
@ApiModelProperty(value = "修改者用户名称")
private String lastUpdateUserName;
@ApiModelProperty(value = "是否系统预设(1：是；0：不是)")
@ExcelProperty(value = "是否系统预设", converter = MsgSystemConverter.class)
private Integer isSystemType;
@ApiModelProperty(value = "模板类型编码")
private String msgFormCode;
@ApiModelProperty(value = "模板类型名称")
@ExcelProperty(value = "模板类型")
private String msgFormName;
}
2.动态头实现
@Getter
@RequiredArgsConstructor(staticName = "of")
public class CodeAndValue {
private final String code;
private final String name;
private final Object value;
}
/**
渠道动态配置属性数据提供接口
*/
public interface DynamicPropertiesGenerator {
/**
* 获取动态配置字段信息
*
* @return List<DynamicProperties>
*/
@ApiModelProperty(hidden = true)
@JsonIgnore
List<CodeAndValue> getDynamicPropertiesList();
}
@ApiModel("模板动态字段配置数据")
@JsonTypeInfo(use = JsonTypeInfo.Id.NAME, property = "contentType")
@JsonSubTypes(value = {
@JsonSubTypes.Type(value = MessageRichTextConfigurationDynamicProperties.class, name = "richTextMessage"),
@JsonSubTypes.Type(value = MessageTextConfigurationDynamicProperties.class, name = "textMessage"),
@JsonSubTypes.Type(value = MessageCardConfigurationDynamicProperties.class, name = "textCardMessage"),
@JsonSubTypes.Type(value = MessagePictureConfigurationDynamicProperties.class, name = "pictureMessage")
})
public interface MessageTemplateDynamicProperties extends DynamicPropertiesGenerator {
}
MessageRichTextConfigurationDynamicProperties 富文本动态参数
@Getter
public class MessageRichTextConfigurationDynamicProperties implements MessageTemplateDynamicProperties {
private final List<CodeAndValue> dynamicPropertiesList;
@JsonIgnore
private final MessageRichConfiguration messageCardConfiguration;
@JsonCreator
public MessageRichTextConfigurationDynamicProperties(@JsonProperty String messagePlatformRedirectUri,
@JsonProperty Boolean messagePlatformRedirectWithAgileUserInfo,
@JsonProperty String agileAppRedirectUri,
@JsonProperty String agileMainRedirectUri) {
this.messageCardConfiguration = new MessageRichConfiguration(messagePlatformRedirectUri, messagePlatformRedirectWithAgileUserInfo, agileAppRedirectUri, agileMainRedirectUri);
this.dynamicPropertiesList = DynamicValueUtil.configurationToDynamicProperties(messageCardConfiguration, Mapper.values());
}
public String getMessagePlatformRedirectUri() {
return messageCardConfiguration.getMessagePlatformRedirectUri();
}
public Boolean getMessagePlatformRedirectWithAgileUserInfo() {
return messageCardConfiguration.getMessagePlatformRedirectWithAgileUserInfo();
}
public String getAgileAppRedirectUri() {
return messageCardConfiguration.getAgileAppRedirectUri();
}
public String getAgileMainRedirectUri() {
return messageCardConfiguration.getAgileMainRedirectUri();
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class MessageRichConfiguration {
@ExcelProperty(value = "第三方平台富文本消息链接跳转地址")
private String messagePlatformRedirectUri;
@ExcelProperty(value = "第三方平台富文本消息链接跳转是否携带agile用户信息")
private Boolean messagePlatformRedirectWithAgileUserInfo;
@ExcelProperty(value = "Agile H5跳转地址")
private String agileAppRedirectUri;
@ExcelProperty(value = "Agile PC跳转地址")
private String agileMainRedirectUri;
}
@Getter
@RequiredArgsConstructor
enum Mapper implements DynamicValueMapper {
MESSAGE_PLATFORM_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getMessagePlatformRedirectUri), "第三方平台富文本消息链接跳转地址"),
MESSAGE_PLATFORM_REDIRECT_WITH_AGILE_USERINFO(LambdaUtil.getFieldName(MessageRichConfiguration::getMessagePlatformRedirectWithAgileUserInfo), "第三方平台富文本消息链接跳转是否携带agile用户信息"),
AGILE_APP_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getAgileAppRedirectUri), "Agile H5跳转地址"),
AGILE_MAIN_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getAgileMainRedirectUri), "Agile PC跳转地址"),
;
private final String code;
private final String name;
}
}
MessageCardConfigurationDynamicProperties 文本卡片动态参数
@Getter
@SuppressWarnings("unused")
public class MessageCardConfigurationDynamicProperties implements MessageTemplateDynamicProperties {
private final List<CodeAndValue> dynamicPropertiesList;
@JsonIgnore
private final MessageCardConfiguration messageCardConfiguration;
@JsonCreator
public MessageCardConfigurationDynamicProperties(@JsonProperty Boolean enableOauth2Link,
@JsonProperty String btnTxt,
@JsonProperty String messagePlatformRedirectUri,
@JsonProperty Boolean messagePlatformRedirectWithAgileUserInfo,
@JsonProperty String agileAppRedirectUri,
@JsonProperty String agileMainRedirectUri) {
this.messageCardConfiguration = new MessageCardConfiguration(enableOauth2Link, btnTxt, messagePlatformRedirectUri, messagePlatformRedirectWithAgileUserInfo,
agileAppRedirectUri, agileMainRedirectUri);
this.dynamicPropertiesList = DynamicValueUtil.configurationToDynamicProperties(messageCardConfiguration, Mapper.values());
}
public Boolean getEnableOauth2Link() {
return messageCardConfiguration.getEnableOauth2Link();
}
public String getBtnTxt() {
return messageCardConfiguration.getBtnTxt();
}
public String getMessagePlatformRedirectUri() {
return messageCardConfiguration.getMessagePlatformRedirectUri();
}
public Boolean getMessagePlatformRedirectWithAgileUserInfo() {
return messageCardConfiguration.getMessagePlatformRedirectWithAgileUserInfo();
}
public String getAgileAppRedirectUri() {
return messageCardConfiguration.getAgileAppRedirectUri();
}
public String getAgileMainRedirectUri() {
return messageCardConfiguration.getAgileMainRedirectUri();
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class MessageCardConfiguration {
@ExcelProperty(value = "是否开启跳转链接")
private Boolean enableOauth2Link;
@ExcelProperty(value = "卡片消息跳转描述")
private String btnTxt;
@ExcelProperty(value = "平台跳转链接")
private String messagePlatformRedirectUri;
@ExcelProperty(value = "是否携带Agile用户信息")
private Boolean messagePlatformRedirectWithAgileUserInfo;
@ExcelProperty(value = "Agile H5跳转地址")
private String agileAppRedirectUri;
@ExcelProperty(value = "Agile PC跳转地址")
private String agileMainRedirectUri;
}
@Getter
@RequiredArgsConstructor
enum Mapper implements DynamicValueMapper {
ENABLE_OAUTH2_LINK(LambdaUtil.getFieldName(MessageCardConfiguration::getEnableOauth2Link), "跳转链接开启Oauth2授权"),
BTN_TXT(LambdaUtil.getFieldName(MessageCardConfiguration::getBtnTxt), "卡片消息跳转描述"),
MESSAGE_PLATFORM_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getMessagePlatformRedirectUri), "平台跳转链接"),
MESSAGE_PLATFORM_REDIRECT_WITH_AGILE_USERINFO(LambdaUtil.getFieldName(MessageCardConfiguration::getMessagePlatformRedirectWithAgileUserInfo), "是否携带Agile用户信息"),
AGILE_APP_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getAgileAppRedirectUri), "Agile H5 跳转路由"),
AGILE_MAIN_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getAgileMainRedirectUri), "Agile PC 跳转路由"),
;
private final String code;
private final String name;
}
}
3.导出动态头
/**
* 按照模板内容类型分组(分sheet)：1.文本消息;2.文本卡片;3.富文本;4.图文
*
* @param queryDto 查询条件
* @param response 响应
* @author zhumq
* @date 2025/01/22 14:30:17
*/
@Override
@SneakyThrows
public void exportExcel(QueryMsgModuleInfoDTO queryDto, HttpServletResponse response) {
log.info("【模板导出】导出模板数据,传入参数[queryDto = {}]", queryDto);
// 获取模板数据
Page<MsgModuleInfoEntity> page = convertPageBean(new Paging(1, -1));
List<MsgModuleInfoDTO> moduleList = msgModuleInfoMapper.selectPageInfo(queryDto, page);
if (CollUtil.isEmpty(moduleList)) {
log.info("【模板导出】查询不到可导出的模板,传入参数[queryDto = {}]", JsonUtil.toJson(queryDto));
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(JSONUtil.toJsonStr(ApiResult.failMessage("没有数据可以导出")));
return;
}
// 设置响应
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
String fileName = URLEncoder.encode("消息模板_" + System.currentTimeMillis(), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream outputStream = null;
ExcelWriter excelWriter = null;
try {
outputStream = response.getOutputStream();
excelWriter = EasyExcel.write(outputStream).build();
// 按内容类型分组
Map<String, List<MsgModuleInfoDTO>> contentMap = moduleList.stream()
.collect(Collectors.groupingBy(MsgModuleInfoDTO::getContentTypeCode));
for (Map.Entry<String, List<MsgModuleInfoDTO>> entry : contentMap.entrySet()) {
String contentType = entry.getKey();
String sheetName = MsgEnum.ContentType.getLabelByKey(contentType);
List<MsgModuleInfoDTO> items = entry.getValue();
// 动态生成表头
Map<String, Field> headMap = this.generateHeader(items);
List<List<String>> head = headMap.keySet().stream()
.map(Collections::singletonList)
.collect(Collectors.toList());
// 提取数据行
List<List<Object>> dataList = this.obtainExportData(items, headMap);
// 写入数据到不同的 sheet 使用动态生成的表头
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
.head(head)
.registerWriteHandler(new CustomColumnWidthStyleStrategy())
.build();
excelWriter.write(dataList, writeSheet);
}
// 刷新输出流
outputStream.flush();
} catch (IOException e) {
log.error("导出模板数据失败", e);
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(JSONUtil.toJsonStr(ApiResult.failMessage("下载文件失败")));
} finally {
// 关闭 ExcelWriter
if (excelWriter != null) {
excelWriter.finish();
}
// 关闭输出流
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("关闭输出流失败", e);
}
}
}
}
/**
* 提取数据行
*
* @param items
* @param headMap
* @return {@link List }<{@link List }<{@link Object }>>
* @author zhumq
* @date 2025/01/22 14:59:11
*/
private List<List<Object>> obtainExportData(List<MsgModuleInfoDTO> items, Map<String, Field> headMap) {
List<List<Object>> dataList = new ArrayList<>();
for (MsgModuleInfoDTO item : items) {
List<Object> dataListRow = new ArrayList<>();
// 填充固定字段
for (Map.Entry<String, Field> entryField : headMap.entrySet()) {
String fieldName = entryField.getKey();
Field field = entryField.getValue();
if (field != null) {
// 固定字段通过反射获取
try {
field.setAccessible(true);
Object value = field.get(item);
dataListRow.add(this.convertValue(value));
} catch (Exception e) {
log.error("反射获取字段值失败: {}", fieldName, e);
dataListRow.add("");
}
} else {
// 动态字段通过getDynamicProperties获取
Object value = Optional.ofNullable(item.getDynamicProperties())
.map(MessageTemplateDynamicProperties::getDynamicPropertiesList)
.orElse(Collections.emptyList())
.stream()
.filter(cv -> cv.getName().equals(fieldName))
.findFirst()
.map(CodeAndValue::getValue)
.orElse("");
dataListRow.add(this.convertValue(value));
}
}
dataList.add(dataListRow);
}
return dataList;
}
/**
* 生成Excel表头结构
*
* @param items 模板数据
* @return {@link Map }<{@link String }, {@link Field }>
* @author zhumq
* @date 2025/01/22 14:30:06
*/
private Map<String, Field> generateHeader(List<MsgModuleInfoDTO> items) {
// 1. 固定字段（通过反射获取DTO的@ExcelProperty）
Map<String, Field> headerMap = new LinkedHashMap<>(this.getExcelHeader(MsgModuleInfoDTO.class));
// 2. 动态字段（直接从dynamicPropertiesList提取code）
if (CollUtil.isNotEmpty(items)) {
MsgModuleInfoDTO firstItem = items.get(0);
MessageTemplateDynamicProperties dynamicProperties = firstItem.getDynamicProperties();
if (dynamicProperties != null && CollUtil.isNotEmpty(dynamicProperties.getDynamicPropertiesList())) {
// 去重处理code，避免重复表头
dynamicProperties.getDynamicPropertiesList().stream()
.map(CodeAndValue::getName)
.distinct()
.forEach(name -> headerMap.putIfAbsent(name, null));
}
}
return headerMap;
}
/**
* 工具方法：获取类中带有@ExcelProperty注解的字段
*
* @param clazz 类
* @return {@link Map }<{@link String }, {@link Field }>
* @author zhumq
* @date 2025/01/22 14:29:55
*/
private Map<String, Field> getExcelHeader(Class<?> clazz) {
Map<String, Field> fieldMap = new LinkedHashMap<>();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
// 获取注解中的字段名称
fieldMap.put(excelProperty.value()[0], field);
}
}
return fieldMap;
}
/**
* 转换字段值为字符串
*
* @param value
* @return {@link Object }
* @author zhumq
* @date 2025/01/22 14:50:16
*/
private Object convertValue(Object value) {
if (value instanceof Boolean) {
return (Boolean) value ? "是" : "否";
} else if (value instanceof Integer) {
return (Integer) value == 1 ? "是" : "否";
} else if (value == null) {
return "";
}
return value;
}
到此这篇关于Java导出Excel动态表头的示例详解的文章就介绍到这了,更多相关Java导出Excel动态表头内容请搜索3672js教程以前的文章或继续浏览下面的相关文章希望大家以后多多支持3672js教程！
您可能感兴趣的文章:- Java利用EasyExcel解析动态表头及导出实现过程
- Java如何使用poi导入导出excel工具类
- java如何在项目中实现excel导入导出功能
- Java实现Excel导出并添加水印
- Java使用JXLS导出Excel详解
- java poi实现Excel多级表头导出方式(多级表头,复杂表头)
用户点评