广安网站建设公司,wordpress 设置二级域名,安溪住房和城乡建设网站,营销网站建设流程需求背景#xff1a; 导出excel的设置某些表头动态导出(可以根据筛选条件或一些属性的数据量)#xff0c;方便导出后用户查看想看的信息。
一、技术选型#xff1a; easyExcel的原生数据处理
二、方案设计#xff1a; 根据EasyExcel支持的表头ListListString…需求背景 导出excel的设置某些表头动态导出(可以根据筛选条件或一些属性的数据量)方便导出后用户查看想看的信息。
一、技术选型 easyExcel的原生数据处理
二、方案设计 根据EasyExcel支持的表头ListListString手动设置,可参考Easy Excel 官网(动态表头)本文给的可自行研究Demo简单易懂。
三、代码实现
3.1:pom.xml
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.3.2/version
/dependency
3.2: API ApiOperation(value 导出)PostMapping(/export)public void export(HttpServletResponse response, RequestBody DemoExportParam param) throws BaseException {exportService.export(response, param);}
3.3通用服务层API
import com.alibaba.excel.write.handler.WriteHandler;import javax.servlet.http.HttpServletResponse;
import java.util.List;/*** author c*/
public interface IEasyExcelService {/*** 导出excel方法** param exportData 需要导出的数据* param response response* param tClass 导出excel的字段实体类* param fileName 文件名字* param sheetName sheet名字*/T void exportExcel(ListT exportData, HttpServletResponse response, ClassT tClass, String fileName, String sheetName);/*** 导出excel方法 (携带自定义策略)* param exportData 需要导出的数据* param response HttpServletResponse* param tClass 导出excel的字段实体类* param fileName 文件名字* param sheetName sheet名字* param writeHandler 自定义策略可扩展多个* param T T*/T void exportExcelWithHandler(ListT exportData, HttpServletResponse response, ClassT tClass, String fileName, String sheetName, WriteHandler writeHandler);/*** 导出excel方法(动态表头-动态数据) 携带自定义handler* param response HttpServletResponse* param head 表头* param data 数据* param fileName 文件名字* param sheetName sheet名字* param writeHandler 自定义策略可扩展多个* param T T*/T void exportExcelWithDynamicsHead(HttpServletResponse response, ListListString head, ListListObject data,String fileName, String sheetName, WriteHandler writeHandler);
}
3.4通用服务层API实现类
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.write.handler.WriteHandler;
import com.galaplat.marketing.oas.easyexcel.IEasyExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;/*** author c* Description: 文件excel处理方法* date: 2024-07-26 10:10:10*/
Service
Slf4j
public class EasyExcelServiceImpl implements IEasyExcelService {private static final String ENCODING_UTF_8 UTF-8;private static final String FILE_END .xlsx;private static final String CONTENT_DISPOSITION Content-Disposition;private static final String CONTENT_DISPOSITION_VALUE attachment;filename;private static final String ACCESS_CONTROL_EXPOSE_HEADERS Access-Control-Expose-Headers;private static final String CONTENT_TYPE application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;/*** 本地转response.setHeader(Content-disposition, attachment;filename*utf-8 fileNameEncoder .xlsx);* param exportData 需要导出的数据* param response response* param tClass 导出excel的字段实体类* param fileName 文件名字* param sheetName sheet名字* param T T*/Overridepublic T void exportExcel(ListT exportData, HttpServletResponse response, ClassT tClass, String fileName, String sheetName){// 使用swagger 会导致各种问题直接用浏览器或者用postmanresponse.setContentType(CONTENT_TYPE);response.setCharacterEncoding(ENCODING_UTF_8);try{// fileName encoderString fileNameEncoder URLEncoder.encode(fileName, ENCODING_UTF_8).replaceAll(\\, %20);response.setHeader(CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE fileNameEncoder FILE_END);response.setHeader(ACCESS_CONTROL_EXPOSE_HEADERS, CONTENT_DISPOSITION);// write to excelEasyExcelFactory.write(response.getOutputStream(), tClass).autoCloseStream(Boolean.FALSE).sheet(sheetName).doWrite(exportData);}catch (Exception e){log.error(EasyExcelServiceImpl-exportExcel error, message is :{}, e.getMessage());}}Overridepublic T void exportExcelWithHandler(ListT exportData, HttpServletResponse response, ClassT tClass,String fileName, String sheetName, WriteHandler writeHandler){// 使用swagger 会导致各种问题直接用浏览器或者用postmanresponse.setContentType(CONTENT_TYPE);response.setCharacterEncoding(ENCODING_UTF_8);try{// fileName encoderString fileNameEncoder URLEncoder.encode(fileName, ENCODING_UTF_8).replaceAll(\\, %20);response.setHeader(CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE fileNameEncoder FILE_END);response.setHeader(ACCESS_CONTROL_EXPOSE_HEADERS, CONTENT_DISPOSITION);// write to excelEasyExcelFactory.write(response.getOutputStream(), tClass).autoCloseStream(Boolean.FALSE)// 自定义策略(支持扩展多个).registerWriteHandler(writeHandler).sheet(sheetName).doWrite(exportData);}catch (Exception e){log.error(EasyExcelServiceImpl-exportExcel error, message is :{}, e.getMessage());}}Overridepublic T void exportExcelWithDynamicsHead(HttpServletResponse response, ListListString head, ListListObject data,String fileName, String sheetName, WriteHandler writeHandler){// 使用swagger 会导致各种问题直接用浏览器或者用postmanresponse.setContentType(CONTENT_TYPE);response.setCharacterEncoding(ENCODING_UTF_8);try {// fileName encoderString fileNameEncoder URLEncoder.encode(fileName, ENCODING_UTF_8).replaceAll(\\, %20);response.setHeader(CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE fileNameEncoder FILE_END);response.setHeader(ACCESS_CONTROL_EXPOSE_HEADERS, CONTENT_DISPOSITION);// write to excelEasyExcelFactory.write(response.getOutputStream()).head(head).sheet(sheetName)// handle.registerWriteHandler(writeHandler).doWrite(data);} catch (Exception e) {log.error(EasyExcelServiceImpl-exportExcelWithHandlerAndHead error, message is :{}, e.getMessage());}}}
3.5服务Demo查询参数DemoExportParam
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;import java.util.List;Data
public class DemoExportParam {ApiModelProperty(value 参数1)private String param1;ApiModelProperty(value 参数1)private String param2;ApiModelProperty(value 动态可选择参数)private ListString choiceParam;} 注意参数DemoExportParam 中的choiceParam是可以多个的选项本文中的动态表头也是根据它来实现动态表头的支持扩展(比如根据查询数据中某个字段来设置具体可以参考下面的实现代码)
3.6服务Demo方法实现层 注意其中IDemoService 是自己的服务方法可以自行修改只用来获取数据
/*** Demo Export Service Impl* author: c* date: 2024-8-15 16:46:08*/
Service
Slf4j
public class DemoExportServiceImpl {private static final String FILE_NAME_PRODUCT_DETAIL 测试动态表头;private final IDemoService service;private final IEasyExcelService excelService;public DemoExportServiceImpl(IDemoService service, IEasyExcelService excelService) {this.service service;this.excelService excelService;}public void export(HttpServletResponse response, DemoExportParam param) throws BaseException {// 根据param获取查询到的参数ListDemoDataVO pageInfoList service.listAllData(param);// export with dynamics headexcelService.exportExcelWithDynamicsHead(response, head(param), getData(pageInfoList, param),FILE_NAME_PRODUCT_DETAIL, FILE_NAME_PRODUCT_DETAIL, new FreezeRowColHandler(FreezeRowColConstant.PRODUCT_DETAIL));}public ListListObject getData(ListDemoDataVO detailList, DemoExportParam param){if (CollectionUtils.isEmpty(detailList)){return new ArrayList();}// 查询的动态参数ListString choiceParam param.getChoiceParam();return detailList.stream().map(c - {ListObject objectList new LinkedList();objectList.add(c.getParamData1());// 设置动态表头的数据this.setChoiceParamValue(objectList, c, choiceParam);objectList.add(c.getParamData2());return objectList;}).collect(Collectors.toList());}private void setChoiceParamValue(ListObject objectList, DemoDataVO vo, ListString choiceParams){ListString paramData vo.getChoiceParamData();if (CollectionUtils.isEmpty(paramData) || CollectionUtils.isEmpty(choiceParams)){return;}// 转成mapMapString, String paramDataMap paramData.stream().collect(Collectors.toMap(Function.identity(), Function.identity()));for (String choiceData : choiceParams) {// 获取对应的值String matchingValue paramDataMap.get(choiceData);// 添加 对应的值 到 objectListif (StringUtils.isNotBlank(matchingValue)) {objectList.add(matchingValue);} else {objectList.add();}}}private ListListString head(DemoExportParam param) {ListListString headList new ArrayList();addHeadItem(headList, 参数1);// 动态表头 此处是根据参数中的动态参数设置选择多少参数就动态加多少表头 其它同理// 这里也可以自定义比如 根据查询的数据量设置等ListString choiceParam param.getChoiceParam();if (!CollectionUtils.isEmpty(choiceParam)){for (String choice : choiceParam) {addHeadItem(headList, 动态头 - choice);}}addHeadItem(headList, 参数2);// .......其他表头return headList;}private static void addHeadItem(ListListString headList, String value) {ListString item new ArrayList();item.add(value);headList.add(item);}}
四、总结 本文主要通过EasyExcel实现动态表头扩展性还可以提供的是基本实现方便自行研究本文所在栏目有多个EasyExcel实现可以自行查看如有其它问题或者好的方法可直接留言或私信欢迎指正。 如果对你有帮助给博主一个免费的点赞以示鼓励 欢迎各位点赞评论收藏⭐️
