preface
I have finished my busy work today, and I have sent a task above. The export interface of a project has too much data, so it can be exported directlyMemory Overflow (OOM)I’m going to limit the number of rows that I’m going to export for the time being, and then I’m going to let me do that, and I’m going to do that all afternoon, and I’m going to do thatEasyExcelThis problem is solved and the mapping speed is greatly improved, as shown in the following figure:
EasyExcel introduction
EasyExcel is an open source project for reading and writing Excel based on Java. It is a high-performance Excel processing tool owned by Alibaba. Save as much memory as possible to support reading and writing Excel 100 MB.
Apache POI and JXL are commonly used frameworks for Java parsing and Excel generation, but they all have a serious problem of memory consumption. POI has a SET of SAX mode API that can solve some problems of memory overflow to a certain extent, but POI still has some defects
For example: 07 version of Excel after decompression storage is completed in memory, memory consumption is still very large. EasyExcel rewrites POI on 07 version of Excel parsing, can make the original a 3M Excel with POI SAX need to use about 100M memory reduced to a few M, and no matter how big Excel will not overflow memory, and it in the upper layer to do the model transformation package, so that users more simple.
Github open Source: github.com/alibaba/eas…
Language finches case address: www.yuque.com/easyexcel/d…
Quick to use
Import Maven dependency ps: Here is only an example of exporting data. For more operations, please refer to the official website
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
</dependency>
Copy the codeEntity class
@data public class DemoData {@excelProperty (" String title ") private String String; // Use string to connect the date. @dateTimeFormat (" YYYY MM MM DD HH MM MM ss SEC ") @excelProperty (" Date title ") private Date Date; @excelProperty (" number title ") // I want to receive percentage numbers // @numberFormat ("#.##%") private Double doubleData; @excelignore private String ignore; }Copy the codeTesting:
@test public void yy(){// 1 String fileName = "D:\\Program Files (x86)\\ Program \excel\\easyExcel "+ "simpleWrite.xlsx"; // We need to specify which class to write to, and then write to the first sheet, EasyExcel. Write(fileName, demodata.class).sheet(" template ").dowrite (data()); } private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); Data. setString(" string "+ I); data.setDate(new Date()); Data. SetDoubleData (0.56); list.add(data); } return list; }Copy the codeEffect display:
Actual project use
After a brief understanding of the use of EasyExcel, we can integrate it into our project. Firstly, we will briefly talk about why the data exported in my project will overflow memory (OOM).
In fact, there are only 4000 rows of exported data, but each row has as many as 78 columns. Don’t ask me why there are so many rows, so when the number of rows reaches 3000 or more, there is a high probability of memory overflow
Our company framework encapsulates a set of POI usage, what I will do now is to replace the usage derived from POI with EasyExcel
1. Create an export POJO template
@data @contentrowheight (10) @Headrowheight (15) // Title Length @ColumnWidth(15) // Title width Public Class EmergencyeventData { @excelProperty (" ID ") private Integer ID; @excelproperty (" projectName ") private String projectName; @dateTimeFormat (" YYYY MM MM DD HH: MM :ss") @excelProperty (" private String createDate "); @dateTimeFormat (" YYYY MM MM DD HH: MM :ss") @ExcelProperty(" date ") private String detectionTime; }Copy the code74 attributes omitted above (PS: writing is really tired)
List<ExcelHeader> headers = new ArrayList<>(); Headers. Add (new ExcelHeader(" no ", true)); Headers. Add (new ExcelHeader(" projectName", "projectName", string.class)); Add (new ExcelHeader(" createDate", 1, Date. Class, "YYYY MM MM DD HH: MM :ss")); Headers. Add (new ExcelHeader(" date ", "detectionTime", String.class));Copy the codeThe original use of project encapsulation POI is to use a List<Map<String, Object>> to encapsulate the mapping data, using the corresponding key in the Map to match the key in the ↑ code. It doesn’t matter. Now I just need to stuff the data from List<Map<String, Object>> into the collection of template objects I’ve defined, as shown in the pseudocode below
private List<EmergencyeventData> EasyExcelData(List<Map<String, String>>){
if(SysFun.isNotEmpty(data.get("endTimes"))){
eventData.setDocName(data.get("endTimes").toString());
}
emergencyeventDataList.add(eventData);
}
Copy the code2. Respond to the data and return the file stream
The project is separated from the front and back ends, so the interface returned to the front end should be in the form of a file stream, here is my example:
/ / set the response format try {ExcelResponse. ResponseHeader (response, XXX. GetName ()); } catch (UnsupportedEncodingException e) { return ResultForm.createErrorResultForm(null, e.getMessage()); }Copy the codeGive me the method code in my response tool class
Public static void responseHeader(HttpServletResponse response, String name) throws UnsupportedEncodingException { Date d = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); String date = sdf.format(d); String fileName = name; String encodeName = java.net.URLEncoder.encode(fileName, "UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("Access-control-Expose-Headers", "attachment"); response.setHeader("attachment", encodeName + ".xlsx"); }Copy the codeWrite data, return file stream:
// Write parameters are output stream, template object, encapsulated collection data, finished data, Try {EasyExcel. Write (response.getOutputStream(), EmergencyeventData. Class). Sheet (" events "). DoWrite (EmergencyeventData); } catch (Exception e) { return ResultForm.createErrorResultForm(null, e.getMessage()); }Copy the codeThe above can be directly used to test the swagger-UI interface
At the end
No EasyExcel must be used, easier to use than POI, better performance, on the shortcomings of the article welcome to point out!! Blogging is not about blogging for blogging’s sake. Blogging is about sharing your problems and helping others.