javaEasyExcel面向Excel文档读写逻辑示例详解

2022-11-13 13:11:49 示例 读写 详解

正文

EasyExcel是一款由阿里开源的 Excel 处理工具。相较于原生的Apache POI,它可以更优雅、快速地完成 Excel 的读写功能,同时更加地节约内存。

即使 EasyExcel 已经很优雅了,但面向 Excel 文档的读写逻辑几乎千篇一律,笔者索性将这些模板化的逻辑抽离出来,该组件已经发布到 Maven 中央仓库,感兴趣的朋友可以体验一下。

1 快速上手

1.1 引入依赖

<dependency>
	<groupId>io.GitHub.dk900912</groupId>
	<artifactId>easyexcel-spring-boot-starter</artifactId>
	<version>0.0.7</version>
</dependency>

1.2 导入与导出

@RestController
@RequestMapping(path = "/easyexcel")
public class ExcelController {
    @PostMapping(path = "/v1/upload")
    public ResponseEntity<String> upload(
            @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)
            })
            @Valid List<List<User>> users) {
        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>> export() {
        List<User> data = Lists.newArrayList();
        for (int i = 0; i < 10000; i++) {
            User user = User.builder().name("暴风赤红" + (i+1))
                    .birth(LocalDate.now()).address("江苏省苏州市科技城昆仑山路58号")
                    .build();
            data.add(user);
        }
        return ImmutableList.of(data, data, data);
    }
    @ResponseExcel(name="templates/程序猿.xlsx", scene = TEMPLATE)
    @GetMapping(path = "/v1/template")
    public void template() {}
}

2 实现原理

一切 Java 程序都是基于 Thread 的,当一个 Http 请求到达后,Servlet Container 会从其线程池中捞出一个线程来处理该 HTTP 请求。具体地,该 HTTP 请求首先到达 Servlet Container 的FilterChain中;然后,FilterChain 将该 HTTP 请求委派给DispatcherServlet处理,而 DispatcherServlet 恰恰就是 Spring mvc 的门户。在 Spring MVC 中,所有 HTTP 请求都由 DispatcherServlet 进行路由分发。大致流程下图所示。

DispatcherServlet 在 HandlerMapping 的帮助下可以快速匹配到最终的 Controller,由于 Controller 大多由@RequestMapping注解标注,那么RequestMappingHandlerMapping最终脱颖而出。

RequestMappingHandlerMapping 会将 HTTP 请求映射到一个HandlerExecutionChain实例中,每一个 HandlerExecutionChain 实例的内部维护了HandlerMethodList<HandlerInterceptor>

其中,HandlerMethod 实例持有一个Object类型的 bean 变量和java.lang.reflect.Method类型的 method 变量,bean 和 method 这俩成员变量组合起来最终可以确定究竟由哪一个 Controller 中的某一方法来处理当前 HTTP 请求。

此时已经知道目标方法了,那直接反射执行目标方法?是不可以的,因为通过反射来执行目标方法需要有参数才行,此外还需要对目标方法的执行结果进行加工处理。既然 HandlerMapping 没有解析请求体和处理目标执行结果的能力,只能再引入一层适配器了,它就是 HandlerAdapter。

在 Spring MVC 所提供的若干种 HandlerAdapter 中,能够适配 HandlerMethod 的只有RequestMappingHandlerAdapter

RequestMappingHandlerAdapter 实现了InitializingBean接口,用于初始化HandlerMethodArgumentResolverComposite类型的 argumentResolvers 成员变量和HandlerMethodReturnValueHandlerComposite类型的 returnValueHandlers 成员变量,Composite 后缀表明这俩成员变量均是一种复合类,argumentResolvers 持有数十种HandlerMethodArgumentResolver类型的方法参数解析器,而 returnValueHandlers 则持有数十种HandlerMethodReturnValueHandler类型的方法返回值解析器。

重点来了!首先,我们需要一个实现 HandlerMethodArgumentResolver 接口的方法参数解析器,该解析器主要用于解析@RequestExcel注解,以读取 Excel 文档;

此外,我们还需要一个实现 HandlerMethodReturnValueHandler 接口的方法返回值解析器,该解析器主要用于解析@ResponseExcel注解,以将目标方法所返回的数据写入到 Excel 文档中;

最后,将这两个自定义的解析器分别添加到 RequestMappingHandlerAdapter 中的 argumentResolvers 与 returnValueHandlers 这俩成员变量中。

在 Spring MVC 中,由RequestResponseBodyMethodProcessor负责处理@RequestBody@ResponseBody 注解。基于这一事实,笔者也没有单独设计两个解析器来分别应对 @RequestExcel 与 @ResponseExcel 注解,而是合二为一。

2.1 @RequestExcel 与 @ResponseExcel 解析器

