【EasyExcel】导出excel-设置动态表头并导出数据

需求背景：

       导出excel的设置某些表头动态导出(可以根据筛选条件或一些属性的数据量)，方便导出后用户查看想看的信息。

一、技术选型：

        easyExcel的原生数据处理

二、方案设计：

        根据EasyExcel支持的表头List<List<String>>手动设置,可参考Easy Excel 官网(动态表头)，本文给的可自行研究，Demo简单易懂。

三、代码实现：

3.1:pom.xml

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.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(List<T> exportData, HttpServletResponse response, Class<T> 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(List<T> exportData, HttpServletResponse response, Class<T> 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, List<List<String>> head, List<List<Object>> 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(List<T> exportData, HttpServletResponse response, Class<T> 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(List<T> exportData, HttpServletResponse response, Class<T> 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, List<List<String>> head, List<List<Object>> 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 List<String> 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获取查询到的参数List<DemoDataVO> 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 List<List<Object>> getData(List<DemoDataVO> detailList, DemoExportParam param){if (CollectionUtils.isEmpty(detailList)){return new ArrayList<>();}// 查询的动态参数List<String> choiceParam = param.getChoiceParam();return detailList.stream().map(c -> {List<Object> objectList = new LinkedList<>();objectList.add(c.getParamData1());// 设置动态表头的数据this.setChoiceParamValue(objectList, c, choiceParam);objectList.add(c.getParamData2());return objectList;}).collect(Collectors.toList());}private void setChoiceParamValue(List<Object> objectList, DemoDataVO vo, List<String> choiceParams){List<String> paramData = vo.getChoiceParamData();if (CollectionUtils.isEmpty(paramData) || CollectionUtils.isEmpty(choiceParams)){return;}// 转成mapMap<String, 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 List<List<String>> head(DemoExportParam param) {List<List<String>> headList = new ArrayList<>();addHeadItem(headList, "参数1");// 动态表头 此处是根据参数中的动态参数设置，选择多少参数就动态加多少表头 其它同理// 这里也可以自定义：比如 根据查询的数据量设置等List<String> choiceParam = param.getChoiceParam();if (!CollectionUtils.isEmpty(choiceParam)){for (String choice : choiceParam) {addHeadItem(headList, "动态头" + "-" + choice);}}addHeadItem(headList, "参数2");// .......其他表头return headList;}private static void addHeadItem(List<List<String>> headList, String value) {List<String> item = new ArrayList<>();item.add(value);headList.add(item);}}

四、总结

        本文主要通过EasyExcel实现动态表头，扩展性还可以，提供的是基本实现，方便自行研究，本文所在栏目有多个EasyExcel实现可以自行查看，如有其它问题或者好的方法，可直接留言或私信，欢迎指正。

   👍如果对你有帮助，给博主一个免费的点赞以示鼓励
欢迎各位🔎点赞👍评论收藏⭐️