- 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 %>
来源: