来博客园两年多了，最近才开通了微博，因为懒所以也一直没有写东西，今天想整理一下自己前段时间遇到的一个导出的问题。
因为项目的需求，要做一部分导出功能。开始的时候用的公司的导出，但是很奇怪有部分模块导出的时候就是会报500错误，发现在删减一些字段后就恢复了正常，当时因为项目紧张，也就临时删减了一些，但也不是长久之计，之后自己在原本的基础上重新修改整理了一下，目前运行还算稳定，就此和大家分享一下。
导出需要三个部分，js，公共方法，后台方法。
js代码
1 function exportData() 2 { 3 //前台接收的参数 4 rwmc = $("#txt_rwmc").val(); 5 rwlb = $("#com_rwlb").combobox("getValues").join(","); 6 7 //调用后台导出功能 8 var service = new Service("cx.RybjcxBndService.exprotExcel"); 9 var str="<RWMC>" + rwmc + "</RWMC><RWLB>" + rwlb + "</RWLB>"; 10 var res = service.doService(str); 11 var oDoc = loadXml(res); 12 if (service.getCode() != "2000") { 13 showMessage("查询失败："+service.getMessage()); 14 return; 15 } 16 var nodata = oDoc.selectSingleNode("ROOT/NODATA").text; 17 if (nodata == "nodata") { 18 showMessage("无数据!"); 19 return; 20 } 21 // 获取导出信息 22 var titleName = oDoc.selectSingleNode("ROOT/TITLE_NAME").text; 23 var fileName = oDoc.selectSingleNode("ROOT/FILE_NAME").text; 24 var outPutInfo = oDoc.selectSingleNode("ROOT/OUTPUTINFO").text; 25 var download_path = oDoc.selectSingleNode("ROOT/DOWNLOAD_PATH").text; 26 if (outPutInfo != "") { 27 showMessage(outPutInfo); 28 return; 29 } 30 if (confirm("导出成功!确认下载文件吗？\n文件名称为："+fileName)) { 31 var file = fileName; 32 var showfile = titleName + ".xls"; 33 showfile = decodeURIComponent(showfile); 34 var idx = document.URL.indexOf("/adp"); 35 if (idx == -1) { 36 alert("无法识别主机地址：" + document.URL); 37 return; 38 } 39 var host = document.URL.substring(0, idx); 40 var width = screen.width; 41 var height = screen.height; 42 debugger; 43 // 打开下载页面 44 var param = "toolbar=no,location=no,status=yes,resizable=no,scrollbars=yes,top=" + height + ",left=" + width + ",width=100,height=100"; 45 // ---------------------------------------------------------- 46 // 此代码块为解决ie6下导出excel失败问题，原因是ie6对window.open(url)支持度不好, 47 // 当浏览器为ie6时改用window.location.href 48 var isIE=!!window.ActiveXObject; 49 var isIE6=isIE&&!window.XMLHttpRequest; 50 if (isIE6) { 51 window.location.href=host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path; 52 } else { 53 window.open(host + "/adp/work/gzkp/common/js/download_new.jsp?file=" + file + "&showfile=" + showfile + "&download_path=" + download_path, "_blank", param); 54 } 55 } 56 }
公共类
1 package ctais.business.gzkp.common; 2 import java.io.File; 3 import java.io.FileInputStream; 4 import java.text.SimpleDateFormat; 5 import java.util.Date; 6 7 import org.apache.poi2.hssf.usermodel.HSSFCell; 8 import org.apache.poi2.hssf.usermodel.HSSFCellStyle; 9 import org.apache.poi2.hssf.usermodel.HSSFFont; 10 import org.apache.poi2.hssf.usermodel.HSSFRow; 11 import org.apache.poi2.hssf.usermodel.HSSFSheet; 12 import org.apache.poi2.hssf.usermodel.HSSFWorkbook; 13 14 import ctais.business.dashboard.service.ExportExcel; 15 16 import ctais.config.Config; 17 import ctais.services.data.DataWindow; 18 import ctais.services.xml.XMLDataObject; 19 import ctais.services.xml.XMLParser; 20 import ctais.util.StringEx; 21 import jxl.Workbook; 22 import jxl.format.Alignment; 23 import jxl.write.Label; 24 import jxl.write.WritableCellFormat; 25 import jxl.write.WritableFont; 26 import jxl.write.WritableSheet; 27 import jxl.write.WritableWorkbook; 28 29 30 /** 31 * <p>Title: 生成EXCEL文件</p> 32 * <p>Description: 转换String字符串为EXCEL文档</p> 38 */ 39 40 public class CreateExcel { 41 private final static String CONFIG_FILE_PATH = Config.CTAIS_HOME; 42 WritableWorkbook wwb = null; 43 XMLDataObject xdo = null; 44 45 public CreateExcel(){ 46 47 } 48 49 /** 50 * 生成EXCEL 51 * @param sql 查询SQL 52 * @param czryDm 操作人员代码 53 * @param titles 导出列标题 54 * @param exlTitle excel表头 55 * @return 56 * @throws Exception 57 */ 58 public String newToExcel(String sql,String czryDm,String[] titles,String exlTitle) throws Exception 59 { 60 try { 61 HSSFWorkbook wb = new HSSFWorkbook(); 62 HSSFSheet sheet = wb.createSheet(); 63 ExportExcel exportExcel = new ExportExcel(wb, sheet); 64 StringBuffer sffer = new StringBuffer(); 65 //int colNum = 30; 66 67 DataWindow dw = DataWindow.dynamicCreate(sql.toString()); 68 dw.setConnectionName(Icomm.GZKPJNDI); 69 long dwRet = dw.retrieve(); 70 if (dwRet <= 0) { 71 sffer.append("<NODATA>nodata</NODATA>"); 72 return sffer.toString(); 73 } else { 74 sffer.append("<NODATA></NODATA>"); 75 } 76 77 int colNum = dw.getColumnCount(); 78 79 // 给工作表列定义列宽(实际应用自己更改列数) 80 for (short i = 0; i <= colNum; i++) { 81 sheet.setColumnWidth(i, (short) 4000); 82 } 83 // 创建单元格样式 84 HSSFCellStyle cellHeadStyle = wb.createCellStyle(); 85 // 指定单元格居中对齐 86 cellHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 87 // 指定单元格垂直居中对齐 88 cellHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 89 // 指定当单元格内容显示不下时自动换行 90 cellHeadStyle.setWrapText(true); 91 // 设置单元格字体 92 HSSFFont headFont = wb.createFont(); 93 headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 94 headFont.setFontName("宋体"); 95 headFont.setFontHeight((short) 200); 96 cellHeadStyle.setFont(headFont); 97 98 // 创建报表头部 99 Date dt=new Date(); 100 SimpleDateFormat sdt=new SimpleDateFormat("yyyyMMddhhmmssS"); 101 String sfm = czryDm + "_" + sdt.format(dt); 102 103 // 设置列头 104 exportExcel.createNormalHead(exlTitle, colNum-1); 105 HSSFRow row1 = sheet.createRow(1); 106 107 for(int i = 0; i < titles.length; i ++) { 108 HSSFCell cell = row1.createCell((short)i); 109 cell.setEncoding(HSSFCell.ENCODING_UTF_16); 110 cell.setCellStyle(cellHeadStyle); 111 cell.setCellValue(titles[i]); 112 } 113 Object value = ""; 114 115 //设置表格样式 116 HSSFCellStyle cellStyle = wb.createCellStyle(); 117 // 指定单元格居中对齐 118 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 119 // 指定单元格垂直居中对齐 120 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 121 // 指定当单元格内容显示不下时自动换行 122 cellStyle.setWrapText(true); 123 // 设置单元格字体 124 HSSFFont font = wb.createFont(); 125 font.setBoldweight(HSSFFont.SS_NONE); 126 font.setFontName("宋体"); 127 font.setFontHeight((short) 200); 128 cellStyle.setFont(font); 129 for(int i = 0 ; i < dw.getRowCount(); i++) { 130 HSSFRow row = sheet.createRow(i + 2); 131 for(int j = 1; j <= dw.getColumnCount(); j++) { 132 HSSFCell cell = row.createCell((short)(j-1)); 133 cell.setEncoding(HSSFCell.ENCODING_UTF_16); 134 cell.setCellStyle(cellStyle); 135 value = dw.getItemAny(i, j-1); 136 if(value == null) { 137 cell.setCellValue(""); 138 } else { 139 cell.setCellValue(value.toString()); 140 } 141 } 142 } 143 //设置导出路径，此处需要注意如果是Linux系统需要手动建路径，（此处的原因有人比较清楚的话还请指教）引用新建的文件路径 144 String path = "/export/"; 145 File file = new File(path); 146 if(!file.exists()) { 147 file.mkdirs(); 148 } 149 150 String fileName = sfm+".xls"; 151 152 //String pth = path.trim() + File.separator + fileName; 153 String pth = path.trim() + fileName; 154 pth = pth.trim(); 155 156 String outPutInfo = exportExcel.outputExcel(pth); 157 158 sffer.append("<TITLE_NAME>" + sfm + "</TITLE_NAME>"); 159 sffer.append("<DOWNLOAD_PATH>" + path + "</DOWNLOAD_PATH>"); 160 sffer.append("<FILE_NAME>" + fileName + "</FILE_NAME>"); 161 sffer.append("<OUTPUTINFO>" + outPutInfo + "</OUTPUTINFO>"); 162 163 return sffer.toString(); 164 } 165 catch (Exception e) { 166 e.printStackTrace(); 167 throw new Exception(e.getMessage()); 168 } 169 } 170 171 }
后台代码
1 /** 2 * 导出功能 3 * @param xdo 前台传参 4 * @param czryDm 操作人员代码 5 * @return 生成的XLS信息 6 * @throws Exception 异常说明 7 */ 8 public String exportExcel(XMLDataObject args,String czryDm) throws Exception 9 { 10 //接收前台传递的查询参数 11 String rwmc = StringEx.sNull(args.getItemValue("RWMC")); 12 String rwlb = StringEx.sNull(args.getItemValue("RWLB")); 13 14 if(null != rwmc && !"".equals(rwmc)) 15 { 16 sqlWhere.append(" AND A.RWMC LIKE '%"+rwmc+"%' "); 17 } 18 if(null != rwlb && !"".equals(rwlb)) 19 { 20 sqlWhere.append(" AND A.RWLB_DM = '"+rwlb+"' "); 21 } 22 23 StringBuilder sql = new StringBuilder(); 24 //拼接查询SQL 25 sql.append("SELECT RWXH,RWMC FROM RWXX") 26 .append(sqlWhere).append(" ORDER BY RWXH ) ").append(sqlisWhere); 27 //导出的列标题 28 String[] titles = {"任务序号","任务名称"}; 29 30 //实例化公共类 31 CreateExcel excel = new CreateExcel(); 32 return excel.newToExcel(sql.toString(), czryDm,titles,"Exlcel表头"); 33 }
1928
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
