利用 hutool工具类ExcelWriter 导出 百万级数据及导出空的数据问题解决

一、Java 通过hutool工具类ExcelWriter 导出

运用到多线程分页查询

这个采用的是Java的utool工具类ExcelWriter 导出
踩过一些坑，尽量用一条sql 将所有数据查询出来，否则再循环时查询会随着表数据的增大查询速度会成倍增加，所以
建议用一条sql把查询出结果。实测21列1.1w 多条数据查询 4067ms左右。
还可以进一步优化。
1.大量数据导出，先调整一下前端请求的response的超时时间 timeout: 1000 * 60 * 10 // 调整个10分钟
2.直接上Java代码
注：page 和Ipage 主要是用到分页，当前页数和大小，自定义分页查询;
每个sheet的导入数据list容易数据过大，请自行优化，后期有时间再做这个优化

 controller 代码：

/**
* 导出用户信息
*/
@GetMapping("/exportUser")
public void exportUser(UserManagerReqParam user, HttpServletResponse response){
// 开始时间
long start = System.currentTimeMillis();
PageParam<User> userPageParam = new PageParam<>();
userPageParam.setCurrent(1);
userPageParam.setSize(10L);
IPage<UserManagerParam> userPage = userService.getUserInfoPage(userPageParam,user);
// 总共有多少条数据
Long total = userPage.getTotal();
// 用户有很多，考虑2000条以上数据的导出 一个之多104w 行数据
Long rowMaxCount = 500000L;
// 每一次查询条数
Long eachCount = 1000L;
// 这里不用PageParam<User> ，为了方便自由调整查询条数
Page<User> pages = new Page<>();
pages.setCurrent(1);

// 控制list 大小
Long listSize = 2000L;
// list 分片的数量
int listNums = getPageSize(total,listSize);
List<UserManagerParam> list = new ArrayList<>();

String filepath = "";
// 查询记录数
//通过工具类创建writer
ExcelWriter writer = ExcelUtil.getBigWriter();
String fileName = "用户信息表.xls";
if (total<=rowMaxCount){
if (total<= eachCount) {
pages.setSize(total);
userPage = userService.getUserInfoPage(pages, user);
exportExcel(userPage.getRecords(), 1, 1, response,writer);
// 导出
PoiExcelUtil.writeExcel(fileName,writer,response);
} else {
// 开启多线程查询，每eachCount(100)行数据为一个线程开始
int pageSize = getPageSize(total, eachCount);
// ExecutorService execservice = new ThreadPoolExecutor(4,10,200L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue(10));
ExecutorService execservice =Executors.newFixedThreadPool(15);
try {
List<Callable<List<UserManagerParam>>> tasks = new ArrayList<Callable<List<UserManagerParam>>>();
for (int i = 1; i <= pageSize; i++) {
Page<User> pagesIndex = new Page<>();
pagesIndex.setCurrent(i);
pagesIndex.setSize(eachCount);
Callable<List<UserManagerParam>> task = new AnalysisSalseTask(userService, user, pagesIndex);
tasks.add(task);
}
List<Future<List<UserManagerParam>>> futures = execservice.invokeAll(tasks);
if (futures != null && futures.size() > 0) {
for (Future<List<UserManagerParam>> future : futures) {
list.addAll(future.get());
}
}
execservice.shutdown();
tasks.clear();
long end = System.currentTimeMillis();
System.out.println("线程查询数据用时:"+(end-start)+"ms");
} catch (Exception e) {
System.out.println("多线程查询异常");
}
exportExcel(list, 1, 0, response, writer);
list.clear();
// 导出
PoiExcelUtil.writeExcel(fileName,writer,response);
}
} else {
// 分多少个 sheet
int pageSize = getPageSize(total,rowMaxCount);
eachCount = 1000L;
for (int i = 1; i <= pageSize; i++) {
list.clear();
int size = getPageSize(rowMaxCount, eachCount);
try {
ExecutorService execservice = new ThreadPoolExecutor(4, 10, 200L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue(10));
List<Callable<List<UserManagerParam>>> tasks = new ArrayList<Callable<List<UserManagerParam>>>();
for (int j = 1; j <= size; j++) {
Page<User> pagesIndex = new Page<>();
pagesIndex.setSize(eachCount);
pagesIndex.setCurrent((i-1) * size + j);
Callable<List<UserManagerParam>> task = new AnalysisSalseTask(userService, user, pagesIndex);
tasks.add(task);
}
List<Future<List<UserManagerParam>>> futures = execservice.invokeAll(tasks);
if (Objects.nonNull(futures) && futures.size() > 0) {
for (Future<List<UserManagerParam>> future : futures) {
list.addAll(future.get());
}
}
tasks.clear();
execservice.shutdown();
long end = System.currentTimeMillis();
System.out.println("线程查询数据用时:"+(end-start)+"ms");
} catch (Exception e) {
System.out.println("多线程查询异常");
}
// 序号 (i-1) * rowMaxCount + 1
int rowStart = new BigDecimal(i - 1).multiply(new BigDecimal(rowMaxCount)).add(new BigDecimal(1)).intValue();
//方法1： 导出到一个临时文件，
// 然后合并小于50W行的Excel到100W行为一个Excel文件,此处跳过，直接是100W行为一个文件
// 然后将100W的每一个Excel文件进行压缩

// 方法2：分多个sheet 导出
// 此时达到 rowMaxCount 行数据 list 导出到excel
exportExcel(list, rowStart, i-1, response, writer);
list.clear();
}
// 导出
PoiExcelUtil.writeExcel(fileName,writer,response);
}
}
private int getPageSize(Long total, Long eachCount) {
int pageSize = new BigDecimal(total).divide(new BigDecimal(eachCount),1).intValue();
int mod = new BigDecimal(total).divideAndRemainder(new BigDecimal(eachCount))[1].intValue();
if (mod > 0) {
pageSize = pageSize + 1;
// pageSize = new BigDecimal(pageSize).add(new BigDecimal(1)).intValue();
}
return pageSize;
}

