将查询的集合导出为Excel文件
第一个是前台的script的代码第二个是servlet中控制xml下载的代码这里直接复制黏贴拿来修改一下就好了可以设置一个按钮访问这个servletmethodexportXls就可以啦function exportXls(){ var empId$(#empId).val(); var deptNo$(#deptNo).val(); var dtdate$(#dtdate).val(); location.hrefservlet/DutyServlet?methodexportXlsempIdempIddeptNodeptNodtdatedtdate; }public void exportXls(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //接收网页中参数 String empIdrequest.getParameter(empId); if(empIdnull){ empId; } String cdeptNorequest.getParameter(deptNo); int deptNo0; try{ deptNoInteger.parseInt(cdeptNo); }catch (Exception e){ } String dtdaterequest.getParameter(dtdate); if(dtdatenull){ dtdate; } DutyService dutyServicenew DutyServiceImple(); //查询数据库将所有符合的信息放到一个集合中 ListDutydutyListdutyService.find(empId,deptNo,dtdate); //调用该方法传一个集合和response响应 createExcel(dutyList,response); } private void createExcel(ListDuty dutyList, HttpServletResponse response) { // 创建一个Excel文件 HSSFWorkbook workbook new HSSFWorkbook(); // 创建一个工作表 HSSFSheet sheet workbook.createSheet(签到表一); CellRangeAddress region new CellRangeAddress(0, // first row 0, // last row 0, // first column 2 // last column ); sheet.addMergedRegion(region); HSSFRow hssfRow sheet.createRow(0); HSSFCell headCell hssfRow.createCell(0); headCell.setCellValue(员工签到表); // 设置单元格格式居中 HSSFCellStyle cellStyle workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); /* cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // cellStyle.setFillBackgroundColor(HSSFColor.GREEN.index); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); HSSFFont font workbook.createFont(); font.setFontName(楷体); //字体 font.setFontHeightInPoints((short)30); //字体大小 font.setColor(HSSFColor.RED.index);//颜色 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗 font.setItalic(true); //倾斜 cellStyle.setFont(font); */ headCell.setCellStyle(cellStyle); // 添加表头行 hssfRow sheet.createRow(1); // 添加表头内容 headCell hssfRow.createCell(0); headCell.setCellValue(用户名); headCell.setCellStyle(cellStyle); headCell hssfRow.createCell(1); headCell.setCellValue(真实姓名); headCell.setCellStyle(cellStyle); headCell hssfRow.createCell(2); headCell.setCellValue(所属部门); headCell.setCellStyle(cellStyle); headCell hssfRow.createCell(3); headCell.setCellValue(出勤日期); headCell.setCellStyle(cellStyle); headCell hssfRow.createCell(4); headCell.setCellValue(签到时间); headCell.setCellStyle(cellStyle); headCell hssfRow.createCell(5); headCell.setCellValue(签退时间); headCell.setCellStyle(cellStyle); // 添加数据内容 for (int i 0; i dutyList.size(); i) { hssfRow sheet.createRow((int) i 2); Duty duty dutyList.get(i); // 创建单元格并设置值 HSSFCell cell hssfRow.createCell(0); cell.setCellValue(duty.getEmpId()); cell.setCellStyle(cellStyle); cell hssfRow.createCell(1); cell.setCellValue(duty.getEmployee().getRealName()); cell.setCellStyle(cellStyle); cell hssfRow.createCell(2); cell.setCellValue(duty.getDept().getDeptName()); cell.setCellStyle(cellStyle); cell hssfRow.createCell(3); cell.setCellValue(duty.getDtDate()); cell.setCellStyle(cellStyle); cell hssfRow.createCell(4); cell.setCellValue(duty.getSignInTime()); cell.setCellStyle(cellStyle); cell hssfRow.createCell(5); cell.setCellValue(duty.getSignOutTime()); cell.setCellStyle(cellStyle); } // 保存Excel文件 try { response.setContentType(application/vnd.ms-excel); response.setHeader(Content-disposition,attachment;filenameduty.xls); OutputStream outputStream response.getOutputStream(); workbook.write(outputStream); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } }
