jsp页面将数据导出到Excel

一、列表页面的部分代码

[html] view plain copy

1. <inputtype="button"class="cxBut2"value="导出数据"onclick="_export()"/>

[javascript] view plain copy

1. function_export(){
2. document.location.href="download.jsp?czId=<%=czId%>&czNum=<%=czNum%>&beginIssue=<%=beginIssue%>&endIssue=<%=endIssue%>&pageNum=<%=pageNum%>&czName="+$("#czId").find("option:selected").text();
3. }

二、download.jsp

[html] view plain copy

1. <%@pagecontentType="application/vnd.ms-excel"language="java"import="java.util.*"pageEncoding="UTF-8"%>
2. <%@pageimport="com.zhcw.kaijiang.service.ExportExcal"%>
3. <%@pageimport="java.io.OutputStream"%>
4. <%
5. StringczNum=request.getParameter("czNum");
6. StringczId=request.getParameter("czId");
7. StringczName=request.getParameter("czName");
8. StringbeginIssue=request.getParameter("beginIssue");
9. StringendIssue=request.getParameter("endIssue");
10. StringpageNum=request.getParameter("pageNum");
11. response.resetBuffer();
12. response.setHeader("Content-Disposition","attachment;filename="+newString(czName.getBytes("UTF-8"),"iso8859-1")+".xls");//指定下载的文件名
13. response.setContentType("application/vnd.ms-excel");
14. try{
15. ExportExcalexportExcal=newExportExcal(beginIssue,czId,czName,czNum,endIssue,pageNum);
16. exportExcal.export(response.getOutputStream());
17. }catch(Exceptionex){
18. ex.printStackTrace();
19. }
20. %>

这个页面主要负责接收上一个页面传来的数据，然后调用后台相应的导出方法来实现导出功能。

注意：这两行代码必须要写：

[html] view plain copy

