利用EasyExcel实现简易Excel导出

目标

通过注解形式完成对一个方法返回值的通用导出功能

工程搭建

pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.13</version></parent><groupId>com.example</groupId><artifactId>export</artifactId><version>0.0.1-SNAPSHOT</version><name>export</name><description>export</description><properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.7.6</spring-boot.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!--    EasyExcel    --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>4.0.3</version></dependency><!--    aspect    --><dependency><groupId>org.aspectj</groupId><artifactId>aspectjweaver</artifactId></dependency><!--    util      --><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.15.0</version></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-collections4</artifactId><version>4.4</version></dependency></dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-dependencies</artifactId><version>${spring-boot.version}</version><type>pom</type><scope>import</scope></dependency></dependencies></dependencyManagement><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>1.8</source><target>1.8</target><encoding>UTF-8</encoding></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><version>${spring-boot.version}</version><configuration><mainClass>com.example.export.ExportApplication</mainClass><skip>true</skip></configuration><executions><execution><id>repackage</id><goals><goal>repackage</goal></goals></execution></executions></plugin></plugins></build></project>

核心代码

CusExport

package com.example.export.annotation;import java.lang.annotation.*;/*** @author PC* 基于EasyExcel实现动态列导出*/@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface CusExport {/*** 导出数据对象*/Class<?> dataClass() default Void.class;/*** 是否是动态的** @return true 是 false 否*/boolean dynamicFlag() default false;/*** 文件名，未指定取实体类名，无实体类取 getDefaultFileName()** @return 文件名*/String fileName() default "";
}

CusExportAspect

package com.example.export.aspect;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.export.annotation.CusExport;
import com.example.export.config.ExportProperties;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;/*** @author PC* 导出切面*/
@Aspect
@Component
public class CusExportAspect {private final static Logger logger = LoggerFactory.getLogger(CusExportAspect.class);private final ExportProperties exportProperties;@Autowiredpublic CusExportAspect(ExportProperties exportProperties) {this.exportProperties = exportProperties;}@AfterReturning(pointcut = "@annotation(com.example.export.annotation.CusExport)", returning = "result")public void afterReturningAdvice(JoinPoint joinPoint, Object result) throws IOException {MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();CusExport cusExport = methodSignature.getMethod().getAnnotation(CusExport.class);HttpServletResponse response = getHttpServletResponse();// 这里URLEncoder.encode可以防止中文乱码String generatorFileName = StringUtils.isEmpty(cusExport.fileName()) ? exportProperties.getDefaultFileName() : cusExport.fileName();String fileName = URLEncoder.encode(generatorFileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");if (!cusExport.dynamicFlag()) {EasyExcel.write(response.getOutputStream(), cusExport.dataClass()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(exportProperties.getDefaultSheetName()).doWrite((Collection<?>) result);} else {this.dealSpecified(cusExport, result, fileName, response);}}private static HttpServletResponse getHttpServletResponse() {ServletRequestAttributes attr = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();assert attr != null;HttpServletResponse response = attr.getResponse();assert response != null;response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");return response;}@SuppressWarnings("unchecked")private void dealSpecified(CusExport cusExport, Object result, String fileName, HttpServletResponse response) throws IOException {if (Objects.isNull(cusExport) || Objects.isNull(result) || StringUtils.isEmpty(fileName)) {logger.error("the required field is missing");}logger.debug("the data type is not specified");List<Map<String, Object>> resultList = new ArrayList<>();if (result instanceof List) {resultList = (List<Map<String, Object>>) result;}if (CollectionUtils.isEmpty(resultList)) {logger.info("data is empty");}List<List<String>> headList = new ArrayList<>();boolean fillHeadFlag = false;for (Map<String, Object> resultItem : resultList) {if (!fillHeadFlag) {resultItem.keySet().forEach(keyCode -> headList.add(Collections.singletonList(keyCode)));fillHeadFlag = true;}}EasyExcel.write(response.getOutputStream()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())// 放入动态头.head(headList).sheet(exportProperties.getDefaultSheetName()).doWrite(convertMapToList(resultList, headList));}private List<List<Object>> convertMapToList(List<Map<String, Object>> mapList, List<List<String>> headList) {List<List<Object>> list = new ArrayList<>();for (Map<String, Object> map : mapList) {List<Object> info = new ArrayList<>();for (List<String> itemHeadList : headList) {info.add(map.get(itemHeadList.get(0)));}list.add(info);}return list;}
}

测试

测试代码

在对应方法添加@CusExport注解即可，导出实体类需指定dataClass，导出动态数据需指定dynamicFlag = true，如需调整生成的文件名，还可以指定fileName

TestExportServiceImpl

package com.example.export.app.service.impl;import com.example.export.annotation.CusExport;
import com.example.export.app.service.TestExportService;
import com.example.export.domain.entity.ExcelTest;
import org.springframework.stereotype.Component;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @author PC*/
@Component
public class TestExportServiceImpl implements TestExportService {@Override@CusExport(dataClass = ExcelTest.class)public List<ExcelTest> generatorDataForClass() {List<ExcelTest> excelTestList = new ArrayList<>();for (int j = 0; j < 100; j++) {ExcelTest excelTest = new ExcelTest();excelTest.setCode("testCode" + j);excelTest.setName("testName" + j);excelTest.setEnabledFlag(j % 2);excelTestList.add(excelTest);}return excelTestList;}@Override@CusExport(dynamicFlag = true)public List<Map<String, Object>> generatorDataForMap() {List<Map<String, Object>> excelTestList = new ArrayList<>();for (int j = 0; j < 100; j++) {Map<String, Object> item = new HashMap<>(3);item.put("code", "testCodeMap" + j);item.put("name", "testNameMap" + j);item.put("enabledFlag", j % 2);excelTestList.add(item);}return excelTestList;}
//
}

ExportTestController

package com.example.export.api.controller;import com.example.export.app.service.TestExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;/*** @author PC*/
@RestController("v1.exportTestController")
@RequestMapping("/export")
public class ExportTestController {private final TestExportService testExportService;@Autowiredpublic ExportTestController(TestExportService testExportService) {this.testExportService = testExportService;}@GetMapping("/by-class")public void exportTest(){testExportService.generatorDataForClass();}@GetMapping("/by-map")public void exportTestMap(){testExportService.generatorDataForMap();}
}