/**
* @param list 导出的数据
* @param rowStart 开始的行数
* @param pages 一共有多少页
*/
private void exportExcel(List<UserManagerParam> list,int rowStart,int pages, HttpServletResponse response,ExcelWriter writer) {

// 商品导出or模板
List<String> headerList;
String[] header = {
"序号", "用户昵称", "用户名称", "联系电话", "会员等级",
"会员类型", "用户积分", "状态","消费金额", "实付金额",
"消费次数", "平均折扣", "充值金额","充值次数", "退款金额",
"退款次数", "累计积分","当前余额", "累计余额",
"注册时间", "最近消费时间"
};
headerList = Arrays.asList(header);
writer.setSheet(pages);
Sheet sheet = writer.getSheet();
writer.merge(headerList.size() - 1, "用户信息表");
writer.writeRow(headerList);
for (int i = 0; i < headerList.size(); i++) {
if (i==19 || i==20) {
sheet.setColumnWidth(i, 30 * 256);
} else {
sheet.setColumnWidth(i, 20 * 256);
}
}
// 如果要导出的数据为空，导出一个模板(可以换成最下面的代码)
if (CollectionUtils.isEmpty(list)) {
PoiExcelUtil.writeExcel(response, writer);
return;
}
int row = rowStart;
int size = list.size();
for (UserManagerParam param : list) {
int firstRow = row + 1;
int lastRow = row + 1;
int col = -1;
// 序号
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,rowStart++);
// 用户昵称
String nickName = Objects.isNull(param.getNickName())?"":param.getNickName();
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,nickName);
// 用户名称
String realName = Objects.isNull(param.getRealName())?"":param.getRealName();
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,realName);
// 联系电话
String userMobile = Objects.isNull(param.getUserMobile())?"":param.getUserMobile();
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,userMobile);
// 会员等级
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getLevelName());
// 会员类型
String levelType = param.getLevelType() == 0 ? "普通会员": "付费会员";
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,levelType);
// 用户积分
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getScore());
// 状态
String status = param.getStatus() == 0 ? "禁用": "正常";
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,status);
// 消费金额
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getConsAmount());
// 实付金额
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getActualAmount());
// 消费次数
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getConsTimes());
// 平均折扣
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getAverDiscount());

