java导出Excel前台代码_数据库导出到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 = 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 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 findLowzxspbbhxsExcel(String s_dwmc, Integer s_zjlx, String s_xm);