POI实现数据分页读取写入excel

excel工具类如下：

基本分四部走，

第一步：初始构建SXSSF对象及其样式、字体

第二步：构建标题行

第三步：写入数据

第四步：将数据写入excel

public class SXSSFExcel {
	private static Logger log = LoggerFactory.getLogger(HssfExcelTool.class);

	private SXSSFWorkbook workbook;// 工作薄

	private SXSSFSheet sheet;// 表格

	private CellStyle headStyle;// 表头单元格样式

	private CellStyle rowStyle;// 数据单元格样式

	// 初始化代码行
	public void initPageExcelExport(String sheetName, int rowAccessWindowSize) throws Exception {
		log.debug("开始初始化分页导出Excel数据文档, sheetName ：{}", sheetName);
		// 声明一个工作薄
		workbook = new SXSSFWorkbook(rowAccessWindowSize);
		// 生成一个表格
		sheet = (SXSSFSheet) workbook.createSheet(sheetName);
		sheet.setDefaultColumnWidth(20); // 统一设置列宽
		try {
			/// excel样式
			CellStyle centerStyle = workbook.createCellStyle();
			CellStyle cellStyleCenter = workbook.createCellStyle(); // 头部布局
			CellStyle cellStyleLeft = workbook.createCellStyle();

			cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
			cellStyleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
			cellStyleCenter.setWrapText(true);
			cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平布局：居左
			cellStyleLeft.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
			cellStyleLeft.setWrapText(true);

			Font font = workbook.createFont();
			font.setColor(Font.COLOR_NORMAL); // 字体颜色
			font.setFontName("黑体"); // 字体
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度

			// 设置标题单元格类型
			centerStyle.setFont(font);
			centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
			centerStyle.setWrapText(true);
			centerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
			centerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置前景填充样式
			centerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);// 前景填充色
			CreationHelper createHelper = workbook.getCreationHelper();
			// 设置标题边框
			centerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			centerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			centerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			centerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		} catch (Exception e) {
			throw new Exception();
		}
	}

	// 构建标题行
	public void buildExcelModelHead(String[] columnName) {
		// 设置表格标题行
		Row row = sheet.createRow(0);
		for (int i = 0; i < columnName.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(columnName[i]);
			cell.setCellStyle(headStyle);
		}
		log.debug("excel模板表头信息构建完成 , 数据列总数：{}", columnName.length);
	}

	// 写入数据
	public void buildExcelData(List<Map<String, Object>> dataList, String[] columnName, String[] valueName,
			Page<Map> page, ServletOutputStream out) {
		int currentPage = page.getCurrent();
		// 设置表格标题行
		int lastRow = 0;
		try {
			if (currentPage == 1) {
				buildExcelModelHead(columnName);
				lastRow = 1;
			} else {
				lastRow = (currentPage - 1) * workbook.getRandomAccessWindowSize();
			}
			Row row = null;
			// int lastRow = sheet.getLastRowNum();
			if (dataList != null) {
				// 遍历集合数据，设置数据行
				for (int i = 0; i < dataList.size(); i++) {
					row = sheet.createRow(++lastRow);
					// 为数据内容设置特点新单元格样式1 自动换行 上下居中
					Cell datacell = null;
					Map<String, Object> map = dataList.get(i);
					for (int j = 0; j < valueName.length; j++) {
						datacell = row.createCell(j);
						datacell.setCellValue(String.valueOf(map.get((String) valueName[j])).equals("null") ? ""
								: String.valueOf(map.get((String) valueName[j])));
						datacell.setCellStyle(rowStyle);
					}
					map.clear();
				}
				currentPage = currentPage + 1;
				page.setCurrent(currentPage);
				// 每创建完成一个sheet页就把数据刷新到磁盘
//				sheet.flushRows();
				dataList.clear();
			} else {
				out.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	// 导入到excel
	public void commintDataToExcel(OutputStream out) throws IOException {
		try {
			// 调用导出方法生成最终的 poi-sxssf-template.xlsx 临时文件，并且此方法包含删除此临时文件的方法
			workbook.write(out);
			// 此方法能够删除导出过程中生成的xml临时文件
			workbook.dispose();
		} catch (IOException e) {
			log.error("导入excel异常!");
			// throw new IOException();
		} finally {
			out.close();
		}
	}

}

调用案例：

 

	public void exportExcelList(QryUserParam qryUserParam, HttpServletResponse response) {
		Date endDate = qryUserParam.getEndCreateTime();
		if (endDate != null) {
			endDate = DateUtil.offsetDay(endDate, 1);
			qryUserParam.setEndCreateTime(endDate);
		}
		// 表头
		String[] title = new String[11];
		title[0] = "姓名";
		title[1] = "手机号";
		title[2] = "性别";
		title[3] = "年龄";


		// 表头和表值映射对应
		String[] keys = new String[11];
		keys[0] = "name";
		keys[1] = "phone";
		keys[2] = "sex";
		keys[3] = "age";


		List<Map<String, Object>> maps;
		Page<Map> page = new Page<Map>(0, 1000);
		try {
			maps = this.getUserList(page, qryUserParam);
		} catch (Exception e1) {
			return;
		}
		if (maps != null) {
			try (ServletOutputStream out = response.getOutputStream()) {
				String filename = "用户" + ".xlsx";
				response.setContentType("application/ms-excel;charset=UTF-8");
				response.setHeader("Content-Disposition",
						"attachment;filename=".concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));

				SXSSFExcel excelTool = new SXSSFExcel();
				excelTool.initPageExcelExport("用户表", 1000);
				int currentPage = page.getCurrent();// 当前页数
				int remainder = (int) (page.getTotal() % page.getSize());// 余数
				int pageNum = 0;
				if (remainder != 0) {
					pageNum = (int) (page.getTotal() / page.getSize() + 1);// 总页数
				} else {
					pageNum = (int) (page.getTotal() / page.getSize());// 总页数
				}
				while (currentPage < pageNum) {
					excelTool.buildExcelData(maps, title, keys, page, out);
					try {
						maps = this.getUserList(page, qryUserParam);
					} catch (Exception e) {
						log.error("第" + page.getCurrent() + "页查询失败！");
						break;
					}
					currentPage = page.getCurrent();
				}
				excelTool.commintDataToExcel(out);

				out.close();
			} catch (Exception e) {
				log.error("下载失败IOException", e);
			}
		}

		return;
	}