批量或者选择导出datagrid列表数据到表格
//导出项目信息 function exportXmxx(){ //判断是否选择站址 var index = $("#dgObj").datagrid('getChecked'); if(index.length == 0){ alert('请选择您要导出的信息！'); return; } var sheetId = ""; if(!selectAllPageFlag){//是否全选 for (var i = 0; i < index.length; i++) { var row = index[i]; if(sheetId == ""){ sheetId = row.SHEETID; }else{ sheetId = sheetId+","+row.SHEETID; } } } var data = { xmmc:$("#ssxm").combobox("getText"),//所属项目(模糊) xmlx:$("#xmlx").combobox("getText"),//项目类型(模糊) xmzt:$("#xmzt").combobox("getValue"),//项目状态 sheetId:sheetId//选中记录的IDS }; var url = window.location.protocol + "//" + window.location.host + resources.webappName + "/servlet/exportXmxx"; name = "导出项目信息表"; openPostWindow(url, JSON.stringify(data), name); //调用openPostWindow方法，请求JSP url post 传递参数 } /*********请求JSP url post 传递参数**********************/ function openPostWindow(url, data, name) { var tempForm = document.createElement("form"); data = encodeURI(encodeURI(data));//两次加密避免中文乱码 tempForm.id="tempForm1"; tempForm.method="post"; tempForm.action=url; if(name != null && name!=undefined && name != ''){//当需要打开一个新窗口时必须要传递一个name tempForm.target=name; }else{ tempForm.target="_self"; } var hideInput = document.createElement("input"); hideInput.type="hidden"; hideInput.name= "params";//接收的名字 hideInput.value= data; tempForm.appendChild(hideInput); if(name != null && name!=undefined && name != ''){//当需要打开一个新窗口时必须要传递一个name tempForm.attachEvent("onsubmit",function(){ _openWindow(name); }); }else{ tempForm.attachEvent("onsubmit",function(){ return false; }); } document.body.appendChild(tempForm); tempForm.fireEvent("onsubmit"); tempForm.submit(); document.body.removeChild(tempForm); } function _openWindow(name){ window.open('about:blank',name,'channelmode=1,fullscreen=1'); }
1.以上为前台部分
2.做好对应的配置
3.以下为后台代码参考示例：
package com.sdjxd.rsgxm.xmzfzr.dao; import com.google.gson.Gson; import com.google.gson.reflect.TypeToken; import com.sdjxd.pms.platform.data.DbOper; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.net.URLDecoder; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import sadmpweb.zzgl.Util; public class exportXmxxDao extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); Date dt = new Date(); String time = sdf.format(dt); String path1 = System.getProperty("user.dir"); path1 = path1.substring(0, path1.indexOf("bin")) + "webapps\\sadmpserver\\"; String sourcePath = path1 + "resource\\数据导出公用模板.xlsx"; String descPath = path1 + "resource"; File fileDir = new File(descPath); if (!fileDir.exists()) { fileDir.mkdir(); } String targetFileName = "弱栅格—项目信息表导出—" + time + ".xlsx"; File targetFile = new File(fileDir, targetFileName); String targetFilePath = targetFile.getPath(); String data = (request.getParameter("params") == null) ? "" : request.getParameter("params"); data = URLDecoder.decode(URLDecoder.decode(data, "utf-8"), "utf-8"); Gson gson = new Gson(); Map jsonObj = (Map)gson.fromJson(data, new TypeToken() { } .getType()); String filter = getFilter(jsonObj); try { targetFile = new Util().copyFile(sourcePath, targetFilePath); FileInputStream fis = new FileInputStream(targetFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet hs1 = wb.getSheetAt(0); XSSFRow row1 = hs1.createRow(0); XSSFCell cell00 = row1.createCell(0); XSSFCell cell01 = row1.createCell(1); XSSFCell cell02 = row1.createCell(2); XSSFCell cell03 = row1.createCell(3); XSSFCell cell04 = row1.createCell(4); XSSFCell cell05 = row1.createCell(5); XSSFCell cell06 = row1.createCell(6); XSSFCell cell07 = row1.createCell(7); XSSFCell cell08 = row1.createCell(8); XSSFCell cell09 = row1.createCell(9); XSSFCell cell10 = row1.createCell(10); XSSFCell cell11 = row1.createCell(11); XSSFCell cell12 = row1.createCell(12); XSSFCell cell13 = row1.createCell(13); XSSFCell cell14 = row1.createCell(14); XSSFCell cell15 = row1.createCell(15); XSSFCell cell16 = row1.createCell(16); XSSFCell cell17 = row1.createCell(17); cell00.setCellValue("数据列名1"); cell01.setCellValue("数据列名2"); cell02.setCellValue("数据列名2"); cell03.setCellValue("数据列名4"); cell04.setCellValue("数据列名5"); cell05.setCellValue("数据列名6"); cell06.setCellValue("数据列名7"); cell07.setCellValue("数据列名8"); cell08.setCellValue("数据列名9"); cell09.setCellValue("数据列名10"); cell10.setCellValue("数据列名11"); cell11.setCellValue("数据列名12"); cell12.setCellValue("数据列名13"); cell13.setCellValue("数据列名14"); cell14.setCellValue("数据列名15"); cell15.setCellValue("数据列名16"); cell16.setCellValue("数据列名17"); cell17.setCellValue("数据列名18"); dcxx(fis, wb, filter); FileOutputStream out = new FileOutputStream(targetFile); wb.write(out); downloadFile(request, response, targetFileName, descPath + "\\" + targetFileName); fis.close(); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } public void downloadFile(HttpServletRequest request, HttpServletResponse response, String targetFileName, String path_filename) throws Exception { response.setContentType("text/html;charset=UTF-8"); BufferedInputStream in = null; BufferedOutputStream out = null; request.setCharacterEncoding("UTF-8"); try { File f = new File(path_filename); response.setContentType("application/x-excel"); response.setCharacterEncoding("UTF-8"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(targetFileName.getBytes("gbk"), "iso-8859-1")); response.setHeader("Content-Length", String.valueOf(f.length())); in = new BufferedInputStream(new FileInputStream(f)); out = new BufferedOutputStream(response.getOutputStream()); byte[] data = new byte[1024]; int len = 0; while (-1 != (len = in.read(data, 0, data.length))) out.write(data, 0, len); } catch (Exception e) { e.printStackTrace(); } finally { if (in != null) { in.close(); } if (out != null) out.close(); } } private String getFilter(Map<String, String> jsonObj) { String res = ""; String xmmc = (String)jsonObj.get("xmmc"); String xmzt = (String)jsonObj.get("xmzt"); String xmlx = (String)jsonObj.get("xmlx"); String sheetId = (String)jsonObj.get("sheetId"); StringBuffer filter = new StringBuffer(); if ((xmmc != null) && (!"".equals(xmmc)) && (!xmmc.equals("--不限--"))) { filter.append(" AND WYXMJC LIKE '%").append(xmmc).append("%' "); } if ((xmlx != null) && (!"".equals(xmlx)) && (!xmlx.equals("--不限--"))) { filter.append(" AND XMSX LIKE '%").append(xmlx).append("%' "); } if ((xmzt != null) && (!"".equals(xmzt)) && (!xmzt.equals("--不限--"))) { filter.append(" AND XMZT = '").append(xmzt).append("' "); } if ((sheetId != null) && (!"".equals(sheetId))) { filter.append(" AND SHEETID IN ('").append(sheetId.replaceAll(",", "','")).append("') "); } res = filter.toString(); return res; } private void dcxx(FileInputStream fis, XSSFWorkbook wb, String filter) throws SQLException { StringBuffer sb = new StringBuffer(); sb.append(" SELECT *FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY X.WYXMBH DESC ) AS ROWNUMBER, X.* FROM SD_RSG_XMGL X"); sb.append(" WHERE 1=1"); sb.append(filter.toString()); sb.append(" ) A ORDER BY ROWNUMBER"); ResultSet rs = DbOper.executeQuery(sb.toString()); XSSFSheet hs1 = wb.getSheetAt(0); int i = 1; while (rs.next()) { XSSFRow row1 = hs1.createRow(i); String WYXMMC = turn(rs.getString("WYXMMC")); String WYXMJC = turn(rs.getString("WYXMJC")); String WYXMBH = turn(rs.getString("WYXMBH")); String XMFZR = turn(rs.getString("XMFZR")); String JF = turn(rs.getString("JF")); String JFXTBH = turn(rs.getString("JFXTBH")); String JFXTMC = turn(rs.getString("JFXTMC")); String YYS = turn(rs.getString("YYS")); String YYSID = turn(rs.getString("YYSID")); String ZS = turn(rs.getString("ZS")); String ZSID = turn(rs.getString("ZSID")); String XMZT = turn(rs.getString("XMZT")); if ("1".equals(XMZT)) XMZT = "未开始"; else if ("2".equals(XMZT)) XMZT = "进行中"; else if ("3".equals(XMZT)) { XMZT = "已完成"; } String XMSX = turn(rs.getString("XMSX")); String XMRWZT = turn(rs.getString("XMRWZT")); if ("1".equals(XMRWZT)) XMRWZT = "未开始"; else if ("2".equals(XMRWZT)) XMRWZT = "进行中"; else if ("3".equals(XMRWZT)) { XMRWZT = "已完成"; } String XMSJZT = turn(rs.getString("XMSJZT")); if ("1".equals(XMSJZT)) XMSJZT = "未开始"; else if ("2".equals(XMSJZT)) XMSJZT = "进行中"; else if ("3".equals(XMSJZT)) { XMSJZT = "已完成"; } String XMKSSJ = turn(rs.getString("XMKSSJ")); String XMJSSJ = turn(rs.getString("XMJSSJ")); String XMFW = turn(rs.getString("XMFW")); String XMSM = turn(rs.getString("XMSM")); String XMBZ = turn(rs.getString("XMBZ")); XSSFCell cell00 = row1.createCell(0); XSSFCell cell01 = row1.createCell(1); XSSFCell cell02 = row1.createCell(2); XSSFCell cell03 = row1.createCell(3); XSSFCell cell04 = row1.createCell(4); XSSFCell cell05 = row1.createCell(5); XSSFCell cell06 = row1.createCell(6); XSSFCell cell07 = row1.createCell(7); XSSFCell cell08 = row1.createCell(8); XSSFCell cell09 = row1.createCell(9); XSSFCell cell10 = row1.createCell(10); XSSFCell cell11 = row1.createCell(11); XSSFCell cell12 = row1.createCell(12); XSSFCell cell13 = row1.createCell(13); XSSFCell cell14 = row1.createCell(14); XSSFCell cell15 = row1.createCell(15); XSSFCell cell16 = row1.createCell(16); XSSFCell cell17 = row1.createCell(17); cell00.setCellValue(WYXMMC); cell01.setCellValue(WYXMJC); cell02.setCellValue(WYXMBH); cell03.setCellValue(XMFZR); cell04.setCellValue(JF); cell05.setCellValue(JFXTBH); cell06.setCellValue(JFXTMC); cell07.setCellValue(YYS); cell08.setCellValue(ZS); cell09.setCellValue(XMZT); cell10.setCellValue(XMSX); cell11.setCellValue(XMRWZT); cell12.setCellValue(XMSJZT); cell13.setCellValue(XMKSSJ); cell14.setCellValue(XMJSSJ); cell15.setCellValue(XMFW); cell16.setCellValue(XMSM); cell17.setCellValue(XMBZ); ++i; } } public static String turn(String s) { if (("NULL".equals(s)) || ("".equals(s)) || (s == null) || ("null".equals(s)) || ("无".equals(s))) { s = " "; } return s; } }
