利用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;

    @Autowired
    public 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;

    @Autowired
    public ExportTestController(TestExportService testExportService) {
        this.testExportService = testExportService;
    }

    @GetMapping("/by-class")
    public void exportTest(){
        testExportService.generatorDataForClass();
    }

    @GetMapping("/by-map")
    public void exportTestMap(){
        testExportService.generatorDataForMap();
    }
}