一个笨方法，有简洁的方法欢迎可以讨论
public void download(){
SXSSFWorkbook swb = new SXSSFWorkbook(10000);// 创建一个Excel文件(海量数据)
HttpServletRequest request = ServletActionContext.getRequest();
//获取数据
Company com = (Company) request.getSession().getAttribute("CompanyInfo"); String starttime = request.getParameter("starttime")+" 00:00:00"; String endtime = request.getParameter("endtime")+" 23:59:59"; String room = request.getParameter("room"); String page = ""; String companyId = com.getCompanyid(); if (room != null) { try { room = java.net.URLDecoder.decode(room, "UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } } Sheet sh = swb.createSheet("会议列表 ");// 创建sheet1-会议管理 Sheet sh1 = swb.createSheet("会议详情");// 创建sheet2-会议详情 // 标题栏样式 CellStyle cellStyle = swb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFillForegroundColor(SKY_BLUE.index); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框 cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框 cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框 cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框 cellStyle.setWrapText(false); // sheet1 ,设置列宽 sh.setColumnWidth(0, 20 * 256); sh.setColumnWidth(1, 20 * 256); sh.setColumnWidth(2, 22 * 256); sh.setColumnWidth(3, 22 * 256); sh.setColumnWidth(4, 20 * 256); sh.setColumnWidth(5, 20 * 256); sh.setColumnWidth(6, 15 * 256); // sheet2 ,设置列宽 sh1.setColumnWidth(0, 20 * 256); sh1.setColumnWidth(1, 20 * 256); sh1.setColumnWidth(2, 22 * 256); sh1.setColumnWidth(3, 22 * 256); sh1.setColumnWidth(4, 20 * 256); sh1.setColumnWidth(5, 20 * 256); sh1.setColumnWidth(6, 15 * 256); sh1.setColumnWidth(7, 15 * 256); Row rowHeader = sh.createRow(0); Cell cellHeader = rowHeader.createCell(0); cellHeader.setCellValue("会议室"); cellHeader.setCellStyle(cellStyle); cellHeader = rowHeader.createCell(1); cellHeader.setCellValue("实际开始时间"); cellHeader.setCellStyle(cellStyle); cellHeader = rowHeader.createCell(2); cellHeader.setCellValue("实际结束时间"); cellHeader.setCellStyle(cellStyle); cellHeader = rowHeader.createCell(3); cellHeader.setCellValue("会议时长"); cellHeader.setCellStyle(cellStyle); cellHeader = rowHeader.createCell(4); cellHeader.setCellValue("当前人数"); cellHeader.setCellStyle(cellStyle); cellHeader = rowHeader.createCell(5); cellHeader.setCellValue("状态"); cellHeader.setCellStyle(cellStyle); Row rowHeader1 = sh1.createRow(0); Cell cellHeader1 = rowHeader1.createCell(0); cellHeader1.setCellValue("会议室"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(1); cellHeader1.setCellValue("用户"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(2); cellHeader1.setCellValue("用户类型"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(3); cellHeader1.setCellValue("加入方式"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(4); cellHeader1.setCellValue("加入时间"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(5); cellHeader1.setCellValue("离开时间"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(6); cellHeader1.setCellValue("操作系统"); cellHeader1.setCellStyle(cellStyle); cellHeader1 = rowHeader1.createCell(7); cellHeader1.setCellValue("会议时长"); cellHeader1.setCellStyle(cellStyle); int i = 1; // 组装会议管理列表查询数据 Map<String, String> map = new HashMap<String, String>(); map.put("tname", com.getInfo2()); map.put("starttime", starttime); map.put("endtime", endtime); map.put("room", room); map.put("page", page); // 会议管理-接收查询得到的结果集 List<Map<String, Object>> meetinglist = new ArrayList<Map<String, Object>>(); // 会议管理-接收处理后的结果集 List<Map<String, Object>> meetlist = new ArrayList<Map<String, Object>>(); if (room.equals("")) { meetinglist = conferencecallService.querymeetinfolist(map); List<Map<String, Object>> emlist = new ArrayList<Map<String, Object>>(); if (companyId != null) { emlist = employeeService.queryByCompanyId(companyId); } meetlist = handleConferenceName(meetinglist, emlist); } else { map.put("companyid", companyId); meetinglist = conferencecallService.meetinfolistwithroom(map); for (Map<String, Object> map2 : meetinglist) { String displayname = map2.get("DisplayName") == null ? map2 .get("RoomOwner").toString() : map2.get( "DisplayName").toString(); map2.put("ConferenceName", displayname); meetlist.add(map2); } } // 会议管理-处理列显示值 List<Map<String, Object>> meetlists = handleColumn(meetlist); //将会议管理列表值写入cell中 for (Map<String, Object> map2 : meetlists) { Row rowHeader2 = sh.createRow(i); Cell cellHeader2 = rowHeader2.createCell(0); cellHeader2.setCellValue(getCellValue(map2.get("ConferenceName"))); cellHeader2 = rowHeader2.createCell(1); cellHeader2.setCellValue(getCellValue(map2.get("starttime"))); cellHeader2 = rowHeader2.createCell(2); cellHeader2.setCellValue(getCellValue(map2.get("endtime"))); cellHeader2 = rowHeader2.createCell(3); cellHeader2.setCellValue(getCellValue(map2.get("bettime"))); cellHeader2 = rowHeader2.createCell(4); cellHeader2.setCellValue(getCellValue(map2.get("online"))); cellHeader2 = rowHeader2.createCell(5); cellHeader2.setCellValue(getCellValue(map2.get("status"))); i++; if (i % 100 == 0) { ((SXSSFSheet) sh1).flushRows(); } } //存放会议id的集合 HashMap<String, Object> map3 = new HashMap<String, Object>(); //存放会议室名称的集合 List<Map<String, Object>> roomnamelist = new ArrayList<Map<String,Object>>(); int j = 1; StringBuffer callids = new StringBuffer(); String meetid = ""; for (Map<String, Object> map2 : meetlists) { //组装会议室名称的集合 String meetid1 = map2.get("UniqueCallID").toString(); String meetname = map2.get("ConferenceName").toString(); Map<String, Object> roommap = new HashMap<String, Object>(); roommap.put("callid", meetid1); roommap.put("roomname", meetname); roomnamelist.add(roommap); //拼接会议id的字符串 callids.append("\""); callids.append(meetid1); callids.append("\""); callids.append(","); } if(callids.lastIndexOf(",") > 0){ meetid = callids.substring(0, callids.lastIndexOf(",")); map3.put("meetid", meetid); } List<Map<String, Object>> detaillist = conferencecallService.querymeetinfo(map3); List<Map<String, Object>> meetinfolist = handlemeetinfo(detaillist,roomnamelist); for (Map<String, Object> map4 : meetinfolist) { Row rowHeader2 = sh1.createRow(j); Cell cellHeader2 = rowHeader2.createCell(0); cellHeader2.setCellValue(getCellValue(map4.get("roomname"))); cellHeader2 = rowHeader2.createCell(1); cellHeader2.setCellValue(getCellValue(map4.get("CallerName"))); cellHeader2 = rowHeader2.createCell(2); cellHeader2.setCellValue(getCellValue(map4.get("CallerID"))); cellHeader2 = rowHeader2.createCell(3); cellHeader2.setCellValue(getCellValue(map4.get("EndpointType"))); cellHeader2 = rowHeader2.createCell(4); cellHeader2.setCellValue(getCellValue(map4.get("JoinTime"))); cellHeader2 = rowHeader2.createCell(5); cellHeader2.setCellValue(getCellValue(map4.get("LeaveTime"))); cellHeader2 = rowHeader2.createCell(6); cellHeader2.setCellValue(getCellValue(map4.get("ApplicationOs"))); cellHeader2 = rowHeader2.createCell(7); cellHeader2.setCellValue(getCellValue(map4.get("btime"))); j++; if (j % 100 == 0) { ((SXSSFSheet) sh1).flushRows(); } } largeCreatExcel(swb); } catch (Exception e) { logger.info(e.getMessage()); }}
// 数据写入excel public void largeCreatExcel(SXSSFWorkbook swb) { HttpServletResponse response = ServletActionContext.getResponse(); String fileName = null; try { fileName = new String("会议召开情况表.xlsx".getBytes("UTF-8"), "iso8859-1"); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } response.reset(); response.setHeader("Content-disposition", "attachment; filename=" + fileName); response.setContentType("application/octet-stream;charset=UTF-8"); OutputStream os; try { os = response.getOutputStream(); swb.write(os); os.flush(); os.close(); swb.dispose(); } catch (IOException e) { e.printStackTrace(); } }
