问题描述:
  Web开发是,我们经常会遇见客户要求,把某些数据导出Excel,有利于客户分析和打印等等。下面是导出Excel下载的对应是JSP.
  1.点击“导出”按钮时,触发exportExcel()的JS方法,JS方法跳到对应的JSP,实现导出功能。
1 //导出成EXCEL
2 function exportExcel(){
3     var checkboxes = j$("input[name='serial_no_check']:checked");            //得到所有选中的checkbox对象
4     if(checkboxes.length <= 1){
5         sl_alert("请至少选择一条记录!");
6         return false;
7     }
8     //获取所有选中的serial_no
9     var serial_no_list = "";
10     j$.each(checkboxes,function(i, val){    //该val是dom对象
11             serial_no_list += j$(val).val()+ ",";
12     });
13     location = "list_cc_export.jsp?serial_no_list=" + serial_no_list;
14 }
  2.在list_cc_export.jsp里面实现导出功能,里面主要是JAVA代码
1 <%@ page contentType="text/html; charset=GBK"  import="com.enfo.intrust.risk.vo.*,com.enfo.intrust.risk.*,com.enfo.intrust.dao.*,com.enfo.intrust.tools.*,com.enfo.intrust.intrust.*,java.math.
*,jxl.write.*,,com.enfo.intrust.project.gain.*"%>
2
3 <%@ include file="/includes/operator.inc" %>
4 <%@ include file="/includes/parameter.inc" %>
5 <%
6 try{
7
8 Integer app_problem_id = Utility.parseInt(Utility.trimNull(request.getParameter("app_problem_id")),Integer.valueOf("0"));
9 Integer sub_product_id = Utility.parseInt(Utility.trimNull(request.getParameter("sub_product_id")),Integer.valueOf("0"));
10 String serial_no_list =Utility.trimNull(request.getParameter("serial_no_list"));
11
12
13 //调用过程
14 List list = null;
15 Object[] params = new Object[1];
16 params[0] = serial_no_list;
17 list = DBManager.listProcAll("{call SP_QUERY_TPLRULENOTICELIST_CC_EXCEL(?)}", params);
18
19
20 response.setContentType("application/vnd.ms-excel");
21 String excelName = "信托划款指令模板";
22 response.addHeader("Content-Disposition","attachment;filename="+new String(excelName.getBytes("GBK"), "ISO-8859-1") +".xls");
23
24 java.io.OutputStream os = response.getOutputStream();
25 out.clear();
26 out = pageContext.pushBody();
27 jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os);
28
29 jxl.write.WritableSheet  ws = wwb.createSheet("外部指令",0);
30
31 // 1、标题的格式
32 // 制定子字串格式
33 WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
34         16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
35 // 指定单元格的各种属性
36 WritableCellFormat format = new WritableCellFormat(font);
37 // 指定水平对齐的方式居中
38 format.setAlignment(Alignment.CENTRE);
39 // 制定垂直对齐的方式居中
40 format.setVerticalAlignment(VerticalAlignment.CENTRE);
41 // 合并单元格
42 //ws.mergeCells(0, 0, 13, 0);
43 //ws.setRowView(0, 500);// 行高
44 // 添加标题
45 //jxl.write.Label labelC = new jxl.write.Label(0, 0, "受益人明细表" , format);
46 //ws.addCell(labelC);
47
48 // 2、表头的格式
49 WritableFont fontTop = new WritableFont(WritableFont.createFont("宋体"),
50         10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
51 WritableCellFormat formatTop = new WritableCellFormat(fontTop);
52 formatTop.setAlignment(Alignment.CENTRE);
53 formatTop.setVerticalAlignment(VerticalAlignment.CENTRE);
54 formatTop.setBorder(Border.ALL, BorderLineStyle.THIN);
55
56 // 3、内容的格式
57 // 居左
58 WritableFont fontLeft = new WritableFont(WritableFont.createFont("宋体"),
59         10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
60 WritableCellFormat formatLeft = new WritableCellFormat(fontLeft);
61 formatLeft.setAlignment(Alignment.LEFT);
62 formatLeft.setVerticalAlignment(VerticalAlignment.CENTRE);
63 formatLeft.setBorder(Border.ALL, BorderLineStyle.THIN);
64 // 剧中
65 WritableFont fontCenter = new WritableFont(WritableFont
66         .createFont("宋体"), 10, WritableFont.NO_BOLD, false,
67         jxl.format.UnderlineStyle.NO_UNDERLINE);
68 WritableCellFormat formatCenter = new WritableCellFormat(fontCenter);
69 formatCenter.setAlignment(Alignment.CENTRE);
70 formatCenter.setVerticalAlignment(VerticalAlignment.CENTRE);
71 formatCenter.setBorder(Border.ALL, BorderLineStyle.THIN);
72 // 居右
73 WritableFont fontRight = new WritableFont(
74         WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false,
75         jxl.format.UnderlineStyle.NO_UNDERLINE);
76 WritableCellFormat formatRight = new WritableCellFormat(fontRight);
77 formatRight.setAlignment(Alignment.RIGHT);
78 formatRight.setVerticalAlignment(VerticalAlignment.CENTRE);
79 formatRight.setBorder(Border.ALL, BorderLineStyle.THIN);
80
81 WritableFont font_1 = new WritableFont(WritableFont.createFont("宋体"),
82         10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE);
83 // 指定单元格的各种属性
84 WritableCellFormat format_1 = new WritableCellFormat(font_1);
85 // 指定水平对齐的方式居中
86 format_1.setAlignment(Alignment.CENTRE);
87 // 制定垂直对齐的方式居中
88 format_1.setVerticalAlignment(VerticalAlignment.CENTRE);
89
90 // 指定单元格的各种属性
91 WritableCellFormat format_2 = new WritableCellFormat(font_1);
92 // 指定水平对齐的方式居中
93 format_2.setAlignment(Alignment.RIGHT);
94 // 制定垂直对齐的方式居中
95 format_2.setVerticalAlignment(VerticalAlignment.CENTRE);
96
97 jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#,##0.00");
98 jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(fontRight, nf);
99 wcfN.setAlignment(Alignment.RIGHT);
100 wcfN.setVerticalAlignment(VerticalAlignment.CENTRE);
101 wcfN.setBorder(Border.ALL, BorderLineStyle.THIN);
102
103
104 ws.setColumnView(0, 15);
105 ws.setColumnView(1, 15);
106 ws.setColumnView(2, 15);
107 ws.setColumnView(3, 15);
108
109 ws.setColumnView(4, 25);
110 ws.setColumnView(5, 25);
111 ws.setColumnView(6, 25);
112 ws.setColumnView(7, 25);
113 ws.setColumnView(8, 25);
114 ws.setColumnView(9, 25);
115 ws.setColumnView(10, 25);
116 ws.setColumnView(11, 25);
117 ws.setColumnView(12, 25);
118 ws.setColumnView(13, 25);
119 ws.setColumnView(14, 25);
120 ws.setColumnView(15, 25);
121 ws.setColumnView(16, 25);
122 ws.setColumnView(17, 25);
123 ws.setColumnView(18, 25);
124 ws.setColumnView(19, 25);
125 ws.setColumnView(20, 25);
126 ws.setColumnView(21, 25);
127 int startRowNum = 0;
128
129 ws.addCell(new jxl.write.Label(0, startRowNum, "*发生日期" , formatCenter));
130 ws.addCell(new jxl.write.Label(1, startRowNum, "*交收日期" , formatCenter));
131 ws.addCell(new jxl.write.Label(2, startRowNum, "*基金代码" , formatCenter));
132 ws.addCell(new jxl.write.Label(3, startRowNum, "*金额" , formatCenter));
133 ws.addCell(new jxl.write.Label(4, startRowNum, "款项用途" , formatCenter));
134 ws.addCell(new jxl.write.Label(5, startRowNum, "*本方银行编号" , formatCenter));
135 ws.addCell(new jxl.write.Label(6, startRowNum, "*本方开户行名称" , formatCenter));
136 ws.addCell(new jxl.write.Label(7, startRowNum, "*本方银行账号" , formatCenter));
137 ws.addCell(new jxl.write.Label(8, startRowNum, "*本方银行户名" , formatCenter));
138 ws.addCell(new jxl.write.Label(9, startRowNum, "本方银行省份代码" , formatCenter));
139 ws.addCell(new jxl.write.Label(10, startRowNum, "本方银行城市代码" , formatCenter));
140 ws.addCell(new jxl.write.Label(11, startRowNum, "*对方银行编号" , formatCenter));
141 ws.addCell(new jxl.write.Label(12, startRowNum, "*对方开户行名称" , formatCenter));
142 ws.addCell(new jxl.write.Label(13, startRowNum, "*对方银行账号" , formatCenter));
143
144 ws.addCell(new jxl.write.Label(14, startRowNum, "*对方银行户名" , formatCenter));
145 ws.addCell(new jxl.write.Label(15, startRowNum, "对方银行省份代码" , formatCenter));
146 ws.addCell(new jxl.write.Label(16, startRowNum, "对方银行城市代码" , formatCenter));
147 ws.addCell(new jxl.write.Label(17, startRowNum, "对方银行大额支付号" , formatCenter));
148 ws.addCell(new jxl.write.Label(18, startRowNum, "备注" , formatCenter));
149 ws.addCell(new jxl.write.Label(19, startRowNum, "托管行联系人" , formatCenter));
150 ws.addCell(new jxl.write.Label(20, startRowNum, "托管行联系电话" , formatCenter));
151 ws.addCell(new jxl.write.Label(21, startRowNum, "托管行传真号码" , formatCenter));
152 //查数据库,并把数据写入到EXCEL
153 int row = 0;
154 int rowAdd = 1;
155 for(; list != null && row < list.size() ; row++ ) {
156     int cols = 0;
157     Map map = (Map)list.get(row);
158     BigDecimal sy_money = Utility.parseDecimal(Utility.trimNull(map.get("SY_MONEY3")),new BigDecimal(0));
159     BigDecimal rg_money2 = Utility.parseDecimal(Utility.trimNull(map.get("RG_MONEY2")),new BigDecimal(0));
160     String sy_amount = user_id.intValue() == 11 ? Utility.trimNull(Format.formatMoney(Utility.parseDecimal(Utility.trimNull(map.get("SY_AMOUNT")),new BigDecimal(0)))) : Utility.trimNull(Format.formatMoney(rg_money2));
161     BigDecimal sy_rate = Utility.parseDecimal(Utility.trimNull(map.get("SY_RATE")),new BigDecimal(0));
162
163     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DATE1")), formatCenter));
164     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DATE2")), formatCenter));
165     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("PRODUCT_CODE")), formatCenter));
166     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, sy_money.toString(), formatRight));
167     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("SUMMARY")), formatCenter));
168     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_CODE")), formatCenter));
169     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_ALL_NAME")), formatCenter));
170     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("TG_BANK_ACCT")), formatCenter));
171     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("TG_ACCT_NAME")), formatCenter));
172     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_CITY_CODE")), formatCenter));
173     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BANK_CITY")), formatCenter));
174     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_CODE")), formatCenter));
175     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_ALL_NAME")), formatCenter));
176
177     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_ACCT")), formatCenter));
178     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("CUST_ACCT_NAME")), formatCenter));
179     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_CITY_CODE")), formatCenter));
180     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_BANK_CITY")), formatCenter));
181     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("BIG_BANK_CODE")), formatCenter));
182     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_SUMMARY")), formatCenter));
183     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_LIKE_NAME")), formatCenter));
184     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_LINK_PHONE")), formatCenter));
185     ws.addCell(new jxl.write.Label(cols++, row + rowAdd, Utility.trimNull(map.get("DF_LINK_FAX")), formatCenter));
186 }
187
188 /*
189 WritableCellFormat formatLeft_1 = new WritableCellFormat(fontLeft);
190 formatLeft_1.setAlignment(Alignment.LEFT);
191 formatLeft_1.setVerticalAlignment(VerticalAlignment.CENTRE);
192 formatLeft_1.setBorder(Border.ALL, BorderLineStyle.THIN);
193 formatLeft_1.setWrap(true);
194
195 ws.addCell(new jxl.write.Label(0, row + 2, "以上述信息为准进行收入和支付操作 信托团队负责人:        信托团队填表人:        信财部复核人:        信财部负责人:", formatLeft_1));
196 ws.mergeCells(0, row + 2, 13, row + 3);
197 ws.setRowView(row + 2, 800);// 行高
198
199 */
200 wwb.write();
201 wwb.close();
202 os.close();
203 }catch(Exception e){ throw e ;}
204 %>
  浏览器会弹出下载保存界面