需求背景：
导出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
*/
@Override
public <T> void exportExcel(List<T> exportData, HttpServletResponse response, Class<T> tClass, String fileName, String sheetName){
// 使用swagger 会导致各种问题，直接用浏览器或者用postman
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(ENCODING_UTF_8);
try{
// fileName encoder
String 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 excel
EasyExcelFactory.write(response.getOutputStream(), tClass)
.autoCloseStream(Boolean.FALSE)
.sheet(sheetName)
.doWrite(exportData);
}catch (Exception e){
log.error("EasyExcelServiceImpl->exportExcel error, message is :{}", e.getMessage());
}
}
@Override
public <T> void exportExcelWithHandler(List<T> exportData, HttpServletResponse response, Class<T> tClass,
String fileName, String sheetName, WriteHandler writeHandler){
// 使用swagger 会导致各种问题，直接用浏览器或者用postman
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(ENCODING_UTF_8);
try{
// fileName encoder
String 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 excel
EasyExcelFactory.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());
}
}
@Override
public <T> void exportExcelWithDynamicsHead(HttpServletResponse response, List<List<String>> head, List<List<Object>> data,
String fileName, String sheetName, WriteHandler writeHandler){
// 使用swagger 会导致各种问题，直接用浏览器或者用postman
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(ENCODING_UTF_8);
try {
// fileName encoder
String 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 excel
EasyExcelFactory.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 head
excelService.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;
}
// 转成map
Map<String, String> paramDataMap = paramData.stream().collect(Collectors.toMap(Function.identity(), Function.identity()));
for (String choiceData : choiceParams) {
// 获取对应的值
String matchingValue = paramDataMap.get(choiceData);
// 添加 对应的值 到 objectList
if (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实现可以自行查看，如有其它问题或者好的方法，可直接留言或私信，欢迎指正。
👍如果对你有帮助，给博主一个免费的点赞以示鼓励
欢迎各位🔎点赞👍评论收藏⭐️
1095
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
