Shulou(Shulou.com)06/01 Report--

Today, the editor will share with you the relevant knowledge points about how to quote EasyExcel. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

Premise

Exporting data to Excel is one of the most common back-end requirements. Today, we recommend an Excel operation artifact from Ali: EasyExcel. EasyExcel is the encapsulation of apache-poi from the perspective of its dependency tree. The author chose EasyExcel from the beginning of contact with Excel processing to avoid the memory leak caused by the widespread apache-poi.

Introduce EasyExcel dependency

The Maven that introduces EasyExcel is as follows:

Com.alibaba easyexcel ${easyexcel.version}

The latest version of the current (2020-09) is 2.2.6.

Introduction to API

Excel files are mainly processed around read and write operations, and EasyExcel's API is also designed around these two aspects. First, take a look at the relevant API of the read operation:

/ / create a new ExcelReaderBuilder instance ExcelReaderBuilder readerBuilder = EasyExcel.read (); / / read the file object, which can be File, path (string) or InputStream instance readerBuilder.file (""); / / the password of the file readerBuilder.password (""); / / specify sheet, which can be numeric sequence number sheetNo or string sheetName, and if not specified, all sheetreaderBuilder.sheet ("") will be read; / / whether to automatically close the input stream readerBuilder.autoCloseStream (true) / / Excel file format, including ExcelTypeEnum.XLSX and ExcelTypeEnum.XLSreaderBuilder.excelType (ExcelTypeEnum.XLSX); / / specify the title line of the file, which can be a Class object (used with the @ ExcelProperty annotation) or List instance readerBuilder.head (Collections.singletonList (Collections.singletonList ("head") / / registers the listener to read the event. The default data type is Map. The subscript of the element in the first column starts with readerBuilder.registerReadListener (new AnalysisEventListener () {@ Override public void invokeHeadMap (Map headMap, AnalysisContext context) {/ / callback to the header row). The first line of the file content will be thought to be the title line} @ Override public void invoke (Object o, AnalysisContext analysisContext) {/ / the data of each line will be called back} @ Override public void doAfterAllAnalysed (AnalysisContext analysisContext) {}}) / / build reader ExcelReader excelReader = readerBuilder.build (); / / read data excelReader.readAll (); excelReader.finish ()

As you can see, read operations are mainly designed using Builder mode and event listening (or "observer mode"). In general, the above code can be simplified as follows:

Map head = new HashMap (); List data = new LinkedList (); EasyExcel.read ("absolute path to the file") .sheet () .registerReadListener (new AnalysisEventListener () {@ Override public void invokeHeadMap (Map headMap, AnalysisContext context) {head.putAll (headMap)) } @ Override public void invoke (Map row, AnalysisContext analysisContext) {data.add (row);} @ Override public void doAfterAllAnalysed (AnalysisContext analysisContext) {/ / here you can print a log to tell all lines to finish reading}}) .doRead ()

If you need to read the data and convert it to the corresponding object list, you need to specify the Class of the header row, which is used in conjunction with the annotation @ ExcelProperty:

File content: | order number | Mobile number | | ORDER_ID_1 | 112222 | | ORDER_ID_2 | 334455 | @ Dataprivate static class OrderDTO {@ ExcelProperty (value = "order number") private String orderId; @ ExcelProperty (value = "mobile number") private String phone;} Map head = new HashMap (); List data = new LinkedList () EasyExcel.read ("absolute path to the file") .head (OrderDTO.class). Sheet () .registerReadListener (new AnalysisEventListener () {@ Override public void invokeHeadMap (Map headMap, AnalysisContext context) {head.putAll (headMap);} @ Override public void invoke (OrderDTO row, AnalysisContext analysisContext) {data.add (row) } @ Override public void doAfterAllAnalysed (AnalysisContext analysisContext) {/ / here you can print a log telling all lines to finish reading}}) .doRead ()

If the amount of data is large, it is recommended to use the Map type to read and manipulate data objects, otherwise a large number of reflection operations will greatly increase the time to read data, and in extreme cases, for example, when there are many attributes, reflection operations may take longer than reading and traversing.

Then look at the API of the write operation:

