基于Java实现千万级Excel数据导出,目前有很多的实现方式,本文主要介绍基于POI的实现方式。
POI常用的两种对象分别为HSSFWorkbook 和SXSSFWorkbook,简要介绍如下:
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls,一张表最大支持65536行数据,256列,也就是说一个sheet页,最多导出6w多条数据。
XSSFWorkbook:是操作Excel2007-2010的版本,扩展名是.xlsx,它的一张表最大支持1048576行,16384列。
下面主要介绍基于SXSSFWorkbook的千万级数据导出:
SXSSFWorkbook主要原理:
借助临时存储空间生成excel,用硬盘空间换内存(就像hashmap用空间换时间一样)SXSSFWorkbook专门处理大数据,对于大型excel的创建且不会内存溢出的,就只有SXSSFWorkbook了。
SXSSFWorkbook是streaming 版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看,在此之前的excel rows都会被写入到硬盘里(Windows电脑的话,是写入到C盘根目录下的temp文件夹)。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。
实现思路:
1、根据HSSFWorkbook导出Excel时,一张sheet的最大行数,创建多张sheet表,实现千万级导出;
2、导出前需考虑自身电脑或服务器的能否支撑此数据;
3、查询官方文档,查询调用方式,提升性能。
官方demo:
mport junit.framework.Assert;
mport org.apache.poi.ss.usermodel.Cell;
mport org.apache.poi.ss.usermodel.Row;
mport org.apache.poi.ss.usermodel.Sheet;
mport org.apache.poi.ss.usermodel.Workbook;
mport org.apache.poi.ss.util.CellReference;
mport org.apache.poi.xssf.streaming.SXSSFWorkbook;
public static void main(String[] args) throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// Rows with rownum < 900 are flushed and not accessible
for(int rownum = 0; rownum < 900; rownum++){
Assert.assertNull(sh.getRow(rownum));
}
// ther last 100 rows are still in memory
for(int rownum = 900; rownum < 1000; rownum++){
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
编码实现:
public SXSSFWorkbook build() {
workbook = new SXSSFWorkbook(100);//在内存中保留100行,超过行将被刷新到磁盘,此处可不设置
workbook.setCompressTempFiles(true);//当临时文件过大时,可使用setCompressTempFiles方法进行压缩 非必须
if (!StringUtils.isEmpty(sheetData.getName())) {
List<RowData> rowDatas = sheetData.getRowDatas();//获取需要导出的数据行
int list_count = rowDatas.size();//总行数
int page_size = 60000;// 定义每页数据数量
//总数量除以每页显示条数等于页数
int export_times = list_count % page_size > 0 ? list_count / page_size + 1 : list_count / page_size;
if(export_times==0){
Sheet sheet = workbook.createSheet(sheetData.getName());//根据标题名称命名
Row header = sheet.createRow(0); // 第0行,创建属于上面Sheet的Row,参数0可以是0~65535之间的任何一个
// 产生标题列,每个sheet页产生一个标题
Cell headerCell;
List<String> headers = sheetData.getHeaders();
for (int j = 0; j < headers.size(); j++) {
headerCell = header.createCell((short) j);
headerCell.setCellValue(headers.get(j));
}
}else{
for (int m = 0; m < export_times; m++) {
int pageIndex = m+1;
Sheet sheet = workbook.createSheet(sheetData.getName()+"("+export_times+"-"+pageIndex+")");//根据标题名称命名
Row header = sheet.createRow(0); // 第0行,创建属于上面Sheet的Row,参数0可以是0~65535之间的任何一个
// 产生标题列,每个sheet页产生一个标题
Cell headerCell;
List<String> headers = sheetData.getHeaders();
for (int j = 0; j < headers.size(); j++) {
headerCell = header.createCell((short) j);
headerCell.setCellValue(headers.get(j));
}
//迭代数据
int index = m*page_size;//起始下标
int listLen = pageIndex*page_size;//遍历的集合长度
if(pageIndex==export_times){//只有一页或最后一页
listLen = list_count;
}
int rowIdx = 1;//行数
for (int i = index; i < listLen; i++) {//每页行数下标
RowData rd = sheetData.getRowDatas().get(i);
writeRowData(sheet,rd,rowIdx++);//写入数据
}
}
}
}
return workbook;
}
性能优化:
workbook = new SXSSFWorkbook(100);//在内存中保留100行,超过行将被刷新到磁盘,此处可不设置
workbook.setCompressTempFiles(true);//当临时文件过大时,可使用setCompressTempFiles方法进行压缩 非必须
附赠POI下载地址:
1、下载POI的Jar包
官网地址:https://poi.apache.org/download.html
历史版本下载:https://archive.apache.org/dist/poi/release/bin/
特别注意:SXSSFWorkbook对象,4.0.0版本的JDK需要1.8以上,如果JDK是1.7的,那么就使用3.9版本的依赖
2、所需Jar包如下
3.8版本
5.0.0版本
3、基于