HSSFWorkbook wb = new HSSFWorkbook();
OutputStream output = response.getOutputStream();
List<ActualReturnDetial> list = this.findActualReturnDetial(params);
int totle = list.size();// 获取List集合的size
int mus = 65535;// :excel表格一个工作表可以存储65536条)
int avg = totle / mus;
for (int i = 0; i < avg + 1; i++) {
HSSFSheet sheet = wb.createSheet("实际还款明细" + (i + 1));
HSSFRow row = sheet.createRow(0);
// 第一行标题
String[] head = new String[]{"机构合同编号", "拍拍贷列表号", "借款人姓名", "贷款本金", "贷款利率", "贷款期数", "还款方式",
"放款日", "到期日",
"当期期数", "当期应还款日", "当期应还本金", "当期应还利息", "当期应还逾期利息", "当期实际还款日", "当期实还本金", "当期实还利息",
"当期实还逾期利息",
"剩余总本金", "当期还款状态", "逾期天数"};
int headInt = 0;
for (String title : head) {
row.createCell(headInt++).setCellValue(title);
}
int num = i * mus;
int index = 0;
int rowInt = 1;
for (int m = num; m < list.size(); m++) {
if (index == mus) {// 判断index == mus的时候跳出当前for循环
break;
}
ActualReturnDetial actualReturnDetial = list.get(m);
// 每列对应的字段
row = sheet.createRow(rowInt++); // 创建行
row.createCell(0).setCellValue(actualReturnDetial.getLoanid());
row.createCell(1).setCellValue(actualReturnDetial.getListingid());
row.createCell(2).setCellValue(actualReturnDetial.getCmstrRealName());
row.createCell(3).setCellValue(actualReturnDetial.getAmount());
row.createCell(4).setCellValue(actualReturnDetial.getRate());
row.createCell(5).setCellValue(actualReturnDetial.getMonths());
row.createCell(6).setCellValue(actualReturnDetial.getRepType());
row.createCell(7).setCellValue(actualReturnDetial.getAuditingDate());
row.createCell(8).setCellValue(actualReturnDetial.getEndDate());
row.createCell(9).setCellValue(actualReturnDetial.getOrderid());
row.createCell(10).setCellValue(actualReturnDetial.getDuedate());
row.createCell(11).setCellValue(actualReturnDetial.getPrincipal());
row.createCell(12).setCellValue(actualReturnDetial.getInterest());
row.createCell(13).setCellValue(actualReturnDetial.getDun());
row.createCell(14).setCellValue(actualReturnDetial.getPaymentdate());
row.createCell(15).setCellValue(actualReturnDetial.getRepPrincipal());
row.createCell(16).setCellValue(actualReturnDetial.getRepInterest());
row.createCell(17).setCellValue(actualReturnDetial.getRepDun());
row.createCell(18).setCellValue(actualReturnDetial.getResiduePrincipal());
row.createCell(19).setCellValue(actualReturnDetial.getRepStatus());
row.createCell(20).setCellValue(actualReturnDetial.getCurrentdefaultdays());
index++;
}
}
response.setHeader("Content-Disposition", "attachment;filename=actualreturndetial.xls");
response.setContentType("application/ms-excel");
wb.write(output);
output.close();
java导出批量数据分多个sheet。
注意事项:
1. 执行row.createCell(1).setCellValue(actualReturnDetial.getListingid());当setCellValue的为Integer值时注意
由于会转为setCellValue(double value) 当Integer为空时会报错空指针错误。
为了避免空指针错误,把值尽量转为string再setCellValue();
2. 由于机器io限制,导出过多数据会导致机器io爆满,当导出大量数据时,最后先把导出的数据放到文件再导出文件。
最近研究发现,导出百万级数据到excel用阿里的 easyexcel导出更方便,不会oom