数据库导出到Excel前后端代码

1、前端页面

省略

2、js

//导出excel
function nexport() {
var formObj = $("#spbfm");
var data = formObj.serializeJSON(); // $('form').serializeJSON(); 
var strdwmc = $('#njgtree').combotree('getValue');//$("#txtseajg").val();
var strzjlx = $("#zjlx").combobox("getValue");
var strxm = $("#xm").val();//！现在虽然取了传到后台，其实后台是设为空的！
var hasChk = $('#check_xss').is(':checked');//包含下属
var bhxs='0';//不包含下属
if (hasChk) {//包含下属
bhxs ='1';
}
var strurl = getContextPathInfo() + "/lowzxspb/export?dwmc=" + strdwmc
+ "&zjlx=" + strzjlx+"&xm="+strxm+"&bhxs="+bhxs;

var reobj = {"url": strurl,"data":data };
aSerge(strurl, data, OnSuccess_NonNeedOped, OnError);
return reobj;
}
//excel附件返回成功或者失败
function OnSuccess_NonNeedOped(json){
if (typeof (json) == "object") {
if (json.resulttype == "SUCCESS") {
var xsrc=json.appenddata;
var srcPath = window.document.location.origin;
var srcname=window.document.location.pathname;
var arrstr=srcname.split('/');
download2(srcPath+'/'+arrstr[1]+xsrc);
alert(json.msg);

}else{
alert(json.msg);
}
}
}

3、java后台

①控制层方法

// 导出
@RequestMapping("/export")
@ResponseBody
public OperationResult export(HttpServletResponse response, HttpServletRequest request, String dwmc, Integer zjlx, String xm, String bhxs) {
OperationResult result = new OperationResult(OperationResultTypeEnum.ERROR, "导出失败");
response.setContentType("application/binary;charset=UTF-8");
try {
ServletOutputStream out = response.getOutputStream();

// String fileName = new String(
// ("UserInfo " + new SimpleDateFormat("yyyy-MM-dd").format(new Date())).getBytes(), "UTF-8");
//
// response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");

String fileName = "Zxspb.xls";
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");

String pathval = request.getSession().getServletContext().getRealPath("/");
String xmname = request.getServletContext().getContextPath();
String srcname = "/UpFile/" + fileName;
pathval += srcname;
String[] titles = { "执法人员姓名 ", "证件类型", "证件号 ", "工作单位 ", "注销时间 " };
lowzxspbservice.export(titles, out, dwmc, zjlx, xm, pathval, bhxs);
result.setResulttype(OperationResultTypeEnum.SUCCESS);
result.setAppenddata("/cats-yunan-view" + srcname);
result.setMsg("注销数据导出成功！");
return result;
} catch (Exception e) {
e.printStackTrace();
return result;
}
}

②、业务逻辑层

