一.大数据量的导出:
采用分页的思想,分多个sheet。
public ResponseEntity exportBigDataExcel() throws Exception {
try {
int pageSize = 100000;
SXSSFWorkbook wb = new SXSSFWorkbook(100000);
Sheet sheet = null; // 工作表对象
Row nRow = null; // 行对象
Cell nCell = null;
// 获取数据库中行数
Integer dataCount = activityDataMapper.selectDataCount();
// 根据函数,获取提取次数
int exportTimes = dataCount % pageSize > 0 ? dataCount / pageSize + 1 : dataCount / pageSize;
// 按次数将数据写入文件
for (int i = 0; i < exportTimes; i++) {
sheet = wb.createSheet("百万英雄00" + i + "的sheet");
sheet = wb.getSheetAt(i);
// 第一行
nRow = sheet.createRow(0);
nCell = nRow.createCell(0);
nCell.setCellValue("ID");
nCell = nRow.createCell(1);
nCell.setCellValue("用户id");
nCell = nRow.createCell(2);
nCell.setCellValue("页面id");
nCell = nRow.createCell(3);
nCell.setCellValue("点击时间");
int pageNo = i * pageSize;
List<ActivityData> activityDataList = activityDataMapper.selectActivityDataByPage(pageNo, pageSize);
for (int j = 0; j < activityDataList.size(); j++) {
// 100000一个sheet
Row dataRow = sheet.createRow(j + 1);
nCell = dataRow.createCell(0);
nCell.setCellValue(activityDataList.get(j).getId().toString());
nCell = dataRow.createCell(1);
nCell.setCellValue(activityDataList.get(j).getAid().toString());
nCell = dataRow.createCell(2);
nCell.setCellValue(activityDataList.get(j).getPageId().toString());
nCell = dataRow.createCell(3);
nCell.setCellValue(DateUtil.dateToStr(activityDataList.get(j).getCreateD(), DateUtil.TIME_PATTERN));
}
}
String fileName = "活动数据明细.xlsx";
return ExcelUtil.outputExcel(wb, fileName);
} catch (Exception e) {
LoggerUtil.error("ActivityDataService exportBigDataExcel Exception" + e.getMessage(), e);
throw e;
}
}
public static ResponseEntity outputExcel(Workbook workbook, String fileName) throws Exception {
//将excel写入流中
ByteArrayOutputStream byteArrayOutputStream = null;
try {
byteArrayOutputStream = new ByteArrayOutputStream();
workbook.write(byteArrayOutputStream);
HttpHeaders headers = new HttpHeaders();
headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
headers.add("Pragma", "no-cache");
headers.add("Expires", "0");
headers.add("charset","utf-8");
//文件名
fileName= URLEncoder.encode(fileName, "UTF-8");
headers.add("fileName",fileName);
InputStreamResource resource = new InputStreamResource(new ByteArrayInputStream(byteArrayOutputStream.toByteArray() ));
return ResponseEntity.ok()
.headers(headers)
.contentType(MediaType.parseMediaType("application/octet-stream"))
.body(resource);
} catch (Exception e) {
return null;
}finally {
if (null != byteArrayOutputStream) {
byteArrayOutputStream.close();
}
if (null != workbook) {
workbook.close();
}
}
}