jxl导出数据到excel文件

需要的jar包

<dependency>
   <groupId>net.sourceforge.jexcelapi</groupId>
   <artifactId>jxl</artifactId>
   <version>2.6.12</version>
</dependency>

前台发送请求的时候不要用ajax发送请求，正常form提交

controller层此时是包含条件查询的参数

/**
	 * 导出数据
	 */
	//导出数据
	@RequestMapping("/exportData")
    public void exportData(String trainTheme,Integer dutyDeptId,String startTime,String deadline,HttpServletRequest request,HttpServletResponse response){
		AqEverydayTrainingService.outPutExcel(trainTheme,dutyDeptId,startTime,deadline,request, response);
    }

service层

/**
	 * 导出数据到xls
	 */
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());
	
	//输出数据并下载
	public void outPutExcel(String trainTheme,Integer dutyDeptId,String startTime,String deadline,HttpServletRequest request,HttpServletResponse response){
		try {
			List<AqEverydayTraining> selectAll1 = null;
			try {
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				Date d1 = null;
				Date d2 = null;
				if(null!=startTime&&null!=deadline&&""!=startTime&&""!=deadline){
					d1 = format.parse(startTime);
					d2 = format.parse(deadline);
				}
				selectAll1 = aqEverydayTrainingMapper.selectAll1(trainTheme,dutyDeptId, d1, d2);
			} catch (ParseException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
			String fileName = "日常培训数据.xls";//要保存的文件名
			String filePath= "G:/数据导出/";//文件存储位置
			WritableWorkbook workbook = Workbook.createWorkbook(new File(filePath+fileName));
			if (workbook != null) {
				WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式
				WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
				WritableCellFormat wcfmt = new WritableCellFormat(); // 单元格定义
				// 设置标题 sheet.addCell(new jxl.write.Label(列(从0开始), 行(从0开始),
				// 内容.));
				try {
					//表头
					String[] baseTitles = new String[]{"培训主题","部门","培训内容","培训时间","组织人","培训人员"};
					//配置单元格属性
					wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
					wcf.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);
					wcfmt.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
					wcfmt.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);
					//========================数据导出start=======================
					WritableSheet sheet = workbook.createSheet("基础信息", 0);
					sheet.addCell(new Label(0, 0, "基础信息", wcf));
					// 合并单元格
					sheet.mergeCells(0, 0, baseTitles.length-1, 0);
					// 设置行高度
					sheet.setRowView(0, 500);
					for(int i=0;i<baseTitles.length;i++){
						// 设置单元格的宽度
						sheet.setColumnView(i, 25);
						sheet.addCell(new Label(i, 1, baseTitles[i], wcf));
					}
					//对象数据输出
					//sheet.setRowView(2, 400);
					for (int i = 0; i < selectAll1.size(); i++) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						StringBuffer sb = new StringBuffer();
						List<QxUser> selectQxUserByAqEverydayTrainingId = aqEverydayTrainingMapper.selectQxUserByAqEverydayTrainingId(selectAll1.get(i).getId());
				    	for (QxUser qxUser : selectQxUserByAqEverydayTrainingId) {
							qxUser.setPassWord(null);
							qxUser.setUserName(null);
							sb.append(qxUser.getTrueName());
							sb.append("、");
						}
				    	String string = sb.toString();
				    	
						sheet.addCell(new Label(0,i+2,selectAll1.get(i).getTrainTheme(), wcfmt));
						sheet.addCell(new Label(1,i+2,selectAll1.get(i).getQxDept().getDeptName(), wcfmt));
						sheet.addCell(new Label(2,i+2,selectAll1.get(i).getTrainContent(), wcfmt));
						sheet.addCell(new Label(3,i+2,sdf.format(selectAll1.get(i).getTrainTime()), wcfmt));
						sheet.addCell(new Label(4,i+2,selectAll1.get(i).getOrganizationPerson(), wcfmt));
						sheet.addCell(new Label(5,i+2,string, wcfmt));
					}
					//========================数据导出end=========================
					// 从内存中写入文件中
					workbook.write();
					// 关闭资源，释放内存
					workbook.close();
					//下载整理好的数据文件
					DownLoadXLS.downLoad(request,response,fileName,filePath);
				} catch (RowsExceededException e) {
					logger.error("sheet不存在", e);
				} catch (WriteException e) {
					logger.error("创建列名出错", e);
				}
			}
		} catch (IOException e) {
			logger.error("文件创建出错", e);
		}
	}

下载的工具类

public class DownLoadXLS {
	public static void downLoad(HttpServletRequest request,HttpServletResponse response,String str,String filePath){
		String path=filePath+str;
		try { 
            File file = new File(path);   
            if(!file.exists()){
            	 file.createNewFile();
            }
            
			 OutputStream os = response.getOutputStream();
			 response.reset();// 清空输出流   
			 response.setContentType(request.getSession().getServletContext().getMimeType(str));
		    // 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
		    response.setHeader("Content-disposition", "attachment;filename=" + new String(str.getBytes("UTF-8"),"ISO8859-1"));
		    
		    InputStream fis = new FileInputStream(path);
		    byte[] buffer = new byte[fis.available()];
		   
	        fis.read(buffer);
		    fis.close();
		    os.write(buffer);// 输出文件
		    os.flush();
		    os.close();
        } catch (IOException e) {    
            e.printStackTrace();
        } finally{
            try {
                File f = new File(path);
                f.delete();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}
}

效果