解决POI读取大Excel文件内存溢出问题
一、复现问题
构造测试数据
根据笛卡尔积算法,生成数据量大的Excel文件,示例代码如下:
package com.test.demo;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import java.util.ArrayList;
import java.util.List;
/**
* @description 构造测试数据
* @author rongrong
* @version 1.0
* @date 2020/11/7 19:17
*/
public class ConstructData {
public static void main(String[] args) {
List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd","aa", "bb", "cc", "dd","aa", "bb", "cc", "dd","aa", "bb", "cc", "dd");
List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1", "bb1", "cc1", "dd1", "bb1", "cc1", "dd1", "bb1", "cc1", "dd1", "bb1", "cc1", "dd1", "bb1", "cc1", "dd1", "bb1", "cc1", "dd1");
List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2", "bb1", "cc1", "dd1", "cc3", "dd3", "bb1", "cc3", "dd3", "bb1");
List<String> row4 = CollUtil.newArrayList("cc3", "dd3", "bb1","aa3", "bb3", "cc3", "dd3", "bb1", "cc3", "dd3", "bb1", "cc1", "dd1");
List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4", "bb1", "cc1" , "cc3", "dd3", "bb1", "cc3", "dd3", "bb1", "cc3", "dd3", "bb1","dd1");
List<List<String>> list = CollUtil.newArrayList(row1, row2, row3, row4, row5);
List<List<String>> result = new ArrayList<List<String>>();
descartes(list, result, 0, new ArrayList<String>());
BigExcelWriter writer= ExcelUtil.getBigWriter("e:/测试数据.xlsx");
// 一次性写出内容,使用默认样式
writer.write(result);
// 关闭writer,释放内存
writer.close();
System.out.println("数据写入成功!!");
}
/***
* 笛卡尔积算法
* @param dimvalue
* @param result
* @param layer
* @param curList
*/
private static void descartes(List<List<String>> dimvalue, List<List<String>> result, int layer, List<String> curList) {
if (layer < dimvalue.size() - 1) {
if (dimvalue.get(layer).size() == 0) {
descartes(dimvalue, result, layer + 1, curList);
} else {
for (int i = 0; i < dimvalue.get(layer).size(); i++) {
List<String> list = new ArrayList<String>(curList);
list.add(dimvalue.get(layer).get(i));
descartes(dimvalue, result, layer + 1, list);
}
}
} else if (layer == dimvalue.size() - 1) {
if (dimvalue.get(layer).size() == 0) {
result.add(curList);
} else {
for (int i = 0; i < dimvalue.get(layer).size(); i++) {
List<String> list = new ArrayList<String>(curList);
list.add(dimvalue.get(layer).get(i));
result.add(list);
}
}
}
}
}
生成Excel文件大小如图所示:
还是不够大,那么我在加工下,这次肯定数据量肯定够大了。
接下来,我们用poi读取Excel文件,示例代码如下:
public static void main(String[] args) throws IOException {
// 获取文件路径和文件
FileInputStream fis = new FileInputStream("e:/测试数据.xlsx");
// 将输入流转换为工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook(fis);
// 获取第一个工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//遍历所有的行
for (Row row : sheet) {
System.out.println("开始遍历第" + row.getRowNum() + "行数据:");
//遍历所有的列
for (Cell cell : row) {
System.out.print(cell.getStringCellValue() + " ");
}
System.out.println(" ");
}
}
运行结果
果然不负众望,终于内存溢出了,如下图所示:
二、解决方法
使用Excel Streaming Reader,这个第三方工具会把一部分的行(可以设置)缓存到内存中,在迭代时不断加载行到内存中,而不是一次性的加载所有记录到内存,这样就可以不断的读取excel内容并且不影响内存的使用。
但是这个工具也有一定的限制:只能用于读取excel的内容,写入操作不可用;可以使用getSheetAt()方法获取到对应的Sheet,因为当前只是加载了有限的row在内存中,因此不能随机访问row,即不能使用getRow(int rowNum)方法;由于行数据已经加载到了内存,因此可以随机的访问Cell数据,即可以使用getCell(int cellnum)方法。使用这个工具,建议使用迭代器来进行迭代。具体内容可以参见:https://github.com/monitorjbl/excel-streaming-reader。
在pom.xml文件中加入依赖:
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.0.0</version>
</dependency>
具体示例代码如下:
public static void main(String[] args) throws IOException {
FileInputStream in = new FileInputStream("e:/测试数据.xlsx");
Workbook wk = StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数,默认是10
.bufferSize(8192) //读取资源时,缓存到内存的字节大小,默认是1024
.open(in); //打开资源,必须,可以是InputStream或者是File,注意:只能打开XLSX格式的文件
Sheet sheet = wk.getSheetAt(0);
//遍历所有的行
for (Row row : sheet) {
System.out.println("开始遍历第" + row.getRowNum() + "行数据:");
//遍历所有的列
for (Cell cell : row) {
System.out.print(cell.getStringCellValue() + " ");
}
System.out.println(" ");
}
}
运行结果
这次就很稳定奔放了,不报错,而且速度很快,如下图所示:
参考文章
优秀不够,你是否无可替代
软件测试交流QQ群:721256703,期待你的加入!!
欢迎关注我的微信公众号:软件测试君
![](https://www.cnblogs.com/images/cnblogs_com/longronglang/1061549/o_QQ%E6%88%AA%E5%9B%BE20190728134401.jpg)