// 充值金额
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getRechargeAmount());
// 充值次数
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getRechargeTimes());

// 退款金额
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getAfterSaleAmount());
// 退款次数
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getAfterSaleTimes());
// 当前积分
// PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getCurrentScore());
// 累计积分
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getSumScore());
// 当前余额
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getCurrentBalance());
// 累计余额
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getSumBalance());
// 注册时间
String regTime = "";
if (Objects.nonNull(param.getUserRegtime())){
regTime = DateUtil.format(param.getUserRegtime(),"yyyy-MM-dd HH:mm:ss");
}
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,regTime);
// 最近消费时间
String recTime = "";
if (Objects.nonNull(param.getUserRegtime())){
recTime = DateUtil.format(param.getReConsTime(),"yyyy-MM-dd HH:mm:ss");
}
PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,recTime);
row++;
}
}

多线程代码：

AnalysisSalseTask.java

@Slf4j
public class AnalysisSalseTask implements Callable<List<UserManagerParam>> {

private UserService userService;
private UserManagerReqParam user;
private Page<User> pages;

public AnalysisSalseTask(UserService userService, UserManagerReqParam user, Page<User> pages) {
this.userService = userService;
this.user = user;
this.pages = pages;
}

@Override
public List<UserManagerParam> call() throws Exception {
IPage<UserManagerParam> userPage = userService.getUserInfoPage(pages,user);
return userPage.getRecords();
}
}

针对 ExcelWriter 的excel操作工具类：

PoiExcelUtil.java

package com.XXX.util;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import cn.hutool.core.io.IORuntimeException;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

/**
* 功能: poi导出excel工具类
*/
public class PoiExcelUtil {

/**
* 合并单元格处理,获取合并行
*
* @param sheet
* @return List<CellRangeAddress>
*/
public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
// 获得一个 sheet 中合并单元格的数量
int sheetmergerCount = sheet.getNumMergedRegions();
// 遍历所有的合并单元格
for (int i = 0; i < sheetmergerCount; i++) {
// 获得合并单元格保存进list中
CellRangeAddress ca = sheet.getMergedRegion(i);
list.add(ca);
}
return list;
}

public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
int xr = 0;
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
for (CellRangeAddress ca : listCombineCell) {
// 获得合并单元格的起始行, 结束行, 起始列, 结束列
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
xr = lastR;
}
}

}
return xr;

}

/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row
* 行下标
* @param column
* 列下标
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}

/**
* 如果需要合并的话，就合并
*/
public static void mergeIfNeed(ExcelWriter writer, int firstRow, int lastRow, int firstColumn, int lastColumn, Object content) {
if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {
writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
} else {
writer.writeCellValue(firstColumn, firstRow, content);
}

}
public static void writeExcel(HttpServletResponse response, ExcelWriter writer) {
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=1.xls");

ServletOutputStream servletOutputStream = null;
try {
servletOutputStream = response.getOutputStream();
writer.flush(servletOutputStream);
servletOutputStream.flush();
} catch (IORuntimeException | IOException e) {
e.printStackTrace();
} finally {
writer.close();
try {
if (servletOutputStream != null) {
servletOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void writeExcel(String filename, ExcelWriter writer, HttpServletResponse response){
ServletOutputStream ouputStream = null;
try {
filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
ouputStream = response.getOutputStream();
writer.flush(ouputStream);
ouputStream.flush();
Runtime.getRuntime().gc();
} catch (Exception e) {
e.printStackTrace();
} finally {
writer.close();
if (null != ouputStream) {
try {
ouputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}

原文链接：https://blog.csdn.net/qq_26586953/article/details/109059564

注意：假入查询数据为null,导出数据时会出现空的excel

解决方案：

创建个空的对象 并添加到list 里  
if (CollectionUtils.isEmpty(itemList)){
    UserManagerParam qualitySuperviseItem = new UserManagerParam();
    List<UserManagerParam> list= new ArrayList<>();
    list.add(qualitySuperviseItem);
    excelWriter.write(list, true);
}else{
    excelWriter.write(lists, true);
}