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