JXL把数据库数据导出为EXCEL文件

JXL把数据库数据导出为EXCEL文件1.第一步:查处数据库信息2.解析为wookbook对象3.保存到服务器提供下载

public String exportMeetingUser() throws NumberFormatException, ServiceException, IOException, RowsExceededException, WriteException { String forward = SUCCESS; // TODO 进行导出操作 // 获取查询条件 String username = this.getParameter("username"); String mobile = this.getParameter("mobile"); String isAdmin = this.getParameter("isAdmin"); String meetingId = this.getParameter("meetingId"); User user = new User(); user.setName(username); user.setMobile(mobile); // 把PAGE_SIZE设置很大 pageSize = 1000; Pager<User> pager = userService.findMeetingUserPager( Long.valueOf(meetingId), user, currentPage, pageSize, isAdmin); log.debug("meeting id is： {}", meetingId); List<User> userList = pager.getPageRecords(); String[] title = { "姓名", "手机号码", "职位(通讯录)", "单位", "房间号", "显示房间号", "性别", "电子邮箱", "城市", "加入通讯录", "显示电话号码", "排序", "职位简称", "显示职位简称" }; // TODO 进行导出的操作 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); String exportFileName = sdf.format(new Date()); // 创建文件夹 File uploadFoldPath = new File(fileSystemService.getDocumentRoot() + this.USER_EXPORT_FOLDER); if (!uploadFoldPath.exists()) { uploadFoldPath.mkdirs(); } // 判断文件是否存在 String fileSaveName = exportFileName + ".xls"; String dstPath = fileSystemService.getDocumentRoot() + this.USER_EXPORT_FOLDER + File.separator + fileSaveName; String from = "WEB"; String documentRoot = fileSystemService.getDocumentRoot(); // DOCUMENT ROOT getRequest().setAttribute("documentRoot", documentRoot); getRequest().setAttribute("from", from); OutputStream os = new FileOutputStream(dstPath); // 再建完这个文件的时候再建立工作文件 jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); int rowIndex = 0; Label label; WritableCellFormat wc = new WritableCellFormat(); wc.setAlignment(Alignment.CENTRE); //wc.set WritableSheet sheet = wwb.createSheet("会议用户导出", 0); label = new Label(2, rowIndex, "此文件为会议云用户导出文件"); sheet.addCell(label); rowIndex++; // 将列标题循环添加到Label中 for (int i = 0; i < title.length; i++) { label = new Label(i, rowIndex, title[i]); sheet.addCell(label); } rowIndex++; // TODO下面是添加数据 if (userList != null && userList.size() > 0) { for (User exportUser : userList) { MeetingMember member = meetingMemberService.selectById( exportUser.getId(), Long.valueOf(meetingId)); exportUser.setMeetingMember(member); // "姓名", "手机号码", "职位(通讯录)", "单位", "房间号", "显示房间号", "性别", // "电子邮箱", "城市", "加入通讯录", "显示电话号码", "排序", "职位简称", "显示职位简称" // 用户名 label = new Label(0, rowIndex, exportUser.getName()); sheet.addCell(label); label = new Label(1, rowIndex, exportUser.getMobile()); sheet.addCell(label); label = new Label(2, rowIndex, exportUser.getMeetingMember() .getJob()); sheet.addCell(label); // 单位 label = new Label(3, rowIndex, exportUser.getMeetingMember() .getDepartment()); sheet.addCell(label); // 房间号 label = new Label(4, rowIndex, exportUser.getMeetingMember() .getRoomNumber()); rowIndex++; } } wwb.write(); wwb.close(); // 现在开始进行下载操作 MeetingFiles meetingFiles = new MeetingFiles(); meetingFiles.setFilePath(this.USER_EXPORT_FOLDER); meetingFiles.setFileName(fileSaveName); meetingFiles.setFileSaveName(fileSaveName); this.getRequest().setAttribute("meetingFiles", meetingFiles); return forward; }