poi导出大数据 1000万

最新推荐文章于 2024-03-06 19:09:22 发布

程门立雪_

最新推荐文章于 2024-03-06 19:09:22 发布

阅读量2.2k

点赞数 9

文章标签：大数据 poi

本文链接：https://blog.csdn.net/sxj_world/article/details/74326618

版权

/**
	 * 
	 * 方法: exportUserToExcel <br>
	 * 描述: 导出excel <br>
	 * 作者: Teacher song<br>
	 * 时间: 2017年7月4日 上午8:55:35
	 * @param request
	 * @param response
	 * @throws Exception 
	 */
	@RequestMapping("exportUserToExcel.do")
	public void exportUserToExcel(UserBean userBean,HttpServletRequest request,HttpServletResponse response) throws Exception{
		SXSSFWorkbook wb = new SXSSFWorkbook(5000);//内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘     
		int count = userService.getUserExcelCount(userBean);
		int page = (int) Math.ceil(count / 500000)+1;
		for (int i = 1; i <= page; i++) {
			System.out.println(i);
			userBean.setPage(i);
			userBean.setRows(500000);
			userBean.calculate();
			List<UserBean> userList = userService.getUserExcelList(userBean);
			Sheet sheet = wb.createSheet("sheet"+i); 
			Row row = sheet.createRow(0);
        	Cell cell = row.createCell(0);
	        cell.setCellValue("id");
	        cell = row.createCell(1);
	        cell.setCellValue("员工姓名");
	        cell = row.createCell(2);
	        cell.setCellValue("员工性别");
	        cell = row.createCell(3);
	        cell.setCellValue("开号日期");
	        cell = row.createCell(4);
	        cell.setCellValue("账号");
	        cell = row.createCell(5);
	        cell.setCellValue("密码");
	        cell = row.createCell(6);
	        cell.setCellValue("头像");
	        cell = row.createCell(7);
	        cell.setCellValue("状态");
	        cell = row.createCell(8);
	        cell.setCellValue("备注");
	        for (int j = 0; j < userList.size(); j++) {
	        	row = sheet.createRow(j+1);
	        	row.createCell(0).setCellValue(userList.get(i).getId());
				row.createCell(1).setCellValue(userList.get(j).getName());
				row.createCell(2).setCellValue(userList.get(j).getSex() == 1 ? "男":"女");
				row.createCell(3).setCellValue(userList.get(j).getRegisTime());
				row.createCell(4).setCellValue(userList.get(j).getLoginNumer());
				row.createCell(5).setCellValue(userList.get(j).getPassword());
				row.createCell(6).setCellValue(userList.get(j).getHeadImg());
				row.createCell(7).setCellValue(userList.get(j).getStatusName());
				row.createCell(8).setCellValue(userList.get(j).getRemark());
			}
		}
		FileOutputStream fout = new FileOutputStream("G:\\students.xlsx");  
        wb.write(fout);
        System.out.println("完成");
        fout.flush();
        fout.close();
        wb.dispose();

        FileUtil.downloadFile(request, response,"G:\\students.xlsx","测试.xlsx");
        File file = new File("G:\\students.xlsx");
        file.delete();
	}