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;