preface

We all know that the well-known frameworks for Java parsing and Excel generation are Apache POI and JXL, but they all have a serious problem that is very memory consumption, and the most important thing is that it is difficult to build these tool classes. Because we are all working boys, of course, we need to pay attention to efficiency. If we can finish this Excel function in 5 minutes, the leader will not respect you three points.

So with this article, to help you quickly achieve excel export function based on SpringBoot, we choose the EasyExcel framework of Alibaba, this framework in the analysis of Excel is yyDS, this paper mainly introduces how to quickly access to achieve.

First, introduce dependencies

<! Alibaba </groupId> <artifactId>easyexcel</artifactId> <version>2.2.11</version> </dependency>Copy the code

2. Core tool classes

/** * download ** @param response web response * @param datas export data * @param clazz entity class * @param fileName export fileName * @return */ public static void download(HttpServletResponse response, List datas, Class clazz, String fileName) {// parseHead(response, datas, clazz, parseHead(clazz), fileName); }Copy the code
Private static List<List<String>> parseHead(Class clazz) {Field[] fields = clazz.getDeclaredFields(); List<List<String>> heads = new ArrayList<>(); for (Field field : fields) { List<String> head = new ArrayList<>(); // We should have Swagger in the development, this annotation is provided by Swagger, of course, we can also define a custom annotation. ApiModelProperty apiAnnotation = Field.getannotation (APIModelProperty.class); head.add(apiAnnotation.value()); heads.add(head); } return heads; }Copy the code
/** * download ** @param response web response * @param datas export data * @param head table header * @param fileName export fileName * @return */ public static void download(HttpServletResponse response, List datas, Class clazz, List<List<String>> head, String fileName) {// set the fileName for the output of the web response setResponseHeader(response, fileName); Try {// Export (response.getOutputStream(), head, datas, clazz); } catch (IOException e) {log.info(" failed to get response stream ", e); }}Copy the code
/** * Sets the name of the output file of the Web response * @param Response Web response * @param fileName Name of the exported file */ private static void setResponseHeader(HttpServletResponse response, String fileName) { response.reset(); response.setContentType("application/vnd.ms-excel; charset=utf-8"); try { response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8")); } the catch (UnsupportedEncodingException e) {the info (" don't support coding ", e); } response.setCharacterEncoding("UTF-8"); }Copy the code

The core of the core is coming!!

/** ** @param OS byte output stream * @param head table header * @param datas Exports data * @Param clazz entity class * @throws IOException */ private static void export(OutputStream os, List<List<String>> head, List datas, Class clazz) throws IOException { if (datas == null) { datas = new ArrayList(); } ExcelWriter excelWriter = null; BufferedOutputStream bos = new BufferedOutputStream(os); try { excelWriter = EasyExcel.write(bos, clazz).build(); WriteSheet testSheet = EasyExcel.writerSheet("sheet1") .head(head) .build(); excelWriter.write(datas, testSheet); } catch (Exception e) {log.info(" easyExcel initialization error ", e); } finally { bos.flush(); if (excelWriter ! = null) { excelWriter.finish(); }}}Copy the code

Here we put the whole core logic set up, is not very simple, next to give you run an example.

Third, in actual combat

  1. Define an HTTP interface
@requestMapping ("/excel") @restController @API (tags = "Excel parsing ") @slf4j public Class ExcelTestController { @PostMapping("/export") public void export(HttpServletResponse response) { List<ExcelExportResponseDTO> list = Lists.newArrayList(); ExcelExportResponseDTO info1 = new ExcelExportResponseDTO(); info1.setName("rose"); info1.setAge("23"); list.add(info1); ExcelUtil. Download (the response to the list, ExcelExportResponseDTO. Class, "personal information"); }}Copy the code
  1. Accessing the HTTP Interface

  1. Output Excel results

Four,

Here is the complete implementation of the entire Java Excel export function, we only need Ctrl+C, Ctrl+V can be quickly applied, I hope to help you, the next output Excel import function implementation!

Refueling workers! Ollie give 😎

I’m Rose, thank you for watching, your likes are the biggest power of Rose output, we will see you in the next article!

Note: If there are any mistakes or suggestions in this blog, please leave comments!

Five, series recommendation

Quick Implementation of Excel Import function in Java