JSP导出Excel文件的方法

<%@page import="jxl.workbook,com.ecc.emp.core.*,com.ecc.emp.data.*,com.ecc.emp.jdbc.connectionmanager,jxl.format.verticalalignment,java.sql.*,jxl.write.*,jxl.format.underlinestyle,javax.sql.datasource"%> <%@page language="java" contenttype="text/html; charset=utf-8"%> <%@taglib uri="/web-inf/ctp.tld" prefix="ctp"%> <jsp:directive.page import="java.util.list"/> <jsp:directive.page import="java.util.arraylist"/> <%@ include file="http://www.xxxx.com/head.jsp" %> <% context context = (context)request.getattribute("context");string flag ="交易日期,交易时间,摘要,借方发生额,贷方发生额,对方账号,对方户名,单位卡号,主卡标识,用途,备注"; indexedcollection indexedcollection = (indexedcollection)context.getdataelement("parentcarddetails"); string[] sourcestrarray=flag.split(","); list selvallist = new arraylist(); selvallist.add("序号#"); for(int i=0;i<sourcestrarray.length;i++){ selvallist.add(sourcestrarray[i]+"#"); } writableworkbook book = null; if (indexedcollection == null || indexedcollection.size()<0) {%> <script language="javascript"> alert('对不起!当前条件没有数据，不能导出!'); //history.go(-1); </script> <% } else { response.reset(); response.setcontenttype("charset=utf-8"); response.setcontenttype("application/vnd.ms-excel"); string filename = "regularinform.xls"; response.setheader("content-disposition","attachment; filename=" + filename); //解决https不能下载的问题 response.setheader("cache-control","public"); book = workbook.createworkbook(response.getoutputstream()); //表头字体 writablecellformat writablecellformat = new writablecellformat(); writablecellformat.setalignment(alignment.centre); writablecellformat.setverticalalignment(verticalalignment.centre); writablecellformat.setwrap(true); writablefont cellfont = new writablefont(writablefont.createfont("宋体"), 14, writablefont.bold, false, underlinestyle.no_underline, colour.black); writablecellformat.setfont(cellfont); writablecellformat.setborder(border.all, borderlinestyle.thin); writablecellformat.setbackground(colour.sky_blue, pattern.solid); //标题字体（左对齐） writablecellformat writablecellformat1 = new writablecellformat(); writablecellformat1.setalignment(alignment.centre); writablecellformat1.setverticalalignment(verticalalignment.centre); writablecellformat1.setwrap(false); writablefont cellfont1 = new writablefont(writablefont.createfont("宋体"), 14, writablefont.no_bold, false, underlinestyle.no_underline, colour.black); writablecellformat1.setfont(cellfont1); //数据字体（居中） writablecellformat writablecellformat2 = new writablecellformat(); writablecellformat2.setalignment(alignment.centre); writablecellformat2.setverticalalignment(verticalalignment.centre); writablecellformat2.setwrap(true); writablefont cellfont2 = new writablefont(writablefont.createfont("宋体"), 10, writablefont.no_bold, false, underlinestyle.no_underline, colour.black); writablecellformat2.setfont(cellfont2); writablecellformat2.setborder(border.all,borderlinestyle.thin); writablesheet sheet = book.createsheet("sheet1", 0); sheet = setsheetheader(sheet, writablecellformat, selvallist); int startrow = 2; label label1 = null; int m = 0; sheet.mergecells(0, 0, selvallist.size()-1, 0); if(indexedcollection!=null && indexedcollection.size()>0) { for (int i = 0; i < indexedcollection.size(); i++){ keyedcollection keyedcollection = (keyedcollection) indexedcollection.get(i); string trandate=(string)keyedcollection.getdatavalue("trandate");//交易日期 string tt = (string)keyedcollection.getdatavalue("trantime"); tt = tt.substring(0,2)+":"+tt.substring(2,4);//交易时间 string narr=utb.nvl(keyedcollection.getdatavalue("narr"), "--");//摘要 string entramt1=utb.formatmoney("debit".equals((string)(keyedcollection.getdatavalue("dcflag")))?((string)keyedcollection.getdatavalue("entramt")):"0.00");//借方发生额 string entramt2=utb.formatmoney("debit".equals((string)(keyedcollection.getdatavalue("dcflag")))?"0.00":((string)keyedcollection.getdatavalue("entramt")));//贷方发生额 string oppac=utb.nvl(keyedcollection.getdatavalue("oppac"), "--");//对方账号 string oppacname=utb.nvl(keyedcollection.getdatavalue("oppacname"), "--");//对方户名 string transcardno=utb.nvl(keyedcollection.getdatavalue("transcardno"), "--");//单位卡号 string stdflag=(string)context.getdatavalue("stdflag"); string cardno=(string)context.getdatavalue("parentcardno"); string mainflag = "附";if(!"parent".equals(stdflag)){}else{mainflag = cardno.equals((string)(keyedcollection.getdatavalue("transcardno")))?"主":"附";}//主卡标识 string usage=utb.nvl(keyedcollection.getdatavalue("usage"), "--");//用途 string remark=utb.nvl(keyedcollection.getdatavalue("remark"), "--");//备注 int k = 0; sheet.setrowview(startrow, 450); label1 = new label(k, startrow, (startrow-1)+"", writablecellformat2); sheet.addcell(label1); k++; //(列，行，值，格式) label1 = new label(k, startrow,trandate, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,tt, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,narr, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,entramt1, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,entramt2, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,oppac, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,oppacname, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,transcardno, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,mainflag, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,usage, writablecellformat2); sheet.addcell(label1); k++; label1 = new label(k, startrow,remark, writablecellformat2); sheet.addcell(label1); k++; startrow = startrow+1; } } book.write(); try { book.close(); response.flushbuffer(); } catch (exception e) { system.out.println("excel is not exist!"); } out = pagecontext.pushbody(); } %><%!private static writablesheet setsheetheader(writablesheet sheet,writablecellformat writablecellformat, list selvallist) throws exception { int i = 0; int j = 1; label label = new label(i, j, "序号", writablecellformat); sheet.addcell(label); sheet.setcolumnview(i, "序号".length() * 4); for(int w =1; w < selvallist.size(); w++) { label = new label(w, j, ((string)selvallist.get(w)).split("#")[0], writablecellformat); sheet.addcell(label); sheet.setcolumnview(w, ((string)selvallist.get(w)).split("#")[0].length() * 5); } return sheet; } %>