一. 简介
导出是后盾管理系统的罕用性能，当数据量特地大的时候会内存溢出和卡顿页面，已经本人封装过一个导出，采纳了分批查问数据来防止内存溢出和应用SXSSFWorkbook形式缓存数据到文件上以解决下载大文件EXCEL卡死页面的问题。
不过一是存在封装不太敌对应用不不便的问题，二是这些poi的操作形式依然存在内存占用过大的问题,三是存在空循环和整除的时候数据有缺点的问题，以及存在内存溢出的隐患。
无意间查问到阿里开源的EasyExcel框架，发现能够将解析的EXCEL的内存占用管制在KB级别，并且相对不会内存溢出（外部实现待钻研）,还有就是速度极快，大略100W条记录，十几个字段，只须要70秒即可实现下载。
遂摈弃本人封装的，转战钻研阿里开源的EasyExcel. 不过 说实话，过后本人封装的那个还是有些技术含量的，例如：外观模式，模板办法模式，以及委托思维，组合思维，能够看看。另外，微信搜寻关注Java技术栈，发送：设计模式，能够获取我整顿的 Java 设计模式实战教程。
EasyExcel的github地址是：https://github.com/alibaba/ea…
二. 案例
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 查问有的时候会很快，有的时候会很慢，待钻研。
发表回复