Java 亿万级数据导出到Excel
由于项目需要，处理百万级数据导出问题。
直接开始撸代码~
- 导入依赖
<!-- 文件导出-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
- PoiUtil导出工具类
package com.aa.utils;
import com.aa.excel.ExcelConstant;
import com.aa.excel.WriteExcelDataDelegated;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
public class PoiUtil {
private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);
/**
* 初始化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 = wb.createSheet("sheet" + (i + 1));
SXSSFRow headRow = sheet.createRow(0);
for (int j = 0; j < titles.length; j++) {
SXSSFCell headRowCell = headRow.createCell(j);
headRowCell.setCellValue(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);
wb.write(fops);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != fops) {
try {
fops.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 下载EXCEL到浏览器
*
* @param wb EXCEL对象XSSFWorkbook
* @param response
* @param fileName 文件名称
* @throws IOException
*/
public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {
response.setHeader("Content-disposition", "attachment; filename="
+ new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != outputStream) {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 导出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 {
logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
// 初始化EXCEL
SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
// 调用委托类分批写数据
int sheetCount = wb.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
SXSSFSheet eachSheet = wb.getSheetAt(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;
writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
}
}
// 下载EXCEL
PoiUtil.downLoadExcelToLocalPath(wb, exportPath);
logger.info("导出完成：" + DateUtil.formatDate(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 {
logger.info("开始导出：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
// 初始化EXCEL
SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);
int num = totalRowCount % ExcelConstant.PER_WRITE_ROW_COUNT == 0 ? totalRowCount / ExcelConstant.PER_WRITE_ROW_COUNT : (totalRowCount / ExcelConstant.PER_WRITE_ROW_COUNT) + 1;
// 调用委托类分批写数据
int sheetCount = wb.getNumberOfSheets();
//判断是需要几个sheet
//假设为13，最终结果要求
//0,10,20,30,40
//50,60,70,80,90
//100,110,120
ArrayList<Integer> list = new ArrayList<>();
if (num <= ExcelConstant.PER_WRITE_ROW_COUNT) {
list.add(num);
} else {
for (int i = 0; i < num / ExcelConstant.PER_WRITE_ROW_COUNT; i++) {
list.add(ExcelConstant.PER_WRITE_ROW_COUNT);
}
if (num % ExcelConstant.PER_WRITE_ROW_COUNT != 0) {
list.add(num % ExcelConstant.PER_WRITE_ROW_COUNT);
}
}
//此list.size相当于sheet的页数。
for (int i = 0; i < list.size(); i++) {
SXSSFSheet eachSheet = wb.getSheetAt(i);
for (int j = 1; j <= list.get(i); j++) {
//获取当前页等信息
int currentPage = ((i * ExcelConstant.PER_WRITE_ROW_COUNT) + j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT;
int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
int endRowCount = startRowCount + pageSize - 1;
//调用方法从数据库查询，并且写入到Excel中
writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);
}
}
// 下载EXCEL
PoiUtil.downLoadExcelToWebsite(wb, response, fileName);
logger.info("导出完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
}
}
- ExcelConstant常量类
/**
* @create 2019/11/19 17:42
* @About EXCEL常量类
*/
public class ExcelConstant {
/**
* 每个sheet存储的记录数 100W
*/
public static final Integer PER_SHEET_ROW_COUNT = 100000;
/**
* 每次向EXCEL写入的记录数(查询每页数据大小) 20W
*/
public static final Integer PER_WRITE_ROW_COUNT = 20000;
/**
* 每个sheet的写入次数 5
*/
public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
}
- WriteExcelDataDelegated
import org.apache.poi.xssf.streaming.SXSSFSheet;
/**
* @create 2019/11/19 17:45
* @About 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;
}
- 实现层使用
//userInfoQuery传入的参数
@Override
public List<userInfoVO> getJcFullCodeListExports(userInfoQuery userInfoQuery, HttpServletResponse resp) {
// 总记录数
Integer totalRowCount = userInfoQuery.getTotal();
// 导出EXCEL文件名称
String filaName = "用户表导出";
String[] titles = {"名称", "编号", "编码", "班级"};
// 开始导入
try {
PoiUtil.exportExcelToWebsite(resp, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
@Override
public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {
userInfoQuery.setPage(currentPage);//设置当前页
userInfoQuery.setSize(pageSize);//设置页面查询条数
//根据条件从数据库查询数据
List<userInfoVO> exportVOList = statisticalAnalysisMapper.getJcFullCodeInfoList(userInfoQuery);
//绑定数据
if (!CollectionUtils.isEmpty(exportVOList)) {
// -------------- 这一块变量照着抄就行 强迫症 后期也封装起来 ----------------------
for (int i = startRowCount; i <= endRowCount; i++) {
SXSSFRow eachDataRow = eachSheet.createRow(i);
if ((i - startRowCount) < exportVOList.size()) {
userInfoVO eachUserVO = exportVOList.get(i - startRowCount);
// --------- 这一块变量照着抄就行 强迫症 后期也封装起来 -----------------------
int column = 0;
eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getQame()));
eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getACode()));
eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getQCode()));
eachDataRow.createCell(column++).setCellValue(StringUtils.defaultString(eachUserVO.getOName()));
}
}
}
}
});
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
- 前端页面JS
//导出指定数据
$scope.exportByParams = function () {
$('.load-bg').fadeIn(200);
var req_data = $.extend({}, $scope.form, $scope.page);
if (req_data['total'] != 0) {
var params = Object.keys(req_data).map(function (key) {
return encodeURIComponent(key) + "=" + encodeURIComponent(req_data[key]);
}).join("&");
var URLINFO = '';
if ($scope.form.type === '1') {
URLINFO = "/statistical/getUserListExports";
} else if ($scope.form.type === '2') {
URLINFO = "/statistical/getClassInfoListExports";
}
window.location.href = URLINFO + "?" + params;
alert("点击确定后请耐心等待片刻，约2分钟，待下载完成后可继续操作！");
$('.load-bg').fadeOut(200);
} else {
$('.load-bg').fadeOut(200);
alert("无数据！")
$('.details_export').fadeOut(200);
}
};
赞 (0)