Java導(dǎo)出Excel動態(tài)表頭的示例詳解
更新時間：2025年02月07日 10:18:25 作者：伏顏.
這篇文章主要為大家詳細介紹了Java導(dǎo)出Excel動態(tài)表頭的相關(guān)知識,文中的示例代碼簡潔易懂,具有一定的借鑒價值,有需要的小伙伴可以了解下
前言
本文只記錄大致思路以及做法，代碼不進行詳細輸出
場景：
模板導(dǎo)出
1.按照模板內(nèi)容類型分組(分sheet)：1.文本消息;2.文本卡片;3.富文本;4.圖文
2.每個類型的動態(tài)參數(shù)不同，即為每個sheet的表頭不同
一、效果展示
二、代碼實現(xiàn)
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 = "模板關(guān)聯(lián)的渠道內(nèi)容類型Code")
private String contentTypeCode;
@ApiModelProperty(value = "模板關(guān)聯(lián)的渠道內(nèi)容類型Value")
@ExcelProperty(value = "內(nèi)容類型")
private String contentTypeValue;
@ApiModelProperty(value = "業(yè)務(wù)場景")
@ExcelProperty(value = "業(yè)務(wù)場景")
private String condition;
@ApiModelProperty(value = "所屬應(yīng)用id")
private Integer appId;
@ApiModelProperty(value = "所屬應(yīng)用名稱")
@ExcelProperty(value = "所屬應(yīng)用")
private String appName;
@ApiModelProperty(value = "是否啟用（1：啟用 ；0：不啟用）")
@ExcelProperty(value = "是否啟用")
private Integer isEnable;
@ApiModelProperty(value = "app_消息跳轉(zhuǎn)url")
private String appUrl;
@ApiModelProperty(value = "pc_消息跳轉(zhuǎn)url")
private String pcUrl;
@ApiModelProperty(value = "模板標題")
@ExcelProperty(value = "模板標題")
private String title;
@ApiModelProperty(value = "模板內(nèi)容")
@ExcelProperty(value = "模板內(nèi)容")
private String content;
@ApiModelProperty(value = "富文本模板內(nèi)容")
@ExcelProperty(value = "富文本模板內(nèi)容")
private String richContent;
private MessageTemplateDynamicProperties dynamicProperties;
@ApiModelProperty(value = "修改時間")
private LocalDateTime lastUpdateTime;
@ApiModelProperty(value = "修改者用戶ID")
private Long lastUpdateUser;
@ApiModelProperty(value = "修改者用戶名稱")
private String lastUpdateUserName;
@ApiModelProperty(value = "是否系統(tǒng)預(yù)設(shè)(1：是；0：不是)")
@ExcelProperty(value = "是否系統(tǒng)預(yù)設(shè)", converter = MsgSystemConverter.class)
private Integer isSystemType;
@ApiModelProperty(value = "模板類型編碼")
private String msgFormCode;
@ApiModelProperty(value = "模板類型名稱")
@ExcelProperty(value = "模板類型")
private String msgFormName;
}2.動態(tài)頭實現(xiàn)
@Getter
@RequiredArgsConstructor(staticName = "of")
public class CodeAndValue {
private final String code;
private final String name;
private final Object value;
}
/**
渠道動態(tài)配置屬性數(shù)據(jù)提供接口
*/
public interface DynamicPropertiesGenerator {
/**
* 獲取動態(tài)配置字段信息
*
* @return List<DynamicProperties>
*/
@ApiModelProperty(hidden = true)
@JsonIgnore
List<CodeAndValue> getDynamicPropertiesList();
}
@ApiModel("模板動態(tài)字段配置數(shù)據(jù)")
@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 富文本動態(tài)參數(shù)
@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 = "第三方平臺富文本消息鏈接跳轉(zhuǎn)地址")
private String messagePlatformRedirectUri;
@ExcelProperty(value = "第三方平臺富文本消息鏈接跳轉(zhuǎn)是否攜帶agile用戶信息")
private Boolean messagePlatformRedirectWithAgileUserInfo;
@ExcelProperty(value = "Agile H5跳轉(zhuǎn)地址")
private String agileAppRedirectUri;
@ExcelProperty(value = "Agile PC跳轉(zhuǎn)地址")
private String agileMainRedirectUri;
}
@Getter
@RequiredArgsConstructor
enum Mapper implements DynamicValueMapper {
MESSAGE_PLATFORM_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getMessagePlatformRedirectUri), "第三方平臺富文本消息鏈接跳轉(zhuǎn)地址"),
MESSAGE_PLATFORM_REDIRECT_WITH_AGILE_USERINFO(LambdaUtil.getFieldName(MessageRichConfiguration::getMessagePlatformRedirectWithAgileUserInfo), "第三方平臺富文本消息鏈接跳轉(zhuǎn)是否攜帶agile用戶信息"),
AGILE_APP_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getAgileAppRedirectUri), "Agile H5跳轉(zhuǎn)地址"),
AGILE_MAIN_REDIRECT_URI(LambdaUtil.getFieldName(MessageRichConfiguration::getAgileMainRedirectUri), "Agile PC跳轉(zhuǎn)地址"),
;
private final String code;
private final String name;
}
}MessageCardConfigurationDynamicProperties 文本卡片動態(tài)參數(shù)
@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 = "是否開啟跳轉(zhuǎn)鏈接")
private Boolean enableOauth2Link;
@ExcelProperty(value = "卡片消息跳轉(zhuǎn)描述")
private String btnTxt;
@ExcelProperty(value = "平臺跳轉(zhuǎn)鏈接")
private String messagePlatformRedirectUri;
@ExcelProperty(value = "是否攜帶Agile用戶信息")
private Boolean messagePlatformRedirectWithAgileUserInfo;
@ExcelProperty(value = "Agile H5跳轉(zhuǎn)地址")
private String agileAppRedirectUri;
@ExcelProperty(value = "Agile PC跳轉(zhuǎn)地址")
private String agileMainRedirectUri;
}
@Getter
@RequiredArgsConstructor
enum Mapper implements DynamicValueMapper {
ENABLE_OAUTH2_LINK(LambdaUtil.getFieldName(MessageCardConfiguration::getEnableOauth2Link), "跳轉(zhuǎn)鏈接開啟Oauth2授權(quán)"),
BTN_TXT(LambdaUtil.getFieldName(MessageCardConfiguration::getBtnTxt), "卡片消息跳轉(zhuǎn)描述"),
MESSAGE_PLATFORM_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getMessagePlatformRedirectUri), "平臺跳轉(zhuǎn)鏈接"),
MESSAGE_PLATFORM_REDIRECT_WITH_AGILE_USERINFO(LambdaUtil.getFieldName(MessageCardConfiguration::getMessagePlatformRedirectWithAgileUserInfo), "是否攜帶Agile用戶信息"),
AGILE_APP_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getAgileAppRedirectUri), "Agile H5 跳轉(zhuǎn)路由"),
AGILE_MAIN_REDIRECT_URI(LambdaUtil.getFieldName(MessageCardConfiguration::getAgileMainRedirectUri), "Agile PC 跳轉(zhuǎn)路由"),
;
private final String code;
private final String name;
}
}3.導(dǎo)出動態(tài)頭
/**
* 按照模板內(nèi)容類型分組(分sheet)：1.文本消息;2.文本卡片;3.富文本;4.圖文
*
* @param queryDto 查詢條件
* @param response 響應(yīng)
* @author zhumq
* @date 2025/01/22 14:30:17
*/
@Override
@SneakyThrows
public void exportExcel(QueryMsgModuleInfoDTO queryDto, HttpServletResponse response) {
log.info("【模板導(dǎo)出】導(dǎo)出模板數(shù)據(jù),傳入?yún)?shù)[queryDto = {}]", queryDto);
// 獲取模板數(shù)據(jù)
Page<MsgModuleInfoEntity> page = convertPageBean(new Paging(1, -1));
List<MsgModuleInfoDTO> moduleList = msgModuleInfoMapper.selectPageInfo(queryDto, page);
if (CollUtil.isEmpty(moduleList)) {
log.info("【模板導(dǎo)出】查詢不到可導(dǎo)出的模板,傳入?yún)?shù)[queryDto = {}]", JsonUtil.toJson(queryDto));
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(JSONUtil.toJsonStr(ApiResult.failMessage("沒有數(shù)據(jù)可以導(dǎo)出")));
return;
}
// 設(shè)置響應(yīng)
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();
// 按內(nèi)容類型分組
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();
// 動態(tài)生成表頭
Map<String, Field> headMap = this.generateHeader(items);
List<List<String>> head = headMap.keySet().stream()
.map(Collections::singletonList)
.collect(Collectors.toList());
// 提取數(shù)據(jù)行
List<List<Object>> dataList = this.obtainExportData(items, headMap);
// 寫入數(shù)據(jù)到不同的 sheet 使用動態(tài)生成的表頭
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
.head(head)
.registerWriteHandler(new CustomColumnWidthStyleStrategy())
.build();
excelWriter.write(dataList, writeSheet);
}
// 刷新輸出流
outputStream.flush();
} catch (IOException e) {
log.error("導(dǎo)出模板數(shù)據(jù)失敗", e);
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(JSONUtil.toJsonStr(ApiResult.failMessage("下載文件失敗")));
} finally {
// 關(guān)閉 ExcelWriter
if (excelWriter != null) {
excelWriter.finish();
}
// 關(guān)閉輸出流
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("關(guān)閉輸出流失敗", e);
}
}
}
}
/**
* 提取數(shù)據(jù)行
*
* @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 {
// 動態(tài)字段通過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表頭結(jié)構(gòu)
*
* @param items 模板數(shù)據(jù)
* @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. 動態(tài)字段（直接從dynamicPropertiesList提取code）
if (CollUtil.isNotEmpty(items)) {
MsgModuleInfoDTO firstItem = items.get(0);
MessageTemplateDynamicProperties dynamicProperties = firstItem.getDynamicProperties();
if (dynamicProperties != null && CollUtil.isNotEmpty(dynamicProperties.getDynamicPropertiesList())) {
// 去重處理code，避免重復(fù)表頭
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;
}
/**
* 轉(zhuǎn)換字段值為字符串
*
* @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;
}到此這篇關(guān)于Java導(dǎo)出Excel動態(tài)表頭的示例詳解的文章就介紹到這了,更多相關(guān)Java導(dǎo)出Excel動態(tài)表頭內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
LockSupport是一種線程阻塞工具,它可以在線程內(nèi)任意位置讓線程阻塞.接下來就帶著大家詳細了解一下LockSupport類,,需要的朋友可以參考下2021-05-05
這篇文章主要介紹了淺談Java回收對象的標記和對象的二次標記過程的相關(guān)內(nèi)容，小編覺得還是挺不錯的，這里給大家分享一下，需要的朋友可以參考。2017-10-10
Spring?Boot與Spring?MVC?Spring對比及核心概念
這篇文章主要為大家介紹了Spring?Boot與Spring?MVC?Spring的對比以及你需要了解的核心概念，有需要的朋友可以借鑒參考下，希望能夠有所幫助2022-03-03
本篇文章是對java中靜態(tài)方法的重寫問題進行了詳細的分析介紹，需要的朋友參考下2013-06-06
面向?qū)ο蟮娜筇匦裕悍庋b、繼承、多態(tài)。從一定角度來看，封裝和繼承幾乎都是為多態(tài)而準備的。今天通過本文給大家介紹java多態(tài)注意項總結(jié)，感興趣的朋友一起看看吧2021-10-10
這篇文章主要為大家詳細介紹了WebUploader實現(xiàn)圖片上傳功能，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-03-03
Struts2+Hibernate實現(xiàn)數(shù)據(jù)分頁的方法
這篇文章主要介紹了Struts2+Hibernate實現(xiàn)數(shù)據(jù)分頁的方法,結(jié)合實例形式分析了Struts2結(jié)合Hibernate實現(xiàn)數(shù)據(jù)分頁的原理,步驟與相關(guān)實現(xiàn)代碼,需要的朋友可以參考下2016-03-03
清除項目緩存是一個常見的操作,本文主要介紹了IDEA強制清除Maven緩存的實現(xiàn)示例,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-07-07
SpringWebMVC是基于Servlet API的Web框架,屬于Spring框架的一部分,主要用于簡化Web應(yīng)用程序的開發(fā),SpringMVC通過控制器接收請求,使用模型處理數(shù)據(jù),并通過視圖展示結(jié)果,感興趣的朋友跟隨小編一起看看吧2024-10-10
JDBC是用于執(zhí)行SQL語句的一類Java API，通過JDBC使得我們可以直接使用Java編程來對關(guān)系數(shù)據(jù)庫進行操作。通過封裝，可以使開發(fā)人員使用純Java API完成SQL的執(zhí)行。2017-01-01