public class RequestResponseExcelMethodProcessor implements HandlerMethodArgumentResolver,
        HandlerMethodReturnValueHandler {
    private final ResourceLoader resourceLoader;
    public RequestResponseExcelMethodProcessor(ResourceLoader resourceLoader) {
        this.resourceLoader = resourceLoader;
    }
    @Override
    public boolean supportsParameter(MethodParameter parameter) {
        return parameter.hasParameterAnnotation(RequestExcel.class);
    }
    @Override
    public boolean supportsReturnType(MethodParameter returnType) {
        return returnType.hasMethodAnnotation(ResponseExcel.class);
    }
    @Override
    public Object resolveArgument(MethodParameter parameter, ModelAndViewContainer mavContainer,
                                  NativeWEBRequest webRequest, WebDataBinderFactory binderFactory) throws Exception {
        parameter = parameter.nestedIfOptional();
        Object data = readWithMessageConverters(webRequest, parameter);
        validateIfNecessary(data, parameter);
        return data;
    }
    @Override
    public void handleReturnValue(Object returnValue, MethodParameter returnType,
                                  ModelAndViewContainer mavContainer, NativeWebRequest webRequest) throws Exception {
        // There is no need to render view
        mavContainer.setRequestHandled(true);
        writeWithMessageConverters(returnValue, returnType, webRequest);
    }
    // +----------------------------------------------------------------------------+
    // |                            private method for read                         |
    // +----------------------------------------------------------------------------+
    protected <T> Object readWithMessageConverters(NativeWebRequest webRequest, MethodParameter parameter)
            throws IOException, UnsatisfiedMethodSignatureException {
        validateArgParamOrReturnValueType(parameter);
        httpservletRequest servletRequest = webRequest.getNativeRequest(HttpServletRequest.class);
        Assert.state(servletRequest != null, "No HttpServletRequest");
        return readWithMessageConverters(servletRequest, parameter);
    }
    protected Object readWithMessageConverters(HttpServletRequest servletRequest, MethodParameter parameter)
            throws IOException {
        RequestExcelInfo requestExcelInfo =
                new RequestExcelInfo(parameter.getParameterAnnotation(RequestExcel.class));
        InputStream inputStream;
        if (servletRequest instanceof MultipartRequest) {
            inputStream = ((MultipartRequest) servletRequest)
                    .getMultiFileMap()
                    .values()
                    .stream()
                    .flatMap(Collection::stream)
                    .findFirst()
                    .map(multipartFile -> {
                        try {
                            return multipartFile.getInputStream();
                        } catch (IOException e) {
                            return null;
                        }
                    })
                    .get();
        } else {
            inputStream = servletRequest.getInputStream();
        }
        CollectorReadListener collectorReadListener = new CollectorReadListener();
        try (ExcelReader excelReader = EasyExcel.read(inputStream).build()) {
            List<ReadSheet> readSheetList = requestExcelInfo.getSheetInfoList()
                    .stream()
                    .map(sheetInfo -> EasyExcel.readSheet(sheetInfo.getIndex())
                            .head(sheetInfo.getHeadClazz())
                            .reGISterReadListener(collectorReadListener)
                            .build()
                    )
                    .collect(Collectors.toList());
            excelReader.read(readSheetList);
        }
        return collectorReadListener.groupByHeadClazz();
    }
    protected void validateIfNecessary(Object data, MethodParameter parameter) throws ExcelCellContentNotValidException {
        if (parameter.hasParameterAnnotation(Validated.class)
                || parameter.hasParameterAnnotation(Valid.class)) {
            List<Object> flattenData = ((List<List<Object>>) data).stream()
                    .flatMap(Collection::stream)
                    .collect(Collectors.toList());
            for (Object target : flattenData) {
                Set<ConstraintViolation<Object>> constraintViolationSet = ValidationUtil.validate(target);
                if (CollectionUtils.isNotEmpty(constraintViolationSet)) {
                    String errORMsg = constraintViolationSet.stream()
                            .map(ConstraintViolation::getMessage)
                            .distinct()
                            .findFirst()
                            .get();
                    throw new ExcelCellContentNotValidException(errorMsg);
                }
            }
        }
    }
    // +----------------------------------------------------------------------------+
    // |                            private method for write                        |
    // +----------------------------------------------------------------------------+
    protected void writeWithMessageConverters(Object value, MethodParameter returnType, NativeWebRequest webRequest)
            throws IOException, HttpMessageNotWritableException, UnsatisfiedMethodSignatureException {
        HttpServletResponse response = webRequest.getNativeResponse(HttpServletResponse.class);
        Assert.state(response != null, "No HttpServletResponse");
        ResponseExcelInfo responseExcelInfo =
                new ResponseExcelInfo(returnType.getMethodAnnotation(ResponseExcel.class));
        final String fileName = responseExcelInfo.getName();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        if (TEMPLATE.equals(responseExcelInfo.getScene())) {
            response.setHeader("Content-disposition",
                    "attachment;filename=" + URLEncoder.encode(
                            fileName.substring(fileName.indexOf("/") + 1), StandardCharsets.UTF_8.name()));
            BufferedInputStream bufferedInputStream =
                    new BufferedInputStream(resourceLoader.getResource(CLASSPATH_URL_PREFIX + fileName).getInputStream());
            BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(response.getOutputStream());
            FileCopyUtils.copy(bufferedInputStream, bufferedOutputStream);
        } else {
            validateArgParamOrReturnValueType(returnType);
            response.setHeader("Content-disposition",
                    "attachment;filename=" + URLEncoder.encode(
                            fileName, StandardCharsets.UTF_8.name()) + responseExcelInfo.getSuffix().getValue());
            try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
                List<WriteSheet> writeSheetList = responseExcelInfo.getSheetInfoList()
                        .stream()
                        .map(sheetInfo -> EasyExcel.writerSheet(sheetInfo.getName())
                                .head(sheetInfo.getHeadClazz())
                                .build()
                        )
                        .collect(Collectors.toList());
                List<List<Object>> multiSheetData = (List<List<Object>>) value;
                for (int i = 0; i < writeSheetList.size(); i++) {
                    WriteSheet writeSheet = writeSheetList.get(i);
                    List<Object> singleSheetData = multiSheetData.get(i);
                    excelWriter.write(singleSheetData, writeSheet);
                }
            }
        }
    }
    // +----------------------------------------------------------------------------+
    // |                            common private method                           |
    // +----------------------------------------------------------------------------+
    private void validateArgParamOrReturnValueType(MethodParameter target) throws UnsatisfiedMethodSignatureException {
        try {
            ResolvableType resolvableType = ResolvableType.forMethodParameter(target);
            if (!List.class.isAssignableFrom(resolvableType.resolve())) {
                throw new UnsatisfiedMethodSignatureException(
                        "@RequestExcel or @ResponseExcel Must Be Annotated With List<List<>>");
            }
            if (!List.class.isAssignableFrom(resolvableType.getGeneric(0).resolve())) {
                throw new UnsatisfiedMethodSignatureException(
                        "@RequestExcel or @ResponseExcel Must Be Annotated With List<List<>>");
            }
        } catch (Exception exception) {
            throw new UnsatisfiedMethodSignatureException(
                    "@RequestExcel or @ResponseExcel Must Be Annotated With List<List<>>");
        }
    }
}

