工作时遇到了需要在页面显示已经分页数据,提供下载按钮下载表格全部信息生成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中获得