用过 POI 的人都知道,在 POI 以前的版本中并不支持大数据量的处理,如果数据量过多还会常报 OOM 错误,这时候调整 JVM 的配置参数也不是一个好对策(注:jdk 在 32 位系统中支持的内存不能超过 2 个 G,而在 64 位中没有限制,但是在 64 位的系统中,性能并不是太好), 好在 POI3.8 版本新出来了一个 SXSSFWorkbook 对象,它就是用来解决大数据量以及超大数据量的导入导出操作的,但是 SXSSFWorkbook 只支持. xlsx 格式,不支持. xls 格式的 Excel 文件.
这里普及一下,在 POI 中使用 HSSF 对象时,excel 2003 最多只允许存储 65536 条数据,一般用来处理较少的数据量,这时对于百万级别数据,Excel 肯定容纳不了,而且在计算机性能稍低的机器上测试,就很容易导致堆溢出.而当我升级到 XSSF 对象时,它可以直接支持 excel2007 以上版本,因为它采用 ooxml 格式.这时 excel 可以支持 1048576 条数据,单个 sheet 表就支持近 104 万条数据了, 虽然这时导出 100 万数据能满足要求,但使用 XSSF 测试后发现偶尔还是会发生堆溢出,所以也不适合百万数据的导出.
现在我们知道 excel2007 及以上版本可以轻松实现存储百万级别的数据,但是系统中的大量数据是如何能够快速准确的导入到 excel 中这好像是个难题,对于一般的 web 系统,我们为了解决成本,基本都是使用的入门级 web 服务器 tomcat,既然我们不推荐调整 JVM 的大小,那我们就要针对我们的代码来解决我们要解决的问题.在 POI3.8 之后新增加了一个类,SXSSFWorkbook,采用当数据加工时不是类似前面版本的对象,它可以控制 excel 数据占用的内存,他通过控制在内存中的行数来实现资源管理,即当创建对象超过了设定的行数,它会自动刷新内存,将数据写入文件,这样导致打印时,占用的 CPU,和内存很少.但有人会说了,我用过这个类啊,他好像并不能完全解决,当数据量超过一定量后还是会内存溢出的,而且时间还很长.对你只是用了这个类,但是你并没有针对你的需求进行相应的设计,仅仅是用了,所以接下来我要说的问题就是,如何通过 SXSSFWorkbook 以及相应的写入设计来实现百万级别的数据快速写入.
我先举个例子,以前我们数据库中存在大量的数据,我们要查询,怎么办?我们在没有经过设计的时候是这样来处理的,先写一个集合,然后执行 jdbc,将返回的结果赋值给 list,然后再返回到页面上,但是当数据量大的时候,就会出现数据无法返回,内存溢出的情况,于是我们在有限的时间和空间下,通过分页将数据一页一页的显示出来,这样可以避免了大数据量数据对内存的占用,也提高了用户的体验,在我们要导出的百万数据也是一个道理,内存突发性占用,我们可以限制导出数据所占用的内存,这里我先建立一个 list 容器,list 中开辟 10000 行的存储空间,每次存储 10000 行,用完了将内容清空,然后重复利用,这样就可以有效控制内存,所以我们的设计思路就基本形成了,所以分页数据导出共有以下 3 个步骤:
1,求数据库中待导出数据的行数 2,根据行数求数据提取次数 3,按次数将数据写入文件
通过以上步骤在效率和用户体验性上都有了很高的提高,接下来上代码
到目前已经可以实现百万数据的导出了,但是当我们的业务数据超过 200 万,300 万了呢?如何解决?
[java] view plain copy
publicvoid exportBigDataExcel(ValueDataDto valueDataDto,String path)
throwsIOException{
// 最重要的就是使用 SXSSFWorkbook,表示流的方式进行操作
// 在内存中保持 100 行,超过 100 行将被刷新到磁盘
SXSSFWorkbook wb =newSXSSFWorkbook(100);
Sheet sh = wb.createSheet();// 建立新的 sheet 对象
Row row = sh.createRow(0); // 创建第一行对象
// ----------- 定义表头 -----------
Cell cel0 = row.createCell(0);
cel0.setCellValue("1");
Cell cel2 = row.createCell(1);
cel2.setCellValue("2");
Cell cel3 = row.createCell(2);
cel3.setCellValue("3");
Cell cel4 = row.createCell(3);
// ---------------------------
List list =newArrayList<valuedatabean>();
// 数据库中存储的数据行
int page_size =10000;
// 求数据库中待导出数据的行数
int list_count =this.daoUtils.queryListCount(this.valueDataDao
.queryExportSQL(valueDataDto).get("count_sql"));
// 根据行数求数据提取次数
int export_times = list_count % page_size >0? list_count / page_size
+1: list_count / page_size;
// 按次数将数据写入文件
for(int j =0; j <export_times; j++){
list =this.valueDataDao.queryPageList(this.valueDataDao
.queryExportSQL(valueDataDto).get("list_sql"), j +1,
page_size);
int len = list.size()< page_size ? list.size(): page_size;
[java] view plain copy
"white-space:pre"> for(int i =0; i <len; i++){
Row row_value = sh.createRow(j * page_size + i +1);
Cell cel0_value = row_value.createCell(0);
cel0_value.setCellValue(list.get(i).getaa());
Cell cel2_value = row_value.createCell(1);
cel2_value.setCellValue(list.get(i).getaa());
Cell cel3_value = row_value.createCell(2);
cel3_value.setCellValue(list.get(i).getaa_person());
}
list.clear();// 每次存储 len 行,用完了将内容清空,以便内存可重复利用
}
FileOutputStream fileOut =newFileOutputStream(path);
wb.write(fileOut);
fileOut.close();
wb.dispose();
}
这时,直接打印数据到一个工作簿的一个工作表是实现不了的,必须拆分到多个工作表,或者多个工作簿中才能实现.因为一个 sheet 最多行数为 1048576.
下面就以这种思路提供另外一种解决方案,直接上代码(后面会附上测试数据库,及案例需要的 jar 包)
数据库截图:案例执行结果截图:
[java] view plain copy
publicstaticvoid main(String[] args)throwsException{
Test3SXXFS tm =newTest3SXXFS();
tm.jdbcex(true);
}
publicvoid jdbcex(boolean isClose)throwsInstantiationException,IllegalAccessException,
ClassNotFoundException,SQLException,IOException,InterruptedException{
String xlsFile ="f:/poiSXXFSBigData.xlsx"; // 输出文件
// 内存中只创建 100 个对象,写临时文件,当超过 100 条,就将内存中不用的对象释放.
Workbook wb =newSXSSFWorkbook(100); // 关键语句
Sheet sheet =null; // 工作表对象
Row nRow =null; // 行对象
Cell nCell =null; // 列对象
// 使用 jdbc 链接数据库
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url ="jdbc:mysql://localhost:3306/bigdata?characterEncoding=UTF-8";
String user ="root";
String password ="123456";
// 获取数据库连接
Connection conn =DriverManager.getConnection(url, user,password);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql ="select * from hpa_normal_tissue limit 1000000"; //100 万测试数据
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
long startTime =System.currentTimeMillis(); // 开始时间
System.out.println("strat execute time:"+ startTime);
int rowNo =0; // 总行号
int pageRowNo =0; // 页行号
while(rs.next()){
// 打印 300000 条后切换到下个工作表,可根据需要自行拓展,2 百万,3 百万... 数据一样操作,只要不超过 1048576 就可以
if(rowNo%300000==0){
System.out.println("Current Sheet:"+ rowNo/300000);
sheet = wb.createSheet("我的第"+(rowNo/300000)+"个工作簿");// 建立新的 sheet 对象
sheet = wb.getSheetAt(rowNo/300000); // 动态指定当前的工作表
pageRowNo =0; // 每当新建了工作表就将当前工作表的行号重置为 0
}
rowNo++;
nRow = sheet.createRow(pageRowNo++); // 新建行对象
// 打印每行,每行有 6 列数据 rsmd.getColumnCount()==6 --- 列属性的个数
for(int j=0;j<rsmd.getColumnCount();j++){
nCell = nRow.createCell(j);
nCell.setCellValue(rs.getString(j+1));
}
if(rowNo%10000==0){
System.out.println("row no:"+ rowNo);
}
// Thread.sleep(1); // 休息一下,防止对 CPU 占用,其实影响不大
}
long finishedTime =System.currentTimeMillis();// 处理完成时间
System.out.println("finished execute time:"+(finishedTime - startTime)/1000+"m");
FileOutputStream fOut =newFileOutputStream(xlsFile);
wb.write(fOut);
fOut.flush(); // 刷新缓冲区
fOut.close();
long stopTime =System.currentTimeMillis(); // 写文件时间
System.out.println("write xlsx file time:"+(stopTime - startTime)/1000+"m");
if(isClose){
this.close(rs, stmt, conn);
}
}
// 执行关闭流的操作
privatevoid close(ResultSet rs,Statement stmt,Connection conn)throwsSQLException{
rs.close();
stmt.close();
conn.close();
}
完美!!!! 数据库脚本及案例相关 jar 包:http://pan.baidu.com/s/1pKXQp55
来源: http://www.bubuko.com/infodetail-2454926.html