function exportExcel() { $.messager .confirm( '提示信息', '您确定要导出到Excel?', function(r) { if (r) { var columns = $("#queryTmallTcMsgGrid").datagrid( "options").columns[0]; var stringTitle = ""; var stringFields = ""; for (var i = 0; i < columns.length; i++) { //导出剔除'联系手机' if (columns[i].title == "联系手机") { continue; } stringTitle = stringTitle + columns[i].title + ","; if (columns[i].title == "客户性质") { stringFields = stringFields + "customerNatureName,"; } else if (columns[i].title == "是否回访") { stringFields = stringFields + "isCallbackName,"; } else if (columns[i].title == "是否购买") { stringFields = stringFields + "isBuyName,"; } else if (columns[i].title == "留言来源") { stringFields = stringFields + "messageSourceName,"; } else if (columns[i].title == "客户类型") { stringFields = stringFields + "userTypeName,"; } else if (columns[i].title == "分配类型") { stringFields = stringFields + "distributionTypeName,"; } else { stringFields = stringFields + columns[i].field + ","; } } var queryParams = $("#queryTmallTcMsgGrid") .datagrid("options").queryParams; queryParams.gridTitle = stringTitle; queryParams.gridField = stringFields; queryParams.moduleName = "天猫留言.xls"; var formObj = $("<form></form>") .attr("method", "post") .attr( "action", appPath + "/page/tmallTcMessageAct/exportExcel"); formObj .append( "<input type='text' name='gridTitle'>") .append( "<input type='text' name='gridField'>") .append( "<input type='text' name='moduleName'>") .append( "<input type='text' name='tmallOrderId'>") .append( "<input type='text' name='memberName'>") .append( "<input type='text' name='alipayUserName'>") .append( "<input type='text' name='alipayPaidFee'>") .append( "<input type='text' name='message'>") .append( "<input type='text' name='messageSource'>") .append( "<input type='text' name='receiveUser'>") .append( "<input type='text' name='receiveFullAddress'>") .append( "<input type='text' name='receiveTel'>") .append( "<input type='text' name='receiveMobile'>") .append( "<input type='text' name='orderCreateTime'>") .append( "<input type='text' name='goodsNo'>") .append( "<input type='text' name='goodsTopic'>") .append( "<input type='text' name='goodsCount'>") .append( "<input type='text' name='isCallback'>") .append("<input type='text' name='isBuy'>") .append( "<input type='text' name='notBuyReason'>") .append( "<input type='text' name='callbackTime'>") .append( "<input type='text' name='commitUserid'>") .append( "<input type='text' name='callbackUserid'>") .append( "<input type='text' name='userType'>") .append( "<input type='text' name='serviceName'>") .append( "<input type='text' name='assignNum'>") .append( "<input type='text' name='distributionType'>") .append( "<input type='text' name='customerNature'>") .append( "<input type='text' name='bgMessageTime'>") .append( "<input type='text' name='endMessageTime'>") //8459修正 /*.append( "<input type='text' name='importId'>")*/ .append( "<input type='text' name='assignId'>") .append( "<input type='text' name = 'note'>") .append( "<input type='text' name = 'orderId'>") .css('display', 'none').appendTo("body"); formObj.form("load", queryParams); formObj.submit(); formObj.remove(); } }); }
@RequestMapping(value = "/exportExcel") public String exportExcel(HttpServletRequest request, HttpServletResponse response, TmallTcMessage tmallTcMessage) { String objList = ""; // Integer userId = (Integer) request.getSession().getAttribute("userId"); // Integer num = systemService.checkName(userId); Integer strLength = 4; // if (num != null && num > 0) { // strLength = 0; // } tmallTcMessage.setStrLength(strLength); try { objList = exportService.exportExcelFile(request, response, tmallTcMessage, "TmallTcMessage.TmallMessageExportExcel"); } catch (Exception e) { logger.debug(e.getMessage()); e.printStackTrace(); } return objList; } =============================================基类========================================================================== public String exportExcelFile(HttpServletRequest request,HttpServletResponse response, Object obj, String sqlMap) throws Exception { String fileChName = request.getParameter("moduleName"); String gridTitle = request.getParameter("gridTitle"); String gridField = request.getParameter("gridField"); response.reset(); response.setCharacterEncoding("ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename="+new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/octet-stream"); OutputStream out = response.getOutputStream(); Map map = new HashMap(); List<Object> paymentInfoList = commonDao.queryForList(sqlMap, obj); if(paymentInfoList.size()>10000){ WritableWorkbook wbook = Workbook.createWorkbook(out);//直接写入内存,不要存放到硬盘中 jxl.write.WritableSheet wsheet = wbook.createSheet("Sheet1", 0);//定义sheet的名称 jxl.write.WritableFont wfont = null; // 字体 jxl.write.WritableCellFormat wcfFC = null; // 字体格式 jxl.write.Label wlabelString = null; // Excel表格的Cell,文本格式 for(int i=0;i<1;i++){ wsheet.setColumnView(i, 40);//设置列宽 } // 设置excel标题字体 wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 // 添加excel标题 jxl.write.Label wlabel1 = new jxl.write.Label(5, 0,"导出提示",wcfFC); wsheet.addCell(wlabel1); // 设置正文字体 wfont = new jxl.write.WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 //往Excel输出数据 wlabelString = new jxl.write.Label(5, 1, "导出数据超过一万条,系统不支持,请分批导出",wcfFC); wsheet.addCell(wlabelString); wbook.write(); // 写入文件 wbook.close(); out.flush(); out.close(); return "ok"; } String[] title = gridTitle.split(","); String[] propery = gridField.split(","); Integer titleNum = title.length;//Excel中字段的个数 String FileTitle = "";//Excel中正文标题 //--建立EXCEL索引、字段名、字段值的关系,存放到map中 for(int i=0;i<title.length;i++){ String PojoPropery = propery[i];//grid中title和field是一一对应的,所以可以这么写 String toUpp = PojoPropery.replaceFirst(PojoPropery.substring(0,1), PojoPropery.substring(0,1).toUpperCase());//把首字母转换为大写 String methodName = "get"+toUpp;//拼成pojo类中getXXX的方法名称 map.put(PojoPropery, new ExportExcelColumn(i,title[i],methodName)); } this.export(out, FileTitle, titleNum,paymentInfoList, map); return "ok"; }
==========================================================js=======================================================
//导出 function exportExcel(){ $.messager.confirm('提示信息','您确定要导出到Excel?',function(r){ if(r){ var columns = $("#linksCommonGrid").datagrid("options").columns[0]; //--------把标题grid标题和grid的field,拼接成字符串----------- var stringTitle = ""; var stringFields = ""; for(var i = 1; i < columns.length; i++){ stringTitle = stringTitle + columns[i].title + ","; stringFields = stringFields + columns[i].field + ","; } if(stringTitle.lastIndexOf(",") == (stringTitle.length-1)){ stringTitle = stringTitle.substring(0, stringTitle.lastIndexOf(",")); } if(stringFields.lastIndexOf(",") == (stringFields.length-1)){ stringFields = stringFields.substring(0, stringFields.lastIndexOf(",")); } var queryParams = $("#linksCommonGrid").datagrid("options").queryParams; queryParams.gridTitle = stringTitle; queryParams.gridField = stringFields; queryParams.moduleName = "友情链接.xls"; var formObj = $("<form></form>").attr("method","post").attr("action",appPath+"/page/pageIndexMrgAct/exportLinksList"); formObj.append("<input type='text' name='gridTitle'>") .append("<input type='text' name='gridField'>") .append("<input type='text' name='moduleName'>") .append("<input type='text' name='linkType'>") .append("<input type='text' name='url'>") .append("<input type='text' name='keywords1'>") .append("<input type='text' name='linkUrl1'>") .append("<input type='text' name='qq'>") .append("<input type='text' name='keywords2'>") .append("<input type='text' name='linkUrl2'>") .css('display','none') .appendTo("body"); formObj.form("load",queryParams); formObj.submit(); formObj.remove(); } }); }
=====================================================java=======================================================
/** * 导出excel * * @param request * @param response * @param website */ @RequestMapping(value = "/exportLinksList") @ResponseBody public String exportLinksList(HttpServletRequest request, HttpServletResponse response, PageIndexLinks pageIndexLinks) { try { List<Object> LinksList=null; ServiceMessage<List<Object>> res = pageIndexLinksService.getLinksListForExport(pageIndexLinks); if(res.getStatus().getCode().equals("0")){ LinksList=res.getResult(); if(LinksList!=null && LinksList.size()>0){ Map<String,String> paramMap=new HashMap<String,String>(); String fileChName = request.getParameter("moduleName"); String gridTitle = request.getParameter("gridTitle"); String gridField = request.getParameter("gridField"); paramMap.put("moduleName", fileChName); paramMap.put("gridTitle", gridTitle); paramMap.put("gridField", gridField); return exportService.exportExcelFileSEO(response, paramMap, LinksList); } } } catch (Exception e) { e.printStackTrace(); } return null; }
=========================基类===========================
/** * SEO daochu Excel */ public String exportExcelFileSEO(HttpServletResponse response, Map<String,String> paramMap, List<Object> objList)throws Exception { String fileChName = paramMap.get("moduleName"); String gridTitle = paramMap.get("gridTitle"); String gridField = paramMap.get("gridField"); response.reset(); response.setCharacterEncoding("ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename="+new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/octet-stream"); OutputStream out = response.getOutputStream(); Map map = new HashMap(); if(objList.size()>10000){ WritableWorkbook wbook = Workbook.createWorkbook(out);//直接写入内存,不要存放到硬盘中 jxl.write.WritableSheet wsheet = wbook.createSheet("Sheet1", 0);//定义sheet的名称 jxl.write.WritableFont wfont = null; // 字体 jxl.write.WritableCellFormat wcfFC = null; // 字体格式 jxl.write.Label wlabelString = null; // Excel表格的Cell,文本格式 for(int i=0;i<1;i++){ wsheet.setColumnView(i, 40);//设置列宽 } // 设置excel标题字体 wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 // 添加excel标题 jxl.write.Label wlabel1 = new jxl.write.Label(5, 0,"导出提示",wcfFC); wsheet.addCell(wlabel1); // 设置正文字体 wfont = new jxl.write.WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 //往Excel输出数据 wlabelString = new jxl.write.Label(5, 1, "导出数据超过一万条,系统不支持,请分批导出",wcfFC); wsheet.addCell(wlabelString); wbook.write(); // 写入文件 wbook.close(); out.flush(); out.close(); return "ok"; } String[] title = gridTitle.split(","); String[] propery = gridField.split(","); Integer titleNum = title.length;//Excel中字段的个数 String FileTitle = "";//Excel中正文标题 //--建立EXCEL索引、字段名、字段值的关系,存放到map中 for(int i=0;i<title.length;i++){ String PojoPropery = propery[i];//grid中title和field是一一对应的,所以可以这么写 String toUpp = PojoPropery.replaceFirst(PojoPropery.substring(0,1), PojoPropery.substring(0,1).toUpperCase());//把首字母转换为大写 String methodName = "get"+toUpp;//拼成pojo类中getXXX的方法名称 map.put(PojoPropery, new ExportExcelColumn(i,title[i],methodName)); } this.export(out, FileTitle, titleNum,objList, map); return "ok"; }