1. response.resetBuffer();<prename="code"class="html">response.setContentType("application/vnd.ms-excel");</pre>
2. <pre></pre>
3. <p></p>
4. <pre></pre>
5. <p></p>
6. <p></p>
7. 三、后台处理类<prename="code"class="java">packagecom.zhcw.kaijiang.service;
9. importjava.io.File;
10. importjava.io.FileOutputStream;
11. importjava.io.OutputStream;
12. importjava.text.DecimalFormat;
13. importjava.util.List;
15. importjxl.Workbook;
16. importjxl.format.Alignment;
17. importjxl.format.Border;
18. importjxl.format.BorderLineStyle;
19. importjxl.write.Label;
20. importjxl.write.WritableCellFormat;
21. importjxl.write.WritableSheet;
22. importjxl.write.WritableWorkbook;
24. importcom.common.PropertiesOperator;
25. importcom.crawler.entity.KaiJiangInfo;
26. importcom.zhcw.kaijiang.util.StringUtil;
28. /**
29. *将查询出来的信息导出到excel中
30. *
31. *@authorzhcw
32. *
33. */
34. publicclassExportExcal{
35. privateStringczNum="";
36. privateStringczId="";
37. privateStringczName="";
38. privateStringbeginIssue="";
39. privateStringendIssue="";
40. privateStringpageNum="";
42. publicExportExcal(StringbeginIssue,StringczId,StringczName,StringczNum,StringendIssue,StringpageNum){
43. super();
44. this.beginIssue=(beginIssue==null?"":beginIssue);
45. this.endIssue=(endIssue==null?"":endIssue);
46. this.czId=(czId==null?"":czId);
47. this.czName=(czName==null?"":czName);
48. this.czNum=(czNum==null||czNum.trim().equals("")||czNum.trim().equals("null"))?"30":czNum;
49. this.pageNum=(pageNum==null||pageNum.trim().equals("")||pageNum.trim().equals("null"))?"1":pageNum;
50. }
52. publicList<KaiJiangInfo>kaiJiangInfoList(){
53. KaijiangInfoServicekaijiangInfoService=newKaijiangInfoService();
54. if(this.beginIssue!=null&&this.beginIssue.trim().length()>0){
55. returnkaijiangInfoService.getSplitPageByCzIdNew(Long.valueOf(this.czId),Integer.valueOf(pageNum),400,this.beginIssue,this.endIssue);
56. }else{
57. returnkaijiangInfoService.getSplitPageByCzIdNew(Long.valueOf(this.czId),Integer.valueOf(pageNum),Integer.parseInt(czNum));
58. }
59. }
61. publicvoidexport(OutputStreamoutput)throwsException{
62. PropertiesOperatorpropertiesOperator=newPropertiesOperator();
63. Stringcz_id_css_1=propertiesOperator.getMessage("cz_id_css_1")==null?"":propertiesOperator.getMessage("cz_id_css_1").trim();
64. Stringcz_id_css_2=propertiesOperator.getMessage("cz_id_css_2")==null?"":propertiesOperator.getMessage("cz_id_css_2").trim();
65. Stringcz_id_css_3=propertiesOperator.getMessage("cz_id_css_3")==null?"":propertiesOperator.getMessage("cz_id_css_3").trim();
66. Stringcz_id_css_4=propertiesOperator.getMessage("cz_id_css_4")==null?"":propertiesOperator.getMessage("cz_id_css_4").trim();
67. Stringcz_id_css_5=propertiesOperator.getMessage("cz_id_css_5")==null?"":propertiesOperator.getMessage("cz_id_css_5").trim();
68. if(StringUtil.contains(this.czId,cz_id_css_1)){
69. this.exportSSQExcel(output);
70. }elseif(StringUtil.contains(this.czId,cz_id_css_2)){
71. this.export3DExcel(output);
72. }
73. }
74. privatevoidexportExcel(Stringtype,OutputStreamoutput)throwsException{
75. WritableWorkbookworkbook=Workbook.createWorkbook(output);//创建工作薄
76. WritableSheetsheet=workbook.createSheet(this.czName,0);//创建第一个工作表，name:工作表名称
77. //设置列宽度
78. sheet.setColumnView(0,7);
79. sheet.setColumnView(1,15);
80. sheet.setColumnView(2,13);
81. sheet.setColumnView(3,25);
82. sheet.setColumnView(4,15);
83. sheet.setColumnView(6,15);
84. sheet.setColumnView(8,15);
85. sheet.setColumnView(10,15);
86. sheet.setColumnView(11,15);
87. WritableCellFormatformat=newWritableCellFormat();
88. format.setAlignment(Alignment.CENTRE);
89. format.setBorder(Border.ALL,BorderLineStyle.THIN);
90. introw=0;
91. //合并标题行
92. sheet.mergeCells(0,row,11,row);
94. //合并
95. sheet.mergeCells(0,row+1,0,row+2);
96. sheet.mergeCells(1,row+1,1,row+2);
97. sheet.mergeCells(2,row+1,2,row+2);
98. sheet.mergeCells(3,row+1,3,row+2);
99. sheet.mergeCells(4,row+1,4,row+2);
100. sheet.mergeCells(11,row+1,11,row+2);
102. sheet.mergeCells(5,row+1,6,row+1);
103. sheet.mergeCells(7,row+1,8,row+1);
104. sheet.mergeCells(9,row+1,10,row+1);
106. Labellabel=null;//用于写入文本内容到工作表中去
107. //开始写入第一行，即标题栏
108. if(this.beginIssue.length()>0){
109. label=newLabel(0,row,czName+"从"+this.beginIssue+"到"+this.endIssue+"期的中奖信息",format);//参数依次代表列数、行数、内容
110. sheet.addCell(label);//写入单元格
111. }else{
112. label=newLabel(0,row,czName+"的前"+this.czNum+"期的中奖信息",format);//参数依次代表列数、行数、内容
113. sheet.addCell(label);//写入单元格
114. }
115. //第二行写表头
116. label=newLabel(0,row+1,"序号",format);//参数依次代表列数、行数、内容
117. sheet.addCell(label);//写入单元格
118. label=newLabel(1,row+1,"开奖日期",format);//参数依次代表列数、行数、内容
119. sheet.addCell(label);//写入单元格
120. label=newLabel(2,row+1,"期号",format);//参数依次代表列数、行数、内容
121. sheet.addCell(label);//写入单元格
122. label=newLabel(3,row+1,"中奖号码",format);//参数依次代表列数、行数、内容
123. sheet.addCell(label);//写入单元格
124. label=newLabel(4,row+1,"销售额(元)",format);//参数依次代表列数、行数、内容
125. sheet.addCell(label);//写入单元格
126. if(type!=null&&type.trim().equals("ssq")){
127. label=newLabel(5,row+1,"一等奖",format);//参数依次代表列数、行数、内容
128. sheet.addCell(label);//写入单元格
129. label=newLabel(7,row+1,"二等奖",format);//参数依次代表列数、行数、内容
130. sheet.addCell(label);//写入单元格
131. label=newLabel(9,row+1,"三等奖",format);//参数依次代表列数、行数、内容
132. sheet.addCell(label);//写入单元格
133. }elseif(type!=null&&type.trim().equals("3d")){
134. label=newLabel(5,row+1,"直选",format);//参数依次代表列数、行数、内容
135. sheet.addCell(label);//写入单元格
136. label=newLabel(7,row+1,"组三",format);//参数依次代表列数、行数、内容
137. sheet.addCell(label);//写入单元格
138. label=newLabel(9,row+1,"组六",format);//参数依次代表列数、行数、内容
139. sheet.addCell(label);//写入单元格
140. }
141. //只有双色球有奖池金额
142. if(type!=null&&type.trim().equals("ssq")){
143. label=newLabel(11,row+1,"奖池(元)",format);//参数依次代表列数、行数、内容
144. sheet.addCell(label);//写入单元格
145. }
146. label=newLabel(5,row+2,"注数",format);//参数依次代表列数、行数、内容
147. sheet.addCell(label);//写入单元格
148. label=newLabel(6,row+2,"奖金",format);//参数依次代表列数、行数、内容
149. sheet.addCell(label);//写入单元格
150. label=newLabel(7,row+2,"注数",format);//参数依次代表列数、行数、内容
151. sheet.addCell(label);//写入单元格
152. label=newLabel(8,row+2,"奖金",format);//参数依次代表列数、行数、内容
153. sheet.addCell(label);//写入单元格
154. label=newLabel(9,row+2,"注数",format);//参数依次代表列数、行数、内容
155. sheet.addCell(label);//写入单元格
156. label=newLabel(10,row+2,"奖金",format);//参数依次代表列数、行数、内容
157. sheet.addCell(label);//写入单元格
158. List<KaiJiangInfo>list=this.kaiJiangInfoList();
159. intindex=1;
160. intnum=2;
161. DecimalFormatdf=newDecimalFormat("#0.00");
162. for(KaiJiangInfokaiJiangInfo:list){
163. num=num+1;
164. label=newLabel(0,num,String.valueOf((Integer.parseInt(pageNum)-1)*400+(index++)),format);//参数依次代表列数、行数、内容
165. sheet.addCell(label);//写入单元格
167. label=newLabel(1,num,kaiJiangInfo.getKjDate(),format);//参数依次代表列数、行数、内容
168. sheet.addCell(label);//写入单元格
170. label=newLabel(2,num,kaiJiangInfo.getIssue(),format);//参数依次代表列数、行数、内容
171. sheet.addCell(label);//写入单元格
173. if(type!=null&&type.trim().equals("ssq")){
174. label=newLabel(3,num,kaiJiangInfo.getKjZNum()+""+kaiJiangInfo.getKjTNum(),format);//参数依次代表列数、行数、内容
175. sheet.addCell(label);//写入单元格
176. }elseif(type!=null&&type.trim().equals("3d")){
177. label=newLabel(3,num,kaiJiangInfo.getKjZNum(),format);//参数依次代表列数、行数、内容
178. sheet.addCell(label);//写入单元格
179. }
181. label=newLabel(4,num,df.format(kaiJiangInfo.getSales()),format);//参数依次代表列数、行数、内容
182. sheet.addCell(label);//写入单元格
184. label=newLabel(5,num,String.valueOf(kaiJiangInfo.getNoteOne()),format);//参数依次代表列数、行数、内容
185. sheet.addCell(label);//写入单元格
187. label=newLabel(6,num,df.format(kaiJiangInfo.getBonusOne()),format);//参数依次代表列数、行数、内容
188. sheet.addCell(label);//写入单元格
190. label=newLabel(7,num,String.valueOf(kaiJiangInfo.getNoteTwo()),format);//参数依次代表列数、行数、内容
191. sheet.addCell(label);//写入单元格
193. label=newLabel(8,num,df.format(kaiJiangInfo.getBonusTwo()),format);//参数依次代表列数、行数、内容
194. sheet.addCell(label);//写入单元格
196. label=newLabel(9,num,String.valueOf(kaiJiangInfo.getNoteThree()),format);//参数依次代表列数、行数、内容
197. sheet.addCell(label);//写入单元格
199. label=newLabel(10,num,df.format(kaiJiangInfo.getBonusThree()),format);//参数依次代表列数、行数、内容
200. sheet.addCell(label);//写入单元格
201. //只有双色球有奖池金额
202. if(type!=null&&type.trim().equals("ssq")){
203. label=newLabel(11,num,df.format(kaiJiangInfo.getBonusPool()),format);//参数依次代表列数、行数、内容
204. sheet.addCell(label);//写入单元格
205. }
206. }
207. workbook.write();
208. workbook.close();
209. }
210. privatevoidexportSSQExcel(OutputStreamoutput)throwsException{
211. this.exportExcel("ssq",output);
212. }
214. privatevoidexport3DExcel(OutputStreamoutput)throwsException{
215. this.exportExcel("3d",output);
216. }
217. }
218. </pre><br>
219. <br>