Skip to content

One-line code to quickly integrate Alibaba EasyExcel, achieving file import and export

License

Notifications You must be signed in to change notification settings

dk900912/easyexcel-spring-boot

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

15 Commits
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Java support License Maven Central GitHub release GitHub Stars GitHub Forks GitHub issues GitHub Contributors GitHub repo size


EasyExcel 是一款由阿里开源的 Excel 处理工具。相较于原生的Apache POI,它可以更优雅、快速地完成 Excel 的读写功能,同时更加地节约内存。即使 EasyExcel 已经很优雅了,但面向 Excel 文档的读写逻辑几乎千篇一律,笔者索性将这些模板化的逻辑抽离出来,该组件已经发布到 maven 中央仓库,感兴趣的朋友可以体验一下。

目前仅支持针对单个 Excel 文档的导入与导出(支持多Sheet),所以由 @RequestExcel 注解修饰的方法参数必须是一个List<List<>>类型,而由 @ResponseExcel 注解修饰的方法返回类型也必须是一个List<List<>>类型,否则将抛出异常。

1 快速上手

1.1 引入依赖

easyexcel 组件组要自行引入,强制使用 3.3+ 版本。

<dependency>
	<groupId>io.github.dk900912</groupId>
	<artifactId>easyexcel-spring-boot-starter</artifactId>
	<version>1.0.1</version>
</dependency>

1.2 导入与导出

@RestController
@RequestMapping(path = "/easyexcel")
public class ExcelController {

    @PostMapping(path = "/v1/upload")
    public ResponseEntity<String> v1upload(
            @RequestExcel(sheets = {
                    @Sheet(index = 0, headClazz = User.class, headRowNumber = 1),
                    @Sheet(index = 1, headClazz = User.class, headRowNumber = 1),
                    @Sheet(index = 2, headClazz = User.class, headRowNumber = 1)}
            )
            List<List<User>> users) {
        return ResponseEntity.ok("OK");
    }

    @PostMapping(path = "/v2/upload")
    public ResponseEntity<String> v2upload(
            @RequestExcel(sheets = {
                    @Sheet(index = 0, headClazz = User.class, headRowNumber = 1),
                    @Sheet(index = 1, headClazz = Admin.class, headRowNumber = 1)}
            )
            List<List<Object>> data) {
        return ResponseEntity.ok("OK");
    }

    @ResponseExcel(
            name="程序猿",
            sheets = {
                    @Sheet(name = "sheet-0", headClazz = User.class),
                    @Sheet(name = "sheet-1", headClazz = User.class),
                    @Sheet(name = "sheet-2", headClazz = User.class)
            },
            suffix = ExcelTypeEnum.XLSX)
    @GetMapping(path = "/v1/export")
    public List<List<User>> v1export() {
        List<User> userList = Lists.newArrayList();
        for (int i = 0; i < 10000; i++) {
            User user = User.builder().name("暴风赤红" + (i+1))
                    .birth(LocalDate.now()).address("江苏省苏州市科技城昆仑山路58号").sex(Sex.MALE)
                    .build();
            userList.add(user);
        }
        return ImmutableList.of(userList, userList, userList);
    }

    @ResponseExcel(
            name="程序猿",
            sheets = {
                    @Sheet(name = "sheet-0", headClazz = User.class),
                    @Sheet(name = "sheet-1", headClazz = Admin.class)
            },
            suffix = ExcelTypeEnum.XLS)
    @GetMapping(path = "/v2/export")
    public List<List<?>> v2export() {
        List<User> userList = Lists.newArrayList();
        List<Admin> adminList = Lists.newArrayList();
        for (int i = 0; i < 10000; i++) {
            User user = User.builder().name("暴风赤红" + (i+1))
                    .birth(LocalDate.now()).address("江苏省苏州市科技城昆仑山路58号").sex(Sex.MALE)
                    .build();
            userList.add(user);
            Admin admin = Admin.builder().name("擎天柱" + (i+1))
                    .birth(LocalDate.now()).address("江苏省苏州市科技城昆仑山路68号").sex(Sex.MALE)
                    .build();
            adminList.add(admin);
        }

        List<List<?>> responseData = Lists.newArrayList();
        responseData.add(userList);
        responseData.add(adminList);

        return responseData;
    }

    @ResponseExcel(name="templates/tem.xlsx", scene = TEMPLATE)
    @GetMapping(path = "/v1/template")
    public void template() {}
}

2 配置项

一般无需配置

spring.easy-excel.enabled=true
spring.easy-excel.converter.media-types[0]=application/octet-stream
spring.easy-excel.template.location=classpath:

3 导出文件名

在非模板导出场景下,导出文件名可以显式指定,也可以不指定,此时会默认使用基于 UUID 的文件名生成策略,如果不满足大家的需求,可以自行实现FileNameGenerator策略接口,然后追加配置项,如下所示:

spring.easy-excel.name.generator=a.b.c.CustomFileNameGenerator

同时内置了两个文件名生成器供大家使用,分别是:

  • io.github.dk900912.easyexcel.support.TimestampFileNameGenerator
  • io.github.dk900912.easyexcel.support.LocalDateTimeFileNameGenerator

4 Bean Validation

依托于 Spring 内部 MethodValidationInterceptor的能力,配合@Validated@Valid注解即可实现。

@Validated
@RestController
@RequestMapping(path = "/easyexcel")
public class ExcelController {

    @PostMapping(path = "/v1/upload")
    public ResponseEntity<String> v1upload(
            @RequestExcel(sheets = {
                    @Sheet(index = 0, headClazz = User.class, headRowNumber = 1),
                    @Sheet(index = 1, headClazz = User.class, headRowNumber = 1),
                    @Sheet(index = 2, headClazz = User.class, headRowNumber = 1)}
            )
            List<List<@Valid User>> users, @RequestParam("id") String id) {
        System.out.println(users);
        System.out.println(id);
        return ResponseEntity.ok("OK");
    }
}

异常信息如下:

jakarta.validation.ConstraintViolationException: v1upload.users[1].<list element>[0].name: name不能为空, v1upload.users[0].<list element>[0].name: name不能为空
	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:138) ~[spring-context-6.0.9.jar:6.0.9]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.9.jar:6.0.9]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-6.0.9.jar:6.0.9]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) ~[spring-aop-6.0.9.jar:6.0.9]
	at io.github.xiaotou.easyexcel.ExcelController$$SpringCGLIB$$0.v1upload(<generated>) ~[classes/:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:207) ~[spring-web-6.0.9.jar:6.0.9]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:152) ~[spring-web-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:884) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1081) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:974) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1011) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590) ~[tomcat-embed-core-10.1.8.jar:6.0]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.0.9.jar:6.0.9]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.8.jar:6.0]

从上述异常信息来看,出现了两次“name不能为空”,这说明 Jakarta Bean Validation 的实现方(Hibernate)没有开启fail fast机制,可以通过以下方式开启:

spring.easy-excel.validation.fail-fast=false

否则本组件将自动开启fail fast机制,如下:

public class FailFastValidationConfigurationCustomizer implements ValidationConfigurationCustomizer {
    @Override
    public void customize(Configuration<?> configuration) {
        configuration.addProperty("hibernate.validator.fail_fast", "true");
    }
}

About

One-line code to quickly integrate Alibaba EasyExcel, achieving file import and export

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages