easypoi 简单分sheet导出
也是从网上借鉴而来,下面放个简单的实例,就当备忘吧
poi版本 4.1.0
private void simpleExport() throws IOException{
// 创建模拟数据
List<Person> data = createData(20000);
String fileName = URLEncoder.encode("export_test" + (int)(Math.random() * 100), "UTF-8");
// 数据分组 每组一页
List<List<Person>> lists = groupList(data, 10000);
// 每一页的参数使用map保存
List<Map<String, Object>> mapList = new ArrayList<>();
int i = 0;
for (List<Person> list : lists) {
ExportParams param = new ExportParams();
param.setSheetName("sheet" + i++);
Map<String, Object> map = new HashMap<>();
// key是固定的
map.put("title", param);
map.put("entity", Person.class);
map.put("data", list);
mapList.add(map);
}
Workbook workbook = ExcelExportUtil.exportExcel(mapList, ExcelType.HSSF);
FileOutputStream outputStream = new FileOutputStream("D://" + fileName + ".xls");
workbook.write(outputStream);
}
// 分组代码
public List<List<Person>> groupList(List<Person> people, int groupNum){
int size = people.size();
int remain = size % groupNum;
int sum = size / groupNum;
List<List<Person>> resultList = new ArrayList<>();
for (int i = 0; i < sum; i++) {
List<Person> subList = new ArrayList<>();
subList = people.subList(i * groupNum, (i + 1) * groupNum).stream().collect(Collectors.toList());
resultList.add(subList);
}
if (remain != 0) {
List<Person> subList = new ArrayList<>();
// 不可以直接用subList!
subList = people.subList(sum * groupNum, size).stream().collect(Collectors.toList());
resultList.add(subList);
}
return resultList;
}
注意使用这种分页办法,分组的时候不可以直接用subList,不然会报concurrentModifyException
效果:
百度的时候还找到另一种方法,这里也贴出来,但是不知道为什么我不成功
public void exportData() throws IOException{
List<Person> data = createData(20000);
String fileName = URLEncoder.encode("export_test" + (int)(Math.random() * 100), "UTF-8");
int totalPage = (data.size() % 10000 ==0)?data.size() / 10000: data.size() / 10000+1;
int pageSize = 10000;
ExportParams exportParams = new ExportParams();
Workbook workbook = null;
workbook = ExcelExportUtil.exportBigExcel(exportParams, Person.class, new IExcelExportServer() {
/**
* obj 就是下面的totalPage,限制条件
* page 是页数,他是在分页进行文件转换,page每次+1
*/
@Override
public List<Object> selectListForExcelExport(Object obj, int page) {
//很重要!!这里面整个方法体,其实就是将所有的数据aList分批返回处理
//分批的方式很多,我直接用了subList。然后 每批不能太大。我试了30000一批,
//内存溢出,貌似 最大每批10000
if (page > totalPage) {
return null;
}
// fromIndex开始索引,toIndex结束索引
int fromIndex = (page - 1) * pageSize;
int toIndex = page != totalPage ? fromIndex + pageSize :data.size();
List<Object> list = new ArrayList<>();
list.addAll(data.subList(fromIndex, toIndex));
System.out.println(fromIndex + ",,," + toIndex);
System.out.println(list.get(0));
return list;
}
}, totalPage);
FileOutputStream outputStream = new FileOutputStream("D://" + fileName + ".xls");
workbook.write(outputStream);
}