将查询的集合导出为Excel文件

╰半橙微兮° 2024-04-17 21:29 100阅读 0赞

**第一个是前台的script的代码，第二个是servlet中控制xml下载的代码**

**这里直接复制黏贴拿来修改一下就好了，可以设置一个按钮访问这个servlet，method=exportXls就可以啦**

function exportXls(){
                var empId=$("#empId").val();
                var deptNo=$("#deptNo").val();
                var dtdate=$("#dtdate").val();
                location.href="servlet/DutyServlet?method=exportXls&empId="+empId+"&deptNo="+deptNo+"&dtdate="+dtdate;
            }

public void exportXls(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            //接收网页中参数
            String empId=request.getParameter("empId");
            if(empId==null){
                empId="";
            }
    
            String cdeptNo=request.getParameter("deptNo");
            int deptNo=0;
            try{
                deptNo=Integer.parseInt(cdeptNo);
            }catch (Exception e){
    
            }
    
            String dtdate=request.getParameter("dtdate");
            if(dtdate==null){
                dtdate="";
            }
    
            DutyService dutyService=new DutyServiceImple();
            //查询数据库，将所有符合的信息放到一个集合中
            List<Duty>dutyList=dutyService.find(empId,deptNo,dtdate);
            //调用该方法，传一个集合和response响应
            createExcel(dutyList,response);
        }
    
        private void createExcel(List<Duty> 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;filename=duty.xls");
    
                OutputStream outputStream = response.getOutputStream();
                workbook.write(outputStream);
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }