來博客園兩年多了，最近才開通了微博，因為懶所以也一直沒有寫東西，今天想整理一下自己前段時間遇到的一個導出的問題。
因為項目的需求，要做一部分導出功能。開始的時候用的公司的導出，但是很奇怪有部分模塊導出的時候就是會報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 }
