java代码使用poi导出查询列表为Excel

1.使用java中的poi导出Excel文件，首先引入poi的jar包    poi-3.8.jar

2.在jsp的列表页面，需要将页面上面的查询条件取出来，传到后台java代码中

//jsp页面上面写一个函数，是列表的模糊查询的条件(注意只是查询条件，和列表展示的内容区分开)
function outExcel(){
	//使用jquery封装好的转码格式，将页面的字符串转码两次
	var bxdh = encodeURI(encodeURI(document.getElementById("sear_file_bxdh").value)); //模糊查询的单号
	var bxsj = encodeURI(encodeURI(document.getElementById("sear_file_bxsj").value)); //模糊查询的开始时间
	var jbxsj = encodeURI(encodeURI(document.getElementById("e_file_bxsj").value)); //模糊查询的结束时间
	var dqhj = encodeURI(encodeURI(document.getElementById("sear_file_dqhj").value)); //模糊查询的环节
	var url = "${ctx}/sbbxyhpj.action?method=outExcelLieBiao&bxdh="+bxdh+"&bxsj="+bxsj+"&jbxsj="+jbxsj+"&dqhj="+dqhj;   //该路径就是控制层的方法加上搜索的参数
	window.open(url);
} 

3.接下来就是java后台代码，只需要写一个方法，将列表查询出来，然后写入excel中，最后导出excel

