/** * @instructions 如果目录不存在，首先创建 * @author jiyanle 2014-03-26 * @return */ public String createDirectory(String dirName) { File dir = new File(dirName); if (dir.exists()) { System.out.println("目录" + dirName + "已经创建"); } else { if (dir.mkdirs()) { System.out.println("创建目录" + dirName + "成功！"); } else { System.out.println("创建目录" + dirName + "失败！"); } } return dirName; } /** * @instructions 导出Excel表格 * @author jiyanle 2014-03-26 * @return */@SuppressWarnings("unchecked")public String exportExcel(){ HttpServletRequest request = ServletActionContext.getRequest(); HttpServletResponse response = ServletActionContext.getResponse(); try{ //定义action返回ajax的xml文件的字符范围及返回类型 response.setContentType("text/xml; charset=utf-8"); response.setHeader("Cache-Contorl", "no-cache"); PrintWriter out = response.getWriter(); // 创建Excel的工作书册 Workbook,对应到一个excel文档 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建Excel的工作sheet,对应到一个excel文档的tab HSSFSheet sheet = workbook.createSheet("公共场所信息"); // 设置excel每列宽度 sheet.setColumnWidth(0, 50*256); sheet.setColumnWidth(1, 15*256); sheet.setColumnWidth(2, 30*256); sheet.setColumnWidth(3, 15*256); // 创建字体样式 HSSFFont font = workbook.createFont(); font.setFontName("Verdana"); font.setBoldweight((short) 100); font.setFontHeight((short) 300); // 创建单元格样式 HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setFont(font);// 设置字体 //---------需要导入的数据集---------- List resultList = (List) request.getSession().getAttribute("resultList"); //标识位 String flag = request.getParameter("flag"); //写入标题 HSSFRow row_1 = sheet.createRow(0); HSSFCell cell_1 = row_1.createCell(0); cell_1.setCellStyle(style); cell_1.setCellValue("单位名称"); cell_1 = row_1.createCell(1); cell_1.setCellStyle(style); cell_1.setCellValue("姓名"); cell_1 = row_1.createCell(2); cell_1.setCellStyle(style); cell_1.setCellValue("许可证号"); cell_1 = row_1.createCell(3); cell_1.setCellStyle(style); cell_1.setCellValue("批准日期"); if (resultList != null && resultList.size() > 0) { for (int i = 0; i < resultList.size(); i++) { String ggcsDwmc = ""; String fddbr = ""; String xkzh = ""; String pzrq = ""; if(!"qzdpp".equals(flag)){ Ggcs ggcs = (Ggcs) resultList.get(i); ggcsDwmc = ggcs.getGgcsbjdDwmc();// 单位名称 fddbr = ggcs.getGgcsbjdFzr();// 法定代表人姓名 xkzh = ggcs.getGgcsbjdXkzhw() + "卫" + ggcs.getGgcsbjdXkzhz() + " 字[" + ggcs.getGgcsbjdXkzhk() + "]第" + ggcs.getGgcsbjdXkzhh() + "号"; pzrq = ggcs.getGgcsbjdXkzpzrq();// 批准日期 }else{ Object[] obj = (Object[])resultList.get(i); ggcsDwmc = obj[0]==null?"":(String)obj[0];// 单位名称 fddbr = obj[10]==null?"":(String)obj[10];// 法定代表人姓名 xkzh = (obj[2]==null?"":(String)obj[2]) + "卫" + (obj[3]==null?"":(String)obj[3]) + " 字[" + (obj[4]==null?"":(String)obj[4]) + "]第" + (obj[5]==null?"":(String)obj[5]) + "号"; pzrq = obj[6]==null?"":(String)obj[6];// 批准日期 } HSSFRow row_2 = sheet.createRow(i+1); HSSFCell cell_2 = row_2.createCell(0); cell_2.setCellValue(ggcsDwmc); cell_2 = row_2.createCell(1); cell_2.setCellValue(fddbr); cell_2 = row_2.createCell(2); cell_2.setCellValue(xkzh); cell_2 = row_2.createCell(3); cell_2.setCellValue(pzrq); } } Date currDate = new Date(); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH.mm.ss"); String currTime = format.format(currDate); String fileName = "未命名"+ currTime +".xls"; if("search".equals(flag)){ fileName = "公共场所全文检索列表"+ currTime +".xls";//默认导出的文件名 }else if("qzdpp".equals(flag)){ fileName = "公共场所全字段匹配列表"+ currTime +".xls";//默认导出的文件名 }else{ fileName = "公共场所基本查询列表"+ currTime +".xls";//默认导出的文件名 } String dirName = "D:\\reports\\";//默认导出的路径 dirName = this.createDirectory(dirName); try { FileOutputStream os = new FileOutputStream(dirName+fileName); workbook.write(os); os.close(); return null; } catch (Exception e) { e.printStackTrace(); log.error("公共场所导出Excel时出错！", e); return "dbError"; }} 附上前台Ajax处理：
//导出Excel方法function exportExcel(){ <% if(null != request.getAttribute("ggcsAllList") && ((List)request.getAttribute("ggcsAllList")).size() > 0){ request.getSession().setAttribute("resultList", request.getAttribute("ggcsAllList")); %> $.ajax({ type:"post",url:"ggcsAction!exportExcel.do", dataType: "text", data:"flag=ggcsnormal", contentType:"application/x-www-form-urlencoded; charset=utf-8", success: function (data) { alert(data); } }); <%}else{%> alert("当前列表无数据，无须导出！"); <%}%> } } 测试过程中发现导出的Excel文件都存入服务器的相对路径，显然这不是我们需要的效果。于是就有了下面的改进，效果跟下载效果相同，代码如下：
// 写入文件FileOutputStream os = new FileOutputStream("/" + "unnamed" + ".xls");workbook.write(os);os.close();// 将文件导出response.setHeader("Content-disposition", "p_w_upload;filename=" + toUtf8String(fileName + ".xls"));response.setHeader("Content-Type", "application/octet-stream");OutputStream ouputStream = response.getOutputStream();workbook.write(ouputStream);ouputStream.flush();ouputStream.close(); /** * @功能:转码 * @param s 需要转码的字符串 * @instructions 如果目录不存在，首先创建 * @author jiyanle 2014-03-28 * @return */ public String toUtf8String(String s) { StringBuffer sb = new StringBuffer(); for (int i = 0; i < s.length(); i++) { char c = s.charAt(i); if (c >= 0 && c <= 255) { sb.append(c); } else { byte[] b; try { b = Character.toString(c).getBytes("utf-8"); } catch (Exception ex) { b = new byte[0]; } for (int j = 0; j < b.length; j++) { int k = b[j]; if (k < 0) { k += 256; } sb.append("%" + Integer.toHexString(k).toUpperCase()); } } } return sb.toString(); }