A brief introduction.
Export is a common function of background management system, when the amount of data is particularly large, memory overflow and blocked pages, I have encapsulated an export, use batch query data to avoid memory overflow and use SXSSFWorkbook caching data to the file to solve the problem of downloading large files EXCEL blocked pages.
However, one is the problem of unfriendly encapsulation and inconvenient use; the other is that the operation mode of these POI still has the problem of excessive memory consumption; the third is the problem of data defects when there are empty cycles and divisible, as well as the hidden danger of memory overflow.
I accidentally found the EasyExcel framework of Ali open source, and found that it can control the memory occupation of EXCEL parsing at KB level, and will never overflow memory (internal implementation needs to be studied). Moreover, it is very fast, about 100W records, more than a dozen fields, it only takes 70 seconds to complete the download.
Then abandoned their packaging, turned to study ali open source EasyExcel. But to tell the truth, at that time their packaging is still some technical content, such as: appearance mode, template method mode, and the idea of delegation, combination of ideas, you can see. In addition, wechat search focus on Java technology stack, send: Design Mode, you can get the Java design mode tutorial I sorted out.
EasyExcel’s Github address is: github.com/alibaba/eas…
Case 2.
2.1 POM depends on
<! Alibaba </groupId> <artifactId> easyExcel </artifactId> <version>1.1.1</version> </dependency>Copy the code2.2 the POJO object
package com.authorization.privilege.excel; import java.util.Date; /** * @author qjwyss * @description */ public class User { private String uid; private String name; private Integer age; private Date birthday; public User() { } public User(String uid, String name, Integer age, Date birthday) { this.uid = uid; this.name = name; this.age = age; this.birthday = birthday; } public String getUid() { return uid; } public void setUid(String uid) { this.uid = uid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; }}Copy the code2.3 Test Environment
2.3.1. Small amount of data (within 20W) : One SHEET and one query export
/** * For a small number of records (approximately 20W), you can call this method and write it to a SHEET in EXCEL * Note: the number of records queried at one time should not be too large, so as not to run out of memory. * * * @ throws IOException / @ Test public void writeExcelOneSheetOnceWrite () throws IOException {/ / generate EXCEL and specify the output path OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx"); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // set SHEET SHEET SHEET = new SHEET (1, 0); sheet.setSheetName("sheet1"); Table Table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); Titles. The add (arrays.aslist (" user ID ")); Titles. The add (arrays.aslist (" name ")); Titles. The add (arrays.aslist (" age ")); Titles. The add (arrays.aslist (" birthday ")); table.setHead(titles); List<List<String>> userList = new ArrayList<>(); for (int i = 0; i < 100; I ++) {userlist.add (array.asList ("ID_" + I, "little" + I, String.Valueof (I), new Date().toString())); } writer.write0(userList, sheet, table); writer.finish(); }Copy the code2.3.2. Moderate Amount of Data (less than 100W) : One SHEET is exported in batches
/** * For the number of records within 105W, you can call this method to find multiple batches and then write to a SHEET in EXCEL * Note: * the number of records out of each query should not be too large, according to the memory size set a reasonable number of records per query, will not overflow memory. * data cannot exceed a maximum data SHEET storage quantity is 105 w * * @ throws IOException * / @ Test public void writeExcelOneSheetMoreWrite () throws IOException {// Generate EXCEL and specify output path OutputStream out = new FileOutputStream("E:\ temp\ withouthead2.xlsx "); IOException {// Generate EXCEL and specify output path OutputStream out = new FileOutputStream("E:\ temp\ withouthead2.xlsx "); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); // set SHEET SHEET SHEET = new SHEET (1, 0); sheet.setSheetName("sheet1"); Table Table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); Titles. The add (arrays.aslist (" user ID ")); Titles. The add (arrays.aslist (" name ")); Titles. The add (arrays.aslist (" age ")); Titles. The add (arrays.aslist (" birthday ")); table.setHead(titles); Integer totalRowCount = 50 Integer totalRowCount = 50 Integer pageSize = 20; Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1); CurrentPage = I +1; currentPage = I +1; pageSize = pageSize for (int i = 0; i < writeCount; If (I < writecount-1) {List<List<String>> userList = new ArrayList<>(); if (I < writecount-1) {List<List<String>> userList = new ArrayList<>(); for (int j = 0; j < pageSize; J++) {userlist.add (array.aslist ("ID_" + math.random (), "shaoming ", string.valueof (math.random ()), new Date().tostring ()))); } writer.write0(userList, sheet, table); } else if (I == writecount-1) {List<List<String>> userList = new ArrayList<>(); Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize; for (int j = 0; j < lastWriteRowCount; J++) {userlist.add (array.aslist ("ID_" + math.random (), "shaoming ", string.valueof (math.random ()), new Date().tostring ()))); } writer.write0(userList, sheet, table); } } writer.finish(); }Copy the code2.3.3. Large amount of data (even millions) : Multiple sheets are queried and exported in batches
/** * This method can be used to find batches of several million records and then write to multiple sheets in EXCEL * Note: * perSheetRowCount % pageSize * perSheetRowCount % pageSize * perSheetRowCount % pageSize * * * @ throws IOException / @ Test public void writeExcelMoreSheetMoreWrite () throws IOException {/ / generate EXCEL and specify the output path OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx"); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); String sheetName = "sheetName "; Table Table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); Titles. The add (arrays.aslist (" user ID ")); Titles. The add (arrays.aslist (" name ")); Titles. The add (arrays.aslist (" age ")); Titles. The add (arrays.aslist (" birthday ")); table.setHead(titles); // Simulate batch query: total number of records 250, 100 for each SHEET, 3 for each query 20, the first two sheets are queried 5 times, the last SHEET is queried 3 times, the last record is written 10 // Note: PerSheetRowCount = pageSize = pageSize = pageSize = pageSize = pageSize = pageSize = pageSize = pageSize Integer totalRowCount = 250; Integer perSheetRowCount = 100; Integer pageSize = 20; Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1); Integer previousSheetWriteCount = perSheetRowCount / pageSize; Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1)); for (int i = 0; i < sheetCount; I ++) {// create SHEET SHEET SHEET = new SHEET (I, 0); sheet.setSheetName(sheetName + i); If (I < sheetCount - 1) {/ / 2 SHEET, before each SHEET to check 5 times Check each article 20 each SHEET to write full 100 line 2 SHEET total 200 practical environment: parameters: currentPage: j+1 + previousSheetWriteCount*i, pageSize: pageSize for (int j = 0; j < previousSheetWriteCount; j++) { List<List<String>> userList = new ArrayList<>(); for (int k = 0; k < 20; K++) {userlist.add (array.aslist ("ID_" + math.random (), "shaoming ", string.valueof (math.random ()), new Date().tostring ()))); } writer.write0(userList, sheet, table); }} else if (I == sheetcount-1) {currentPage = I +1; pageSize = pageSize for (int j = 0; j < lastSheetWriteCount; If (j < lastsheetwritecount-1) {List<List<String>> userList = new ArrayList<>(); for (int k = 0; k < 20; K++) {userlist.add (array.aslist ("ID_" + math.random (), "shaoming ", string.valueof (math.random ()), new Date().tostring ()))); } writer.write0(userList, sheet, table); } else if (j == lastSheetwritecount-1) {List<List<String>> userList = new ArrayList<>(); Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize; for (int k = 0; k < lastWriteRowCount; K++) {userlist.add (array.aslist ("ID_" + math.random (), "sime1 ", string.valueof (math.random ()), new Date().tostring ()))); } writer.write0(userList, sheet, table); } } } } writer.finish(); }Copy the code2.4 Production Environment
2.4.0. Excel constant class
package com.authorization.privilege.constant; /** * @author qjwyss * @description EXCEL constant class */ public class ExcelConstant {/** * public static final Integer PER_SHEET_ROW_COUNT = 1000000; Public static final Integer PER_WRITE_ROW_COUNT = 200000; public static final Integer PER_WRITE_ROW_COUNT = 200000; }Copy the codeNote: For the convenience of writing, the two here must be divisible, which can save a lot of unnecessary judgment. In addition, if you test yourself, you can change to 100,20.
2.4.1. Small amount of data (within 20W) : One SHEET and one query export
@Override public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception { ServletOutputStream out = null; try { out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); String fileName = new String(("SystemExcel").getBytes(), "utF-8 "); // Set SHEET name SHEET = new SHEET (1, 0); Sheet.setsheetname (" Sheet1 "); Table Table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); Title.add (Arrays. AsList (" system name ")); Title.add (arrays.aslist (" system id ")); Titles. The add (arrays.aslist (" description ")); Titles. The add (arrays.aslist (" state ")); Title.add (Arrays. AsList (" creator ")); Title.add (arrays.asList (" createtime ")); table.setHead(titles); EXCEL List<List<String>> dataList = new ArrayList<>(); List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO); if (! CollectionUtils.isEmpty(sysSystemVOList)) { sysSystemVOList.forEach(eachSysSystemVO -> { dataList.add(Arrays.asList( eachSysSystemVO.getSystemName(), eachSysSystemVO.getSystemKey(), eachSysSystemVO.getDescription(), eachSysSystemVO.getState().toString(), eachSysSystemVO.getCreateUid(), eachSysSystemVO.getCreateTime().toString() )); }); } writer.write0(dataList, sheet, table); SetHeader (" content-disposition ", "attachment; filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.finish(); out.flush(); } finally { if (out ! = null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); }} return resultvo. getSuccess(" EXCEL successfully exported system list "); }Copy the code2.4.2. Moderate Amount of Data (less than 100W) : One SHEET is exported in batches
@Override public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception { ServletOutputStream out = null; try { out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); String fileName = new String(("SystemExcel").getBytes(), "utF-8 "); // Set SHEET name SHEET = new SHEET (1, 0); Sheet.setsheetname (" Sheet1 "); Table Table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); Title.add (Arrays. AsList (" system name ")); Title.add (arrays.aslist (" system id ")); Titles. The add (arrays.aslist (" description ")); Titles. The add (arrays.aslist (" state ")); Title.add (Arrays. AsList (" creator ")); Title.add (arrays.asList (" createtime ")); table.setHead(titles); Integer totalRowCount = this.sysSystemReadMapper.selectCountSysSystemVOList(sysSystemVO); Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT; Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1); Int I = 0; for (int I = 0; i < writeCount; i++) { List<List<String>> dataList = new ArrayList<>(); Pagehelper. startPage(I + 1, pageSize); pageSize (I + 1, pageSize); List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO); if (! CollectionUtils.isEmpty(sysSystemVOList)) { sysSystemVOList.forEach(eachSysSystemVO -> { dataList.add(Arrays.asList( eachSysSystemVO.getSystemName(), eachSysSystemVO.getSystemKey(), eachSysSystemVO.getDescription(), eachSysSystemVO.getState().toString(), eachSysSystemVO.getCreateUid(), eachSysSystemVO.getCreateTime().toString() )); }); } writer.write0(dataList, sheet, table); } // Download EXCEL response.setHeader(" content-disposition ", "attachment; filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.finish(); out.flush(); } finally { if (out ! = null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); }} return resultvo. getSuccess(" EXCEL successfully exported system list "); }Copy the code2.4.3. Data is very large (even millions) : Multiple sheets are queried and exported in batches
@Override public ResultVO<Void> exportSysSystemExcel(SysSystemVO sysSystemVO, HttpServletResponse response) throws Exception { ServletOutputStream out = null; try { out = response.getOutputStream(); ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX); String fileName = new String(("SystemExcel").getBytes(), "utF-8 "); // Set SHEET name String sheetName = "sheetName "; Table Table = new Table(1); List<List<String>> titles = new ArrayList<List<String>>(); Title.add (Arrays. AsList (" system name ")); Title.add (arrays.aslist (" system id ")); Titles. The add (arrays.aslist (" description ")); Titles. The add (arrays.aslist (" state ")); Title.add (Arrays. AsList (" creator ")); Title.add (arrays.asList (" createtime ")); table.setHead(titles); / / total number of queries and encapsulate relevant variables (do not change the copy directly line) Integer totalRowCount = this. SysSystemReadMapper. SelectCountSysSystemVOList (sysSystemVO); Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT; Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT; Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1); Integer previousSheetWriteCount = perSheetRowCount / pageSize; Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ? previousSheetWriteCount : (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1)); for (int i = 0; i < sheetCount; I ++) {// create SHEET SHEET SHEET = new SHEET (I, 0); sheet.setSheetName(sheetName + i); For (int j = 0; int j = 0; j < (i ! = sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) { List<List<String>> dataList = new ArrayList<>(); Pagehelper. startPage(j + 1 + previousSheetWriteCount * I, pageSize); List<SysSystemVO> sysSystemVOList = this.sysSystemReadMapper.selectSysSystemVOList(sysSystemVO); if (! CollectionUtils.isEmpty(sysSystemVOList)) { sysSystemVOList.forEach(eachSysSystemVO -> { dataList.add(Arrays.asList( eachSysSystemVO.getSystemName(), eachSysSystemVO.getSystemKey(), eachSysSystemVO.getDescription(), eachSysSystemVO.getState().toString(), eachSysSystemVO.getCreateUid(), eachSysSystemVO.getCreateTime().toString() )); }); } writer.write0(dataList, sheet, table); }} // Download EXCEL response.setHeader(" content-disposition ", "attachment; filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xls"); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); writer.finish(); out.flush(); } finally { if (out ! = null) { try { out.close(); } catch (Exception e) { e.printStackTrace(); }} return resultvo. getSuccess(" EXCEL successfully exported system list "); }Copy the codeThird, summary
Forged data, 100W records, 18 fields, test export 70s. In the actual production environment use, specific or depends on the performance of their own WRITTEN SQL. If SQL is fast, it will be fast.
One recommendation is to use a single table query when paging. For the redundant foreign key fields that need to be processed, check them out and put them in the map once (@mapkey annotation is recommended), and then get the corresponding names from the map based on the foreign key when traversing the list.
One tenet: less query SQL, faster export.
As an aside: If the amount of data is too large, it will be slow to query the total number using count(1). You can speed up the query by adjusting mysql buffer pool parameters.
Also is met a problem, use pagehelper, a large quantity of data, limit 0, 20 W, the limit of 20 W, 40 W, 40 W limit, 60 W, limit 60 W, 80 W query sometime soon, sometimes slow, for research.