Java导出Excel表格
问题描述：
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, "以上述信息为准进行收入和支付操作\r\n\r\n信托团队负责人: 信托团队填表人: 信财部复核人: 信财部负责人:", 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 %>
浏览器就会弹出下载保存界面
