一. 簡介
匯出是后臺管理系統的常用功能，當資料量特別大的時候會記憶體溢位和卡頓頁面，曾經自己封裝過一個匯出，采用了分批查詢資料來避免記憶體溢位和使用SXSSFWorkbook方式快取資料到檔案上以解決下載大檔案EXCEL卡死頁面的問題，
不過一是存在封裝不太友好使用不方便的問題，二是這些poi的操作方式仍然存在記憶體占用過大的問題,三是存在慷訓圈和整除的時候資料有缺陷的問題，以及存在記憶體溢位的隱患，
無意間查詢到阿里開源的EasyExcel框架，發現可以將決議的EXCEL的記憶體占用控制在KB級別，并且絕對不會記憶體溢位（內部實作待研究）,還有就是速度極快，大概100W條記錄，十幾個欄位，只需要70秒即可完成下載，
遂拋棄自己封裝的，轉戰研究阿里開源的EasyExcel. 不過 說實話，當時自己封裝的那個還是有些技術含量的，例如：外觀模式，模板方法模式，以及委托思想，組合思想，可以看看，
EasyExcel的github地址是：https://github.com/alibaba/easyexcel
二. 案例
2.1 POM依賴
<!-- 阿里開源EXCEL -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.1</version>
</dependency>
2.2 POJO物件
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;
}
}
2.3 測驗環境
2.3.1.資料量少的(20W以內吧)：一個SHEET一次查詢匯出
/**
* 針對較少的記錄數(20W以內大概)可以呼叫該方法一次性查出然后寫入到EXCEL的一個SHEET中
* 注意： 一次性查詢出來的記錄數量不宜過大，不會記憶體溢位即可，
*
* @throws IOException
*/
@Test
public void writeExcelOneSheetOnceWrite() throws IOException {
// 生成EXCEL并指定輸出路徑
OutputStream out = new FileOutputStream("E:\\temp\\withoutHead1.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 設定SHEET
Sheet sheet = new Sheet(1, 0);
sheet.setSheetName("sheet1");
// 設定標題
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("用戶ID"));
titles.add(Arrays.asList("名稱"));
titles.add(Arrays.asList("年齡"));
titles.add(Arrays.asList("生日"));
table.setHead(titles);
// 查詢資料匯出即可 比如說一次性總共查詢出100條資料
List<List<String>> userList = new ArrayList<>();
for (int i = 0; i < 100; i++) {
userList.add(Arrays.asList("ID_" + i, "小明" + i, String.valueOf(i), new Date().toString()));
}
writer.write0(userList, sheet, table);
writer.finish();
}
2.3.2.資料量適中（100W以內）：一個SHEET分批查詢匯出
/**
* 針對105W以內的記錄數可以呼叫該方法分多批次查出然后寫入到EXCEL的一個SHEET中
* 注意：
* 每次查詢出來的記錄數量不宜過大，根據記憶體大小設定合理的每次查詢記錄數，不會記憶體溢位即可，
* 資料量不能超過一個SHEET存盤的最大資料量105W
*
* @throws IOException
*/
@Test
public void writeExcelOneSheetMoreWrite() throws IOException {
// 生成EXCEL并指定輸出路徑
OutputStream out = new FileOutputStream("E:\\temp\\withoutHead2.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 設定SHEET
Sheet sheet = new Sheet(1, 0);
sheet.setSheetName("sheet1");
// 設定標題
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("用戶ID"));
titles.add(Arrays.asList("名稱"));
titles.add(Arrays.asList("年齡"));
titles.add(Arrays.asList("生日"));
table.setHead(titles);
// 模擬分批查詢：總記錄數50條，每次查詢20條， 分三次查詢 最后一次查詢記錄數是10
Integer totalRowCount = 50;
Integer pageSize = 20;
Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
// 注： 此處僅僅為了模擬資料，實用環境不需要將最后一次分開，合成一個即可， 引數為：currentPage = i+1; pageSize = pageSize
for (int i = 0; i < writeCount; i++) {
// 前兩次查詢 每次查20條資料
if (i < writeCount - 1) {
List<List<String>> userList = new ArrayList<>();
for (int j = 0; j < pageSize; j++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
} else if (i == writeCount - 1) {
// 最后一次查詢 查多余的10條記錄
List<List<String>> userList = new ArrayList<>();
Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
for (int j = 0; j < lastWriteRowCount; j++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
}
}
writer.finish();
}
2.3.3.資料量很大（幾百萬都行）：多個SHEET分批查詢匯出
/**
* 針對幾百萬的記錄數可以呼叫該方法分多批次查出然后寫入到EXCEL的多個SHEET中
* 注意：
* perSheetRowCount % pageSize要能整除 為了簡潔，非整除這塊不做處理
* 每次查詢出來的記錄數量不宜過大，根據記憶體大小設定合理的每次查詢記錄數，不會記憶體溢位即可，
*
* @throws IOException
*/
@Test
public void writeExcelMoreSheetMoreWrite() throws IOException {
// 生成EXCEL并指定輸出路徑
OutputStream out = new FileOutputStream("E:\\temp\\withoutHead3.xlsx");
ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
// 設定SHEET名稱
String sheetName = "測驗SHEET";
// 設定標題
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("用戶ID"));
titles.add(Arrays.asList("名稱"));
titles.add(Arrays.asList("年齡"));
titles.add(Arrays.asList("生日"));
table.setHead(titles);
// 模擬分批查詢：總記錄數250條，每個SHEET存100條，每次查詢20條 則生成3個SHEET，前倆個SHEET查詢次數為5， 最后一個SHEET查詢次數為3 最后一次寫的記錄數是10
// 注：該版本為了較少資料判斷的復雜度，暫時perSheetRowCount要能夠整除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++) {
// 創建SHEET
Sheet sheet = new Sheet(i, 0);
sheet.setSheetName(sheetName + i);
if (i < sheetCount - 1) {
// 前2個SHEET, 每個SHEET查5次 每次查20條 每個SHEET寫滿100行 2個SHEET合計200行 實用環境：引數：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(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
}
} else if (i == sheetCount - 1) {
// 最后一個SHEET 實用環境不需要將最后一次分開，合成一個即可， 引數為：currentPage = i+1; pageSize = pageSize
for (int j = 0; j < lastSheetWriteCount; j++) {
// 前倆次查詢 每次查詢20條
if (j < lastSheetWriteCount - 1) {
List<List<String>> userList = new ArrayList<>();
for (int k = 0; k < 20; k++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
} else if (j == lastSheetWriteCount - 1) {
// 最后一次查詢 將剩余的10條查詢出來
List<List<String>> userList = new ArrayList<>();
Integer lastWriteRowCount = totalRowCount - (sheetCount - 1) * perSheetRowCount - (lastSheetWriteCount - 1) * pageSize;
for (int k = 0; k < lastWriteRowCount; k++) {
userList.add(Arrays.asList("ID_" + Math.random(), "小明1", String.valueOf(Math.random()), new Date().toString()));
}
writer.write0(userList, sheet, table);
}
}
}
}
writer.finish();
}
2.4 生產環境
2.4.0.Excel常量類
package com.authorization.privilege.constant;
/**
* @author qjwyss
* @description EXCEL常量類
*/
public class ExcelConstant {
/**
* 每個sheet存盤的記錄數 100W
*/
public static final Integer PER_SHEET_ROW_COUNT = 1000000;
/**
* 每次向EXCEL寫入的記錄數(查詢每頁資料大小) 20W
*/
public static final Integer PER_WRITE_ROW_COUNT = 200000;
}
注：為了書寫方便，此處倆個必須要整除，可以省去很多不必要的判斷， 另外如果自己測驗，可以改為100,20，
2.4.1.資料量少的(20W以內吧)：一個SHEET一次查詢匯出
@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);
// 設定EXCEL名稱
String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
// 設定SHEET名稱
Sheet sheet = new Sheet(1, 0);
sheet.setSheetName("系統串列sheet1");
// 設定標題
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("系統名稱"));
titles.add(Arrays.asList("系統標識"));
titles.add(Arrays.asList("描述"));
titles.add(Arrays.asList("狀態"));
titles.add(Arrays.asList("創建人"));
titles.add(Arrays.asList("創建時間"));
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);
// 下載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成功");
}
2.4.2.資料量適中（100W以內）：一個SHEET分批查詢匯出
@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);
// 設定EXCEL名稱
String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
// 設定SHEET名稱
Sheet sheet = new Sheet(1, 0);
sheet.setSheetName("系統串列sheet1");
// 設定標題
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("系統名稱"));
titles.add(Arrays.asList("系統標識"));
titles.add(Arrays.asList("描述"));
titles.add(Arrays.asList("狀態"));
titles.add(Arrays.asList("創建人"));
titles.add(Arrays.asList("創建時間"));
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);
// 寫資料 這個i的最大值直接拷貝就行了 不要改
for (int i = 0; i < writeCount; i++) {
List<List<String>> dataList = new ArrayList<>();
// 此處查詢并封裝資料即可 currentPage, pageSize這個變數封裝好的 不要改動
PageHelper.startPage(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);
}
// 下載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成功");
}
2.4.3.資料里很大（幾百萬都行）：多個SHEET分批查詢匯出
@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);
// 設定EXCEL名稱
String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
// 設定SHEET名稱
String sheetName = "系統串列sheet";
// 設定標題
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
titles.add(Arrays.asList("系統名稱"));
titles.add(Arrays.asList("系統標識"));
titles.add(Arrays.asList("描述"));
titles.add(Arrays.asList("狀態"));
titles.add(Arrays.asList("創建人"));
titles.add(Arrays.asList("創建時間"));
table.setHead(titles);
// 查詢總數并封裝相關變數(這塊直接拷貝就行了不要改)
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++) {
// 創建SHEET
Sheet sheet = new Sheet(i, 0);
sheet.setSheetName(sheetName + i);
// 寫資料 這個j的最大值判斷直接拷貝就行了，不要改動
for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {
List<List<String>> dataList = new ArrayList<>();
// 此處查詢并封裝資料即可 currentPage, pageSize這倆個變數封裝好的 不要改動
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);
}
}
// 下載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成功");
}
三、總結
造的假資料，100W條記錄，18個欄位，測驗匯出是70s，在實際上產環境使用的時候，具體的還是要看自己寫的sql的性能，sql性能快的話，會很快，
有一點推薦一下：在做分頁的時候使用單表查詢， 對于所需要處理的外鍵對應的冗余欄位，在外面一次性查出來放到map里面(推薦使用@MapKey注解)，然后遍歷list的時候根據外鍵從map中獲取對應的名稱，
一個宗旨：少發查詢sql, 才能更快的匯出，
題外話：如果資料量過大，在使用count(1)查詢總數的時候會很慢，可以通過調整mysql的緩沖池引數來加快查詢，
還有就是遇到了一個問題，使用pagehelper的時候，資料量大的時候，limit 0,20W, limit 20W,40W, limit 40W,60W, limit 60W,80W 查詢有的時候會很快，有的時候會很慢，待研究，
原文鏈接：https://blog.csdn.net/qq_35206261/article/details/88579151
著作權宣告：本文為CSDN博主「請叫我猿叔叔」的原創文章，遵循CC 4.0 BY-SA著作權協議，轉載請附上原文出處鏈接及本宣告，
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2021最新版)
2.別在再滿屏的 if/ else 了，試試策略模式，真香！！
3.臥槽！Java 中的 xx ≠ null 是什么新語法？
4.Spring Boot 2.6 正式發布，一大波新特性，，
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/376831.html
標籤：Java