POI读取Excel模板并导出大量数据
我在使用XSSFWorkbook读取Excel模板并导出大量数据(百万级)时,发现很长时间没有响应,debugger模式发现在读取第三四十万条数据时,程序直接停了,如下代码:
使用之前首先引入maven依赖,我使用的是POI版本为3.17
<!-- apache-poi引入 -->
<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>
问题代码:
/**
* SSOSystemPrivTask.java
* 用户对应的角色与权限
*/
public void exportUserRolePermits() {
FileOutputStream out = null;
XSSFWorkbook workbook = null;
try {
//excel模板路径
String filePath = "/template/业务系统角色与权限关联数据(模板一).xlsx";
//导出路径
String exportFilePath = CmUtil.getProps("/conf.properties").getProperty("all_permit_export_path","/");
//读取excel模板
InputStream in = SSOSystemPrivTask.class.getResourceAsStream(filePath);
workbook = new XSSFWorkbook(in);
//获取角色关联权限
List<List<String>> privList = this.ssoTaskService.getRolePermits();
if (privList != null && privList.size() > 0) {
XSSFSheet sheet0 = workbook.getSheetAt(0);//角色对应权限页签
//在相应的单元格进行赋值
for (int i = 0; i < privList.size(); i++) {
Row row = sheet0.getRow(i+1);
if (row == null) {
row = sheet0.createRow(i+1);
}
for (int j = 0; j < privList.get(i).size()