/**
	 * 导出列表的EXCEL表
	 * @param req
	 * @param res
	 * @return
	 * @throws Exception
	 */
	public ModelAndView outExcelLieBiao(HttpServletRequest req, HttpServletResponse res) throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdfsfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String bxdh = URLDecoder.decode(RequestUtil.getParameterStr(req, "bxdh", ""), "UTF-8");//标题
		String bxsj = URLDecoder.decode(RequestUtil.getParameterStr(req, "bxsj", ""), "UTF-8");//当前环节
		String jbxsj = URLDecoder.decode(RequestUtil.getParameterStr(req, "jbxsj", ""), "UTF-8");//当前环节
		String ywlx2 = "wsbxlc";
		String wjlx = "ZSWJ";
		
		//业务表 unid = srcunid
		String dqhj = URLDecoder.decode(RequestUtil.getParameterStr(req, "dqhj", ""), "UTF-8");//环节名称
		
		DetachedCriteria dcperionLsksqsqry = DetachedCriteria.forClass(SbbxWsbx.class);
		//判断单号不为空尽心模糊查询
		if(StringUtils.isNotBlank(bxdh)){
			dcperionLsksqsqry.add(Restrictions.like("bxdh", "%"+bxdh+"%"));
		}
		//判断搜索的时间范围不为空
		if(StringUtils.isNotBlank(bxsj) && StringUtils.isNotBlank(jbxsj)){
			java.util.Date ksrqDate =sdf.parse(bxsj);
			java.util.Date jsrqDate =sdfsfm.parse(jbxsj+" 23:59:59");
			dcperionLsksqsqry.add(Restrictions.ge("gwcreatetime", new Timestamp(ksrqDate.getTime()-1)));
			dcperionLsksqsqry.add(Restrictions.le("gwcreatetime", new Timestamp(jsrqDate.getTime()+1)));
		}
		//查询另外一张表，判断不为空
		DetachedCriteria dcperionWdbj = DetachedCriteria.forClass(TydbWdbj.class);
		if(StringUtils.isNotBlank(dqhj)){
			dcperionWdbj.add(Restrictions.like("dqhj", "%"+dqhj+"%"));
		}
		
		dcperionWdbj.add(Restrictions.eq("ywlx2", ywlx2));
		dcperionWdbj.add(Restrictions.eq("wjlx", wjlx));
		dcperionWdbj.setProjection(Property.forName("srcunid"));
		dcperionLsksqsqry.add(Property.forName("unid").in(dcperionWdbj));
		dcperionLsksqsqry.addOrder(Order.desc("createtime"));
		//查询业务表中的所有数据
		List<SbbxWsbx> sbbxwsbxList = commonService.findListByCriteria(dcperionLsksqsqry, -1, -1);
		// 创建HSSFWorkbook对象(excel的文档对象)
		HSSFWorkbook wb = new HSSFWorkbook();
		// 建立新的sheet对象（excel的表单），excel表中底部的菜单名称
		HSSFSheet sheet = wb.createSheet("报修列表");
		
		HSSFRow row1 = sheet.createRow(0);
		//HSSFCell cell = row1.createCell(0);
		// 设置单元格内容
		
		//sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
		// 在sheet里创建第二行
		sheet.setColumnWidth(0, 20*200);
		sheet.setColumnWidth(1, 40*200);
		sheet.setColumnWidth(2, 30*200);
		sheet.setColumnWidth(3, 30*200);
		//HSSFRow row2 = sheet.createRow(0);
		// row2.setHeightInPoints(15);
		// 创建单元格并设置单元格内容
		row1.createCell(0).setCellValue("报修单号");
		row1.createCell(1).setCellValue("报修时间");
		row1.createCell(2).setCellValue("环节处理人");
		row1.createCell(3).setCellValue("环节名称");
		//将业务表中查询的列表的数据写进Excel中
		if(sbbxwsbxList!=null && !sbbxwsbxList.isEmpty()){
		for (int i = 0; i < sbbxwsbxList.size(); i++) {
			SbbxWsbx wsbxsqry = sbbxwsbxList.get(i);
			String unid = wsbxsqry.getUnid();
			String srcunid = unid;
			//查询业务表
			DetachedCriteria dcperionTydbWdbj = DetachedCriteria.forClass(TydbWdbj.class);
			dcperionTydbWdbj.add(Restrictions.eq("srcunid", srcunid));
			//查询业务表辅助表
			List<TydbWdbj> xxoaTydbWdbjList = commonService.findListByCriteria(dcperionTydbWdbj, -1, -1);
			TydbWdbj tydbWdbj = xxoaTydbWdbjList.get(0);
			HSSFRow row3 = sheet.createRow(1 + i);
			row3.createCell(0).setCellValue(wsbxsqry.getBxdh());
			String bxsj1 = "";
			try {
				bxsj1 = sdf.format(wsbxsqry.getBxsj());
			} catch (Exception e) {
			}
			row3.createCell(1).setCellValue(bxsj1);
			//查询业务表关联表
			DetachedCriteria opinionInfoWdbj = DetachedCriteria.forClass(OpinionInfo.class);
			opinionInfoWdbj.add(Restrictions.eq("dataunid", srcunid));
			opinionInfoWdbj.add(Restrictions.eq("fieldname", "SBBX_WSBX.wxkys"));
			List<OpinionInfo> opinionInfoList = commonService.findListByCriteria(opinionInfoWdbj, -1, -1);
			String creatorname = "";
			if(opinionInfoList.size()>0){
				OpinionInfo opinionInfo = opinionInfoList.get(0);
				creatorname = opinionInfo.getCreatorname();
			}
			row3.createCell(2).setCellValue(creatorname);
			row3.createCell(3).setCellValue(tydbWdbj.getDqhj());
		}
		}
		
		// 输出Excel文件
		OutputStream output = res.getOutputStream();
		try {
			String dqsj = sdfsfm.format(new Date());
			res.reset();
			String filename = URLEncoder.encode("网上报修","UTF-8");
			res.setHeader("Content-disposition", "attachment; filename="+filename+dqsj + ".xlsx");
			res.setContentType("application/msexcel");
			wb.write(output);
		} catch (Exception e) {
			System.out.println("输出异常");
		}finally{
			output.flush();
			output.close();
		}
		
		return null;
	}

以上就是将查询列表导出excel的代码，需要注意1.查询条件传到后台的是文字字符串，需要转码两次2.在引入jar包的时候不要出差，时间包要引入import java.util.Date;