/ / create a new ExcelWriterBuilder instance ExcelWriterBuilder writerBuilder = EasyExcel.write (); / / output the file object, which can be File, path (string), or OutputStream instance writerBuilder.file (""); / / specify sheet, which can be numeric serial number sheetNo or string sheetName, and may not be set. The WriteSheet mentioned below overrides writerBuilder.sheet ("); / / the password of the file writerBuilder.password ("). / / Excel file format, including ExcelTypeEnum.XLSX and ExcelTypeEnum.XLSwriterBuilder.excelType (ExcelTypeEnum.XLSX); / / whether to automatically close the output stream writerBuilder.autoCloseStream (true); / / specify the header line of the file, which can be a Class object (used with @ ExcelProperty annotation) or List instance writerBuilder.head (Collections.singletonList (Collections.singletonList ("head"); / / build ExcelWriter instance ExcelWriter excelWriter = writerBuilder.build (); List data = new ArrayList () / / build output sheetWriteSheet writeSheet = new WriteSheet (); writeSheet.setSheetName ("target"); excelWriter.write (data, writeSheet); / / this step must be called, otherwise the output file may be incomplete excelWriter.finish ()

There are many styles, line processors, converter settings and other methods in ExcelWriterBuilder, which I don't think are commonly used. I don't give an example here. The style of the content is usually easier to operate after the output file is processed again. Write operations can generally be simplified as follows:

List head = new ArrayList (); List data = new LinkedList (); EasyExcel.write ("absolute path to output file") .head (head) .excelType (ExcelTypeEnum.XLSX) .sheet ("target") .doWrite (data); practical skills

The following is a brief introduction to the practical techniques used in production.

Multithreaded reading

Using EasyExcel multithreading is recommended under limited prerequisites:

The source file has been divided into multiple small files, and each small file has the same number of header rows and columns.

The machine should have plenty of memory, because the results of concurrent reads need to be merged into one large result set, and all the data is stored in memory.

Multiple files that often encounter external feedback need urgent data analysis or cross-proofreading. In order to speed up file reading, the author usually uses this method to read Excel files in batches with consistent format.

A simple example is as follows:

@ Slf4jpublic class EasyExcelConcurrentRead {static final int N_CPU = Runtime.getRuntime (). AvailableProcessors (); public static void main (String [] args) throws Exception {/ / suppose there are a bunch of Excel files of the same format in the temp directory of disk I String dir = "I:\\ temp"; List mergeResult = Lists.newLinkedList () ThreadPoolExecutor executor = new ThreadPoolExecutor (N_CPU, N_CPU * 2,0, TimeUnit.SECONDS, new LinkedBlockingQueue (), new ThreadFactory () {private final AtomicInteger counter = new AtomicInteger (); @ Override public Thread newThread (@ NotNull Runnable r) {Thread thread = newThread (r); thread.setDaemon (true)) Thread.setName ("ExcelReadWorker-" + counter.getAndIncrement ()); return thread;}}); Path dirPath = Paths.get (dir) If (Files.isDirectory (dirPath)) {List futures = Files.list (dirPath) .map (path-> path.toAbsolutePath () .toString ()) .filter (absolutePath-> absolutePath.endsWith (".xls") | | absolutePath.endsWith (".xl sx")) .map (absolutePath-> executor.submit (new ReadTask (absolutePath) .notify (Collectors.toList ()) For (Future future: futures) {mergeResult.addAll (future.get ());} log.info ("read the file in the [{}] directory successfully and load a total of {} rows of data", dir, mergeResult.size ()); / / other business logic. } @ RequiredArgsConstructor private static class ReadTask implements Callable {private final String location; @ Override public List call () throws Exception {List data = Lists.newLinkedList () EasyExcel.read (location) .sheet () .registerReadListener (new AnalysisEventListener () {@ Override public void invoke (Map row, AnalysisContext analysisContext) {data.add (row) } @ Override public void doAfterAllAnalysed (AnalysisContext analysisContext) {log.info ("read path [{}] file successfully, a total of [{}] lines", location, data.size ();}}) .doRead () Return data;}

Here we use ThreadPoolExecutor#submit () to submit and read the task, and then use Future#get () to wait for all the tasks to complete before merging the final read result.

Note that the write operations of general files cannot be performed concurrently, otherwise there is a high probability of data confusion.

Multi-Sheet write

Multi-Sheet writing means writing to multiple WriteSheet instances using the same ExcelWriter instance. The header line of each Sheet can be overridden by the configuration attribute in the WriteSheet instance, as shown below:

Public class EasyExcelMultiSheetWrite {public static void main (String [] args) throws Exception {ExcelWriterBuilder writerBuilder = EasyExcel.write (); writerBuilder.excelType (ExcelTypeEnum.XLSX); writerBuilder.autoCloseStream (true); writerBuilder.file ("I:\ temp\\ temp.xlsx"); ExcelWriter excelWriter = writerBuilder.build (); WriteSheet firstSheet = new WriteSheet (); firstSheet.setSheetName ("first") FirstSheet.setHead (Collections.singletonList (Collections.singletonList ("Head of the first Sheet")); / / write the first Sheet excelWriter.write named first (Collections.singletonList ("data of the first Sheet"), firstSheet); WriteSheet secondSheet = new WriteSheet (); secondSheet.setSheetName ("second"); secondSheet.setHead (Collections.singletonList (Collections.singletonList ("Head of the second Sheet") / / write a second Sheet excelWriter.write named second (Collections.singletonList (Collections.singletonList ("data of the second Sheet"), secondSheet); excelWriter.finish ();}} paged query and batch write

In some scenarios with a large amount of data, you can consider paging query and batch writing, which is actually paging query raw data-> data aggregation or conversion-> writing target data-> next page query. In fact, in the case of a small amount of data, one-time full query and full write is only a special case of paged query and batch writing, so query, transformation and write operations can be abstracted into a reusable template method:

Int batchSize = defines the number of entries per query; OutputStream outputStream = defines where to write; ExcelWriter writer = new ExcelWriterBuilder () .autoCloseStream (true) .file (outputStream) .excelType (ExcelTypeEnum.XLSX) .head (ExcelModel.class); for (;;) {List list = originModelRepository. Paging query (); if (list.isEmpty ()) {writer.finish (); break;} else {list conversion-> List excelModelList; writer.write (excelModelList);}} Excel upload and download

The following examples apply to Servlet containers, such as Tomcat, and to spring-boot-starter-web

The operation of Excel file upload is similar to that of ordinary file upload, and then you can use EasyExcel's ExcelReader to read the InputStream instance that is partially abstracted from the request object MultipartHttpServletRequest:

@ PostMapping (path = "/ upload") public ResponseEntity upload (MultipartHttpServletRequest request) throws Exception {Map fileMap = request.getFileMap (); for (Map.Entry part: fileMap.entrySet ()) {InputStream inputStream = part.getValue () .getInputStream (); Map head = new HashMap (); List data = new LinkedList () EasyExcel.read (inputStream) .sheet () .registerReadListener (new AnalysisEventListener () {@ Override public void invokeHeadMap (Map headMap, AnalysisContext context) {head.putAll (headMap) } @ Override public void invoke (Map row, AnalysisContext analysisContext) {data.add (row) } @ Override public void doAfterAllAnalysed (AnalysisContext analysisContext) {log.info ("read the file [{}] successfully, altogether: {} lines.", part.getKey (), data.size ();}}) .doRead () / / other business logic} return ResponseEntity.ok ("success");}

The request using Postman is as follows:

It is also relatively simple to export Excel files using EasyExcel. You only need to attach the OutputStream object carried in the response object HttpServletResponse to the ExcelWriter instance of EasyExcel:

@ GetMapping (path = "/ download") public void download (HttpServletResponse response) throws Exception {/ / if the file name is involved in Chinese, you must use URL encoding, otherwise it will garbled String fileName = URLEncoder.encode ("file name .xlsx", StandardCharsets.UTF_8.toString ()); / / encapsulated header line List head = new ArrayList (); / / encapsulated data List data = new LinkedList (); response.setContentType ("application/force-download") Response.setHeader ("Content-Disposition", "attachment;filename=" + fileName); EasyExcel.write (response.getOutputStream ()) .head (head) .autoCloseStream (true) .excelType (ExcelTypeEnum.XLSX) .sheet ("Sheet name") .doWrite (data);}

It needs to be noted here:

If the file name contains Chinese, it needs to be encoded by URL, otherwise it will be garbled.

Regardless of import or export, if the amount of data is large and time-consuming, remember to adjust the upper limit configuration of connection and read-write timeout in Nginx if you use Nginx.

To use SpringBoot, you need to adjust the configuration values of spring.servlet.multipart.max-request-size and spring.servlet.multipart.max-file-size to avoid anomalies when uploaded files are too large.

These are all the contents of this article "how to quote EasyExcel". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.