Java導(dǎo)出Excel動(dòng)態(tài)表頭的示例詳解
前言
本文只記錄大致思路以及做法,代碼不進(jìn)行詳細(xì)輸出
場景:
模板導(dǎo)出
1.按照模板內(nèi)容類型分組(分sheet):1.文本消息;2.文本卡片;3.富文本;4.圖文
2.每個(gè)類型的動(dòng)態(tài)參數(shù)不同,即為每個(gè)sheet的表頭不同
一、效果展示
二、代碼實(shí)現(xiàn)
1.固定頭實(shí)體類
@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 = "模板標(biāo)題") @ExcelProperty(value = "模板標(biāo)題") 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 = "修改時(shí)間") 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.動(dòng)態(tài)頭實(shí)現(xiàn)
@Getter @RequiredArgsConstructor(staticName = "of") public class CodeAndValue { private final String code; private final String name; private final Object value; } /** 渠道動(dòng)態(tài)配置屬性數(shù)據(jù)提供接口 */ public interface DynamicPropertiesGenerator { /** * 獲取動(dòng)態(tài)配置字段信息 * * @return List<DynamicProperties> */ @ApiModelProperty(hidden = true) @JsonIgnore List<CodeAndValue> getDynamicPropertiesList(); } @ApiModel("模板動(dòng)態(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 富文本動(dòng)態(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 文本卡片動(dòng)態(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)出動(dòng)態(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(); // 動(dòng)態(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 使用動(dòng)態(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 { // 動(dòng)態(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. 動(dòng)態(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動(dòng)態(tài)表頭的示例詳解的文章就介紹到這了,更多相關(guān)Java導(dǎo)出Excel動(dòng)態(tài)表頭內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
淺談Java回收對象的標(biāo)記和對象的二次標(biāo)記過程
這篇文章主要介紹了淺談Java回收對象的標(biāo)記和對象的二次標(biāo)記過程的相關(guān)內(nèi)容,小編覺得還是挺不錯(cuò)的,這里給大家分享一下,需要的朋友可以參考。2017-10-10Spring?Boot與Spring?MVC?Spring對比及核心概念
這篇文章主要為大家介紹了Spring?Boot與Spring?MVC?Spring的對比以及你需要了解的核心概念,有需要的朋友可以借鑒參考下,希望能夠有所幫助2022-03-03WebUploader實(shí)現(xiàn)圖片上傳功能
這篇文章主要為大家詳細(xì)介紹了WebUploader實(shí)現(xiàn)圖片上傳功能,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2021-03-03Struts2+Hibernate實(shí)現(xiàn)數(shù)據(jù)分頁的方法
這篇文章主要介紹了Struts2+Hibernate實(shí)現(xiàn)數(shù)據(jù)分頁的方法,結(jié)合實(shí)例形式分析了Struts2結(jié)合Hibernate實(shí)現(xiàn)數(shù)據(jù)分頁的原理,步驟與相關(guān)實(shí)現(xiàn)代碼,需要的朋友可以參考下2016-03-03IDEA強(qiáng)制清除Maven緩存的實(shí)現(xiàn)示例
清除項(xiàng)目緩存是一個(gè)常見的操作,本文主要介紹了IDEA強(qiáng)制清除Maven緩存的實(shí)現(xiàn)示例,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07Spring MVC的項(xiàng)目準(zhǔn)備和連接建立方法
SpringWebMVC是基于Servlet API的Web框架,屬于Spring框架的一部分,主要用于簡化Web應(yīng)用程序的開發(fā),SpringMVC通過控制器接收請求,使用模型處理數(shù)據(jù),并通過視圖展示結(jié)果,感興趣的朋友跟隨小編一起看看吧2024-10-10詳解通過JDBC進(jìn)行簡單的增刪改查(以MySQL為例)
JDBC是用于執(zhí)行SQL語句的一類Java API,通過JDBC使得我們可以直接使用Java編程來對關(guān)系數(shù)據(jù)庫進(jìn)行操作。通過封裝,可以使開發(fā)人員使用純Java API完成SQL的執(zhí)行。2017-01-01