大数据量的导出(poi)

一.大数据量的导出：

    采用分页的思想，分多个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();
			}
		}
	}

 

 

转载于:https://my.oschina.net/u/3110937/blog/1616236