2.2 RequestMappingHandlerAdapter 后置处理器

public class RequestMappingHandlerAdapterPostProcessor implements BeanPostProcessor,
        PriorityOrdered, ResourceLoaderAware {
    private ResourceLoader resourceLoader;
    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
        if (!supports(bean)) {
            return bean;
        }
        RequestMappingHandlerAdapter requestMappingHandlerAdapter = (RequestMappingHandlerAdapter) bean;
        List<HandlerMethodArgumentResolver> argumentResolvers = requestMappingHandlerAdapter.getArgumentResolvers();
        List<HandlerMethodReturnValueHandler> returnValueHandlers = requestMappingHandlerAdapter.getReturnValueHandlers();
        Assert.notEmpty(argumentResolvers,
                "RequestMappingHandlerAdapter's argument resolver is empty, this is illegal state");
        Assert.notEmpty(returnValueHandlers,
                "RequestMappingHandlerAdapter's return-value handler is empty, this is illegal state");
        List<HandlerMethodArgumentResolver> copyArgumentResolvers = new ArrayList<>(argumentResolvers);
        RequestResponseExcelMethodProcessor argumentResolver4RequestExcel = new RequestResponseExcelMethodProcessor(null);
        copyArgumentResolvers.add(0, argumentResolver4RequestExcel);
        requestMappingHandlerAdapter.setArgumentResolvers(Collections.unmodifiableList(copyArgumentResolvers));
        List<HandlerMethodReturnValueHandler> copyReturnValueHandlers = new ArrayList<>(returnValueHandlers);
        RequestResponseExcelMethodProcessor returnValueHandler4ResponseExcel = new RequestResponseExcelMethodProcessor(resourceLoader);
        copyReturnValueHandlers.add(0, returnValueHandler4ResponseExcel);
        requestMappingHandlerAdapter.setReturnValueHandlers(Collections.unmodifiableList(copyReturnValueHandlers));
        return requestMappingHandlerAdapter;
    }
    @Override
    public int getOrder() {
        return Ordered.LOWEST_PRECEDENCE;
    }
    @Override
    public void setResourceLoader(ResourceLoader resourceLoader) {
        this.resourceLoader = resourceLoader;
    }
    private boolean supports(Object bean) {
        return bean instanceof RequestMappingHandlerAdapter;
    }
}

3 总结

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

坦白来说,该组件的设计初衷只是为了帮助大家从公式化、模板化的Excel 读写逻辑中解放出来,从而专注于核心业务逻辑的开发,并不是为了增强 EasyExcel,后续也不会朝着这一方向演进。

参考 github.com/dk900912/ea…

以上就是java EasyExcel面向Excel文档读写逻辑示例详解的详细内容,更多关于java EasyExcel读写逻辑的资料请关注其它相关文章!

相关文章