jxl实现excel 导出

使用jxl导出Excel数据

最新推荐文章于 2022-02-16 11:11:38 发布

原创最新推荐文章于 2022-02-16 11:11:38 发布 · 504 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一个基于jxl库实现的Excel导出功能，通过Maven引入jxl依赖，并利用Spring MVC框架中的@RequestMapping注解来处理导出请求。文章详细展示了如何将数据库中的学生信息写入Excel文件并通过HTTP响应提供给用户。

1.maven下载jxl包

2.excel导出

/**
     * 对象数据写入到Excel
	 * @throws UnsupportedEncodingException 
     */   
	@RequestMapping(value = "/downloadSchoolStudent/{id}")
	public String downloadSchoolStudent(Model model, @PathVariable("id") Long schoolId,HttpServletResponse response) throws UnsupportedEncodingException{	
		
		PartySchool partySchool  =partySchoolService.getById(schoolId);
		String fileName=partySchool.getName()+"第"+partySchool.getCount()+"期"+"学生名单";
		fileName = new String(fileName.getBytes("utf-8"),"iso-8859-1");
		response.setCharacterEncoding("utf-8");
		response.reset();
		response.setContentType("application/OCTET-STREAM;charset=utf-8");
		 
		response.setHeader("pragma", "no-cache");
		response.addHeader("Content-Disposition", "attachment;filename=\""
		+ fileName + ".xls\"");// 点击导出excle按钮时候页面显示的默认名称
		
		OutputStream os=null;
		//创建表格
			WritableWorkbook book = null;
		try {
			 // 打开文件
//         book = Workbook.createWorkbook(new File("D:/test/student/stuList.xls"));
			os=response.getOutputStream();
			book = Workbook.createWorkbook(os);

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("学生", 0); 
            WritableFont   wf   =   new   WritableFont(WritableFont.TIMES,12,WritableFont.BOLD,true); 
            WritableCellFormat   whf   =   new   WritableCellFormat(wf); 
            whf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); 
            whf.setAlignment(Alignment.CENTRE);  //水平居中
            whf.setVerticalAlignment(VerticalAlignment.CENTRE);  //垂直居中
            WritableFont   wf2   =   new   WritableFont(WritableFont.ARIAL,11,WritableFont.NO_BOLD,false); 
            WritableCellFormat   wcf   =   new   WritableCellFormat(wf2); 
            wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); 
	        // excel要导出的数据   
	        List<Map<String, Object>> list = partySchoolService.getSchoolStudentList(schoolId,null); 
	        // 导出  
	        if (list == null || list.size() == 0) {  
	            System.out.println("学生为空");  
	        }else {  
	        	
	        	sheet.setRowView(0,400);   //设置第一行的高度为400
	        	sheet.setColumnView(0,20);   //设置第一行的宽度为20
	        	sheet.setColumnView(4,20);   //设置第4行的宽度为20
	        	sheet.setColumnView(5,20);   //设置第5行的宽度为20
	        	sheet.setColumnView(6,20);   //设置第6行的宽度为20
	        	//添加表头	        	
	        	sheet.addCell(new Label(0, 0, "学号",whf));    
                sheet.addCell(new Label(1, 0, "姓名",whf));   
                sheet.addCell(new Label(2, 0, "性别",whf));  
                sheet.addCell(new Label(3, 0, "年级",whf));  
                sheet.addCell(new Label(4, 0, "系院",whf));  
                sheet.addCell(new Label(5, 0, "专业",whf));  
                sheet.addCell(new Label(6, 0, "班级",whf));  
                sheet.addCell(new Label(7, 0, "班主任",whf));  
                
	        	//写入文件
	        	  for(int i=0; i<list.size(); i++){
	                    sheet.addCell(new Label(0, (i+1), (String) list.get(i).get("studentNumber"),wcf));  
	                    sheet.addCell(new Label(1,(i+1),  (String)list.get(i).get("studentName"),wcf));   
	                    sheet.addCell(new Number(2,(i+1),  Integer.valueOf((String)list.get(i).get("gender")),wcf));  
	                    sheet.addCell(new Label(3,(i+1),  (String)list.get(i).get("studentGrade"),wcf));  
	                    sheet.addCell(new Label(4,(i+1),  (String)list.get(i).get("facultyName"),wcf));  
	                    sheet.addCell(new Label(5,(i+1),  (String)list.get(i).get("majorName"),wcf));  
	                    sheet.addCell(new Label(6,(i+1),  (String)list.get(i).get("classInfoName"),wcf));  
	                    sheet.addCell(new Label(7,(i+1),  (String)list.get(i).get("classTeacherName"),wcf));                      
	                   
	                }
	        	// 写入数据并关闭文件
	            book.write();	     
	            os.flush();
	        }         

		} catch(Exception e) {			
			model.addAttribute("error", "下载失败,"+e.getMessage());
			e.printStackTrace();
		}finally{  
		
                try {
                	if(book!=null){
                		book.close();
                    }
                	if(os!=null){
                		os.close();
                	}
                	 
					
                } catch (Exception e) {
                    e.printStackTrace();
                } 
            }        
		return null;     
    
    }

3.html页面

导出的方法，注意用的是location.href="";的方式，以前想要post请求，返回的流接收不到。

function exportStudent(){   
	      
        var row = $('#dg').datagrid('getSelected');
 		if(row){ 				
			location.href="<%= request.getContextPath() %>/manage/partySchool/downloadSchoolStudent22/"+row.id;
 		}
	      
	}