java EasyExcel面向Excel文檔讀寫邏輯示例詳解

更新時間：2022年07月25日 15:10:15 作者：程序猿杜小頭

這篇文章主要為大家介紹了java EasyExcel面向Excel文檔讀寫邏輯示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

正文

EasyExcel是一款由阿里開源的 Excel 處理工具。相較于原生的Apache POI，它可以更優(yōu)雅、快速地完成 Excel 的讀寫功能，同時更加地節(jié)約內(nèi)存。

即使 EasyExcel 已經(jīng)很優(yōu)雅了，但面向 Excel 文檔的讀寫邏輯幾乎千篇一律，筆者索性將這些模板化的邏輯抽離出來，該組件已經(jīng)發(fā)布到 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 實現(xiàn)原理

一切 Java 程序都是基于 Thread 的，當一個 HTTP 請求到達后，Servlet Container 會從其線程池中撈出一個線程來處理該 HTTP 請求。具體地，該 HTTP 請求首先到達 Servlet Container 的FilterChain中；然后，F(xiàn)ilterChain 將該 HTTP 請求委派給DispatcherServlet處理，而 DispatcherServlet 恰恰就是 Spring MVC 的門戶。在 Spring MVC 中，所有 HTTP 請求都由 DispatcherServlet 進行路由分發(fā)。大致流程下圖所示。

DispatcherServlet 在 HandlerMapping 的幫助下可以快速匹配到最終的 Controller，由于 Controller 大多由@RequestMapping注解標注，那么RequestMappingHandlerMapping最終脫穎而出。

RequestMappingHandlerMapping 會將 HTTP 請求映射到一個HandlerExecutionChain實例中，每一個 HandlerExecutionChain 實例的內(nèi)部維護了HandlerMethod和List<HandlerInterceptor>。

其中，HandlerMethod 實例持有一個Object類型的 bean 變量和java.lang.reflect.Method類型的 method 變量，bean 和 method 這倆成員變量組合起來最終可以確定究竟由哪一個 Controller 中的某一方法來處理當前 HTTP 請求。

此時已經(jīng)知道目標方法了，那直接反射執(zhí)行目標方法？是不可以的，因為通過反射來執(zhí)行目標方法需要有參數(shù)才行，此外還需要對目標方法的執(zhí)行結果進行加工處理。既然 HandlerMapping 沒有解析請求體和處理目標執(zhí)行結果的能力，只能再引入一層適配器了，它就是 HandlerAdapter。

在 Spring MVC 所提供的若干種 HandlerAdapter 中，能夠適配 HandlerMethod 的只有RequestMappingHandlerAdapter；

RequestMappingHandlerAdapter 實現(xiàn)了InitializingBean接口，用于初始化HandlerMethodArgumentResolverComposite類型的 argumentResolvers 成員變量和HandlerMethodReturnValueHandlerComposite類型的 returnValueHandlers 成員變量，Composite 后綴表明這倆成員變量均是一種復合類，argumentResolvers 持有數(shù)十種HandlerMethodArgumentResolver類型的方法參數(shù)解析器，而 returnValueHandlers 則持有數(shù)十種HandlerMethodReturnValueHandler類型的方法返回值解析器。

重點來了！首先，我們需要一個實現(xiàn) HandlerMethodArgumentResolver 接口的方法參數(shù)解析器，該解析器主要用于解析@RequestExcel注解，以讀取 Excel 文檔；

此外，我們還需要一個實現(xiàn) HandlerMethodReturnValueHandler 接口的方法返回值解析器，該解析器主要用于解析@ResponseExcel注解，以將目標方法所返回的數(shù)據(jù)寫入到 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 注解修飾的方法參數(shù)必須是一個List<List<>>類型，而由 @RequestExcel 注解修飾的方法返回類型也必須是一個List<List<>>類型，否則將拋出UnsatisfiedMethodSignatureException類型的自定義異常。

坦白來說，該組件的設計初衷只是為了幫助大家從公式化、模板化的Excel 讀寫邏輯中解放出來，從而專注于核心業(yè)務邏輯的開發(fā)，并不是為了增強 EasyExcel，后續(xù)也不會朝著這一方向演進。

參考 github.com/dk900912/ea…

以上就是java EasyExcel面向Excel文檔讀寫邏輯示例詳解的詳細內(nèi)容，更多關于java EasyExcel讀寫邏輯的資料請關注腳本之家其它相關文章！

您可能感興趣的文章: