Java后台获取数据库数据导出Excel传至前端下载

工作时遇到了需要在页面显示已经分页数据，提供下载按钮下载表格全部信息生成Excel输出

由于前端使用的是Angular4，所有另有区别还需要各位自行调整。附上个人代码。

angular路由跳转（SkipRouter）限制，使用的是js中的window.open("url")方法直接调用后台接口。

controller：使用HSSFWorkbook定义一张表，通过Sheet接口添加工作表，在工作表中定义表头，随后获取数据库中所需的数据，遍历后对表格中进行赋值，设置返回数据格式编码文件名等。

@RequestMapping(value = "/api/user/exportUser", method = RequestMethod.GET)
	@ResponseBody
	public void exportUser(HttpServletRequest request,HttpServletResponse response) throws Exception {
		Workbook wb = new HSSFWorkbook();
		Sheet sh = wb.createSheet();
		Row titleRow = sh.createRow(0);
		titleRow.createCell(0).setCellValue("部门");
		titleRow.createCell(1).setCellValue("科室");
		titleRow.createCell(2).setCellValue("登陆名");
		titleRow.createCell(3).setCellValue("姓名");
		titleRow.createCell(4).setCellValue("注册时间");
		sh.setDefaultColumnWidth(50);
		sh.setColumnWidth(0, 1500);
		sh.setColumnWidth(1, 5000);
		sh.setColumnWidth(2, 3000);
		sh.setColumnWidth(3, 3000);
		sh.setColumnWidth(4, 3000);

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String subDomains = request.getParameter("subDomain");
		String startTime = request.getParameter("startTime");
		String endTime = request.getParameter("endTime");
		String[] subDomainList = subDomains.split(",");
		SearchCriteriaTenantZhzfUser searchCriteria = new SearchCriteriaTenantZhzfUser();
		if (!"null".equals(startTime)) {
			Date start = sdf.parse(startTime);
			searchCriteria.setStartTime(start);
		}
		if (!"null".equals(endTime)) {
			Date end = sdf.parse(endTime);
			searchCriteria.setEndTime(end);
		}
		List<ObjectId> tenantList = new ArrayList<ObjectId>();
    	for (String subDomain : subDomainList) {
    		SaasTenant saasTent = saasTenantService.findBySubDomain(subDomain);
    		tenantList.add(new ObjectId(saasTent.getId()));
    	}
    	
    	searchCriteria.setTenantId(tenantList);
    	
        Page<ZhzfUserDTO> userList = (Page<ZhzfUserDTO>) zhzfUserServiceOne.getAllTenantUserList(searchCriteria);
        for(int i = 0; i < userList.size(); i++) {
        	String gName = "";
        	if (userList.get(i).getGroup() != null && userList.get(i).getGroup().size() > 0) {
        		for (BasicDBObject g : userList.get(i).getGroup()) {
        			gName = gName + "/" + g.get("name");
        		}
        		gName = gName.substring(1, gName.length());
        	}
        	userList.get(i).setgName(gName);
        }
		

		for (int rownum = 1; rownum <= userList.size(); rownum++) {
			Row contentRow = sh.createRow(rownum);
			ZhzfUserDTO dto = userList.get(rownum - 1);

			contentRow.createCell(0).setCellValue(dto.getGroupName());
			contentRow.createCell(1).setCellValue(dto.getgName());
			contentRow.createCell(2).setCellValue(dto.getLoginName());
			contentRow.createCell(3).setCellValue(dto.getName());
			if (dto.getAddTime() != null) {
				contentRow.createCell(4).setCellValue(sdf.format(dto.getAddTime()));
			} else {
				contentRow.createCell(4).setCellValue("");
			}
		}

		response.setContentType("application/octet-stream; charset=iso-8859-1");
		StringBuffer contentDisposition = new StringBuffer("attachment; filename=\"");
		String fileName = new String("users.xls".getBytes("UTF-8"), "iso-8859-1");
		contentDisposition.append(fileName).append("\"");
		response.setHeader("Content-disposition", contentDisposition.toString());
		ServletOutputStream out = response.getOutputStream();
		try {
			wb.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			out.flush();
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

获取数据方法自己写就行了，前台传回的查询条件可在HttpServletRequest中获得