// 导出
@Override------->因为这是serviceImpl层，它是实现了service层，故有此注解
public void export(String[] titles, ServletOutputStream out, String dwmc, Integer zjlx, String xm, String pathval,
String bhxs) {
try {
// 第一步，创建一个workbook，对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet hssfSheet = workbook.createSheet("sheet1");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow hssfRow = hssfSheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
// 居中样式
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFCell hssfCell = null;
for (int i = 0; i < titles.length; i++) {
hssfCell = hssfRow.createCell(i);// 列索引从0开始
hssfCell.setCellValue(titles[i]);// 列名1
hssfCell.setCellStyle(hssfCellStyle);// 列居中显示
}

// 第五步，写入实体数据
List<LowzxspbDto> lowzxspbDto = null;
String s_xm = null;// 模糊查询
Integer s_zjlx = (zjlx == null || "".equals(zjlx)) ? null : zjlx;// 因为前台传来的都为字符(可以用integer接，但不可以用int接)//因为前端是下拉框，这个条件可以不用（建议不用，因为数据库里是数字）
String s_dwmc = (dwmc == null || "".equals(dwmc)) ? null : dwmc;// (==)条件查询
if (("0".equals(bhxs))) {// 不包含下属excel
lowzxspbDto = lowzxspbdao.findLowzxspbExcel(s_dwmc, s_zjlx, s_xm);
} else {// 1 包含下属excel
lowzxspbDto = lowzxspbdao.findLowzxspbbhxsExcel(s_dwmc, s_zjlx, s_xm);
}

// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if (lowzxspbDto != null && !lowzxspbDto.isEmpty()) {
for (int i = 0; i < lowzxspbDto.size(); i++) {
hssfRow = hssfSheet.createRow(i + 1);
LowzxspbDto spbDto = lowzxspbDto.get(i);

// 第六步，创建单元格，并设置值
String xm1 = "";// 执法人姓名
if (spbDto.getXm() != "") {
xm1 = spbDto.getXm();
}
hssfRow.createCell(0).setCellValue(xm1);
String zjlxString = "";
// Integer zjlx1 = null;//证件类型
if (spbDto.getZjlx() != null) {
if (spbDto.getZjlx().equals(0)) {
zjlxString = "交通行政执法证";
} else if (spbDto.getZjlx().equals(1)) {
zjlxString = "云南省行政执法证";
} else if (spbDto.getZjlx().equals(2)) {
zjlxString = "云南省法制督察证";
} else {
zjlxString = "海事执法证";
}
}
hssfRow.createCell(1).setCellValue(zjlxString);
String zfzh = "";// 证件号
if (spbDto.getZfzh() != "") {
zfzh = spbDto.getZfzh();
}
hssfRow.createCell(2).setCellValue(zfzh);
String gzdw = "";// 工作单位
if (spbDto.getGzdw() != "") {
gzdw = spbDto.getGzdw();
}
hssfRow.createCell(3).setCellValue(gzdw);
Timestamp zxsj = null;// 注销时间
String zxsjString = "";
if (spbDto.getOpedate() != null) {
zxsj = spbDto.getOpedate();
zxsjString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(zxsj);// Timestamp-->String(方法二
// tostring)
}
hssfRow.createCell(4).setCellValue(zxsjString);
}

}
try {

FileOutputStream fileOutputStream = new FileOutputStream(pathval);// 指定路径与名字和格式
workbook.write(fileOutputStream);// 将数据写出去
fileOutputStream.close();// 关闭输出流
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
try {
throw new Exception("导出信息失败！");
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}

}

③、dao持久化层

/**
* 
* @Title: excel不包含下属查询
* @Description: TODO(这里用一句话描述这个方法的作用) 
* @param s_dwmc
* @param s_zjlx
* @param s_xm
* @return 
* @throws
*/

@Query("select new com.catsic.casee.yn.law.entity.LowzxspbDto(spb.dm,spb.yjdm,spb.opeodm,spb.zfrdm,spb.zjdm,spb.xm,spb.zfzh,spb.opedate,lct.zjlx,xxb.MC as mc,xxb.IDCARD as idcard,xxb.GZDW as gzdw,zdb.uname as uname) from Lowzxspb spb left join Zfryxx xxb on spb.zfrdm=xxb.DM left join Lowcredentials lct on spb.zjdm=lct.dm left join Zidianb zdb on lct.zjlx=zdb.ucode where zdb.pcode='0299' and spb.dwdm=?1 and lct.zjlx=?2 and (?3 is null or spb.xm like ?3) order by spb.opedate desc ")
public List<LowzxspbDto> findLowzxspbExcel(String s_dwmc, Integer s_zjlx, String s_xm);

/**
* 包含下属查询
* @Title:包含下属excel
* @Description: TODO(这里用一句话描述这个方法的作用) 
* @param dwdm
* @param zjlx
* @param xm
* @return 
* @throws
*/
@Query("select new com.catsic.casee.yn.law.entity.LowzxspbDto(spb.dm,spb.yjdm,spb.opeodm,"
+ "spb.zfrdm,spb.zjdm,spb.xm,spb.zfzh,spb.opedate,lct.zjlx,xxb.MC as mc,"
+ "xxb.IDCARD as idcard,xxb.GZDW as gzdw,zdb.uname as uname) "
+ "from Lowzxspb spb left join Zfryxx xxb on spb.zfrdm=xxb.DM "
+ "left join Lowcredentials lct on spb.zjdm=lct.dm "
+ "left join Zidianb zdb on lct.zjlx=zdb.ucode "
+ "left join Qxgsbmb qsb on qsb.dm=spb.dwdm "
+ "where zdb.pcode='0299' and (?1 is null or qsb.dmlist like ?1 and (?2 is null or lct.zjlx=?2) and (?3 is null or spb.xm like ?3)) order by spb.opedate desc ")
public List<LowzxspbDto> findLowzxspbbhxsExcel(String s_dwmc, Integer s_zjlx, String s_xm);