一. 简介
excel导出，如果数据量在百万级，会出现俩点内存溢出的问题：
1. 查询数据量过大，导致内存溢出。 该问题可以通过分批查询来解决；
2. 最后下载的时候大EXCEL转换的输出流内存溢出；该方式可以通过新版的SXSSFWorkbook来解决，可通过其构造函数执指定在内存中缓存的行数，剩余的会自动缓存在硬盘的临时目录上，同时，并不会存在页面卡顿的情况；
3. 为了能够使用不同的mapper并分批写数据, 采用了外观模式和模板方法模式，大体分三步：
a. 根据总数量生成excel，确定sheet的数量和写标题；
b. 写数据，在可变的匿名内部类中实现写入逻辑；
c. 转换输出流进行下载；
4. 使用案例在3.3 ServiceImpl中，可自行书写。
5. 最近太忙，写的太仓促，性能我感觉还有一倍的优化空间，比如循环次数，现在存在无意义空循环（这个耗时比较多）的情况，缓冲流，mybatis的浮标等，待优化........
6. 优化空间很大 刚试了下 把空循环去掉 4个字段 90W条数据 40s 180W 75s 300W 122s
7. 转战阿里开源的EasyExcel了， 那个内存控制在kb级别，绝对不会内存溢出。使用说明请参见博主的另一篇文章：
阿里开源（EasyExcel）---导出EXCEL
8. 导入的也有，请参见另一篇文章： 阿里开源（EasyExcel）---导入EXCEL
二. 工具代码
2.1 配置
2.1.1
<!-- poi -->
<dependency>
<groupId></groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>注： 如果是springboot2.0,则不需要poi依赖，如果是1.0，则需要poi依赖，并且poi和poi-ooxml的版本要保持一致。
别的依赖我就不加了。
2.1.2
# pagehelper
pagehelper:
helperDialect: mysql
reasonable: false # 如果没有数据 返回空 而非最后一页的数据
supportMethodsArguments: true
params: count=countSql
returnPageInfo: check注： reasonable一定要为false, 其他的我就不粘了。
2.2 ExcelConstant
package ;
/**
* @author qjwyss
* @date 2018/9/19
* @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;
/**
* 每个sheet的写入次数 5
*/
public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
}
注： xlsx模式的excel每个sheet最多存储104W，此处我就每个sheet存储了 100W数据；每次查询20W数据； 自己根据内存来调合适的大小，写入次数待优化。
2.3 写数据委托类
package ;
import ;
/**
* @author qjwyss
* @date 2018/9/20
* @description EXCEL写数据委托类
*/
public interface WriteExcelDataDelegated {
/**
* EXCEL写数据委托类 针对不同的情况自行实现
*
* @param eachSheet 指定SHEET
* @param startRowCount 开始行
* @param endRowCount 结束行
* @param currentPage 分批查询开始页
* @param pageSize 分批查询数据量
* @throws Exception
*/
public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;
}
2.4 DateUtil工具类（非必须）
package ;
import ;
import ;
/**
* @author qjwyss
* @date 2018/9/20
* @description 日期工具类
*/
public class DateUtil {
public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";
/**
* 将日期转换为字符串
*
* @param date DATE日期
* @param format 转换格式
* @return 字符串日期
*/
public static String formatDate(Date date, String format) {
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
return (date);
}
}
2.5 POI工具类
package ;
import ;
import ;
import ;
import ;
import ;
import org.;
import org.;
import ;
import ;
import ;
import ;
import ;
/**
* @author qjwyss
* @date 2018/9/18
* @description POI导出工具类
*/
public class PoiUtil {
private final static Logger logger = ();
/**
* 初始化EXCEL(sheet个数和标题)
*
* @param totalRowCount 总记录数
* @param titles 标题集合
* @return XSSFWorkbook对象
*/
public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {
// 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
(totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));
// 根据总记录数创建sheet并分配标题
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet sheet = ("sheet" + (i + 1));
SXSSFRow headRow = (0);
for (int j = 0; j < ; j++) {
SXSSFCell headRowCell = (j);
(titles[j]);
}
}
return wb;
}
/**
* 下载EXCEL到本地指定的文件夹
*
* @param wb EXCEL对象SXSSFWorkbook
* @param exportPath 导出路径
*/
public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
FileOutputStream fops = null;
try {
fops = new FileOutputStream(exportPath);
(fops);
} catch (Exception e) {
();
} finally {
if (null != wb) {
try {
();
} catch (Exception e) {
();
}
}
if (null != fops) {
try {
();
} catch (Exception e) {
();
}
}
}
}
/**
* 下载EXCEL到浏览器
*
* @param wb EXCEL对象XSSFWorkbook
* @param response
* @param fileName 文件名称
* @throws IOException
*/
public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
("Content-disposition", "attachment; filename="
+ new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名
OutputStream outputStream = null;
try {
outputStream = ();
(outputStream);
} catch (Exception e) {
();
} finally {
if (null != wb) {
try {
();
} catch (Exception e) {
();
}
}
if (null != outputStream) {
try {
();
} catch (Exception e) {
();
}
}
}
}
/**
* 导出Excel到本地指定路径
*
* @param totalRowCount 总记录数
* @param titles 标题
* @param exportPath 导出路径
* @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
* @throws Exception
*/
public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
("开始导出：" + (new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
// 初始化EXCEL
SXSSFWorkbook wb = (totalRowCount, titles);
// 调用委托类分批写数据
int sheetCount = ();
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet eachSheet = (i);
for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
int endRowCount = startRowCount + pageSize - 1;
(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
}
}
// 下载EXCEL
(wb, exportPath);
("导出完成：" + (new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
/**
* 导出Excel到浏览器
*
* @param response
* @param totalRowCount 总记录数
* @param fileName 文件名称
* @param titles 标题
* @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
* @throws Exception
*/
public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {
("开始导出：" + (new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
// 初始化EXCEL
SXSSFWorkbook wb = (totalRowCount, titles);
// 调用委托类分批写数据
int sheetCount = ();
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet eachSheet = (i);
for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {
int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
int endRowCount = startRowCount + pageSize - 1;
(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
}
}
// 下载EXCEL
(wb, response, fileName);
("导出完成：" + (new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
}
三. 使用DEMO
3.1 Controller
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .*;
import ;
import ;
/**
* @author qjwyss
* @date 2018/8/30
* @description 用户控制类
*/
@Api(tags = {"UserController"}, description = "用户Controller")
@RestController
@RequestMapping(value = "/user")
public class UserController extends BaseController {
@Autowired
private UserService userService;
@ApiOperation("导出用户EXCEL")
@ApiImplicitParams({
@ApiImplicitParam(paramType = "query", dataType = "Long", name = "loginUid", value = "登录用户UID", required = true),
@ApiImplicitParam(paramType = "query", dataType = "Long", name = "uid", value = "用户UID", required = true)
})
@GetMapping("/export")
public ResultVO<Void> exportUser(@ApiIgnore UserVO userVO, HttpServletResponse response) throws Exception {
return (userVO, response);
}
}
3.2 Service
package ;
import ;
import ;
import ;
import ;
/**
* @author qjwyss
* @date 2018/8/30
* @description 用户SERVICE
*/
public interface UserService {
/**
* 导出用户EXCEL
*
* @param userVO
* @return VOID
* @throws Exception
*/
ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception;
}
3.3 ServiceImpl
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
* @author qjwyss
* @date 2018/8/30
* @description 用户SERVICEIMPL
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Autowired
private HttpServletRequest request;
@Override
public ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception {
// 总记录数
Integer totalRowCount = (userVO);
// 导出EXCEL文件名称
String filaName = "用户EXCEL";
// 标题
String[] titles = {"账号", "密码", "状态", "昵称", "职位", "手机号", "邮箱", "创建人ID", "创建时间", "修改人ID", "修改时间"};
// 开始导入
(response, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
@Override
public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
(currentPage, pageSize);
List<UserVO> userVOList = (userVO);
if (!(userVOList)) {
// -------------- 这一块变量照着抄就行 强迫症 后期也封装起来 ----------------------
for (int i = startRowCount; i <= endRowCount; i++) {
SXSSFRow eachDataRow = (i);
if ((i - startRowCount) < ()) {
UserVO eachUserVO = (i - startRowCount);
// --------- 这一块变量照着抄就行 强迫症 后期也封装起来 -----------------------
(0).setCellValue(() == null ? "" : ());
(1).setCellValue(() == null ? "" : ());
(2).setCellValue(() == null ? "" : (() == 1 ? "启用" : "停用"));
(3).setCellValue(() == null ? "" : ());
(4).setCellValue(() == null ? "" : ());
(5).setCellValue(() == null ? "" : ());
(6).setCellValue(() == null ? "" : ());
if (null != ()) {
(7).setCellValue(());
}
if (null != ()) {
(8).setCellValue(((), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
if (null != ()) {
(9).setCellValue(());
}
if (null != ()) {
(10).setCellValue(((), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
}
}
}
}
});
return ("导出用户EXCEL成功");
}
}
3.4 mapper
package ;
import ;
import ;
import ;
/**
* @author qjwyss
* @date 2018/8/29
* @description 用户MAPPER
*/
public interface UserMapper {
/**
* 查询用户列表
*
* @param userVO
* @return UserVO集合
* @throws Exception
*/
List<UserVO> selectUserVOList(UserVO userVO) throws Exception;
/**
* 查询用户数量
*
* @param userVO
* @return 用户数量
* @throws Exception
*/
Integer selectUserVOCount(UserVO userVO) throws Exception;
}3.5
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/" >
<mapper namespace="" >
<resultMap type="" >
<id column="uid" property="uid" jdbcType="BIGINT" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="state" property="state" jdbcType="INTEGER" />
<result column="nickname" property="nickname" jdbcType="VARCHAR" />
<result column="position" property="position" jdbcType="VARCHAR" />
<result column="mobile" property="mobile" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
<result column="create_uid" property="createUid" jdbcType="BIGINT" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="update_uid" property="updateUid" jdbcType="BIGINT" />
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
</resultMap>
<!-- 查询用户分页列表返回MAP -->
<resultMap type="">
<id column="uid" property="uid" jdbcType="BIGINT" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="user_state" property="userState" jdbcType="INTEGER" />
<result column="nickname" property="nickname" jdbcType="VARCHAR" />
<result column="position" property="position" jdbcType="VARCHAR" />
<result column="mobile" property="mobile" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
<result column="create_uid" property="createUid" jdbcType="BIGINT" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="update_uid" property="updateUid" jdbcType="BIGINT" />
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
<result column="apartment_name" property="apartmentName" jdbcType="VARCHAR" />
<result column="role_names" property="roleNames" jdbcType="VARCHAR" />
</resultMap>
<sql >
uid, username, password, state, nickname, position, mobile, email, create_uid, create_time,
update_uid, update_time
</sql>
<!-- 查询用户列表 -->
<select parameterType="" resultMap="SelectUserVOListMap">
SELECT
, , AS user_state, , , , , U.create_uid, U.create_time,
U.update_uid, U.update_time,
A.apartment_name,
(
SELECT
GROUP_CONCAT( R.role_name ) AS role_name_list
FROM
user_role AS UR
LEFT JOIN role AS R ON =
WHERE
> 0
AND =
GROUP BY
) role_names
FROM
`user` AS U
LEFT JOIN user_apartment AS UA ON =
LEFT JOIN apartment AS A ON =
WHERE
> 0
<if test="userState != null">
AND = #{userState,jdbcType=INTEGER}
</if>
<if test="nickname != null and nickname != ''">
AND LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
</if>
<if test="apartmentName != null and apartmentName != ''">
AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
</if>
ORDER BY U.create_time DESC
</select>
<!-- 查询用户列表数量 -->
<select parameterType="" resultType="" >
SELECT
COUNT(1)
FROM
`user` AS U
LEFT JOIN user_apartment AS UA ON =
LEFT JOIN apartment AS A ON =
WHERE
> 0
<if test="userState != null">
AND = #{userState,jdbcType=INTEGER}
</if>
<if test="nickname != null and nickname != ''">
AND LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
</if>
<if test="apartmentName != null and apartmentName != ''">
AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
</if>
</select>
</mapper>4. 测试
每个sheet存100W，每次查询写20W，每条记录10个字段，时间如下：
| 数据量 | 时间 |
| 100W | 3.5分钟 |
| 150W | 7分钟 |