JSP导出Excel文件的方法
本文实例讲述了JSP导出Excel文件的方法。分享给大家供大家参考,具体如下:
<%@pageimport="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"%> <%@pagelanguage="java"contentType="text/html;charset=UTF-8"%> <%@tagliburi="/WEB-INF/CTP.tld"prefix="ctp"%> <jsp:directive.pageimport="java.util.List"/> <jsp:directive.pageimport="java.util.ArrayList"/> <%@includefile="http://www.xxxx.com/head.jsp"%> <% Contextcontext=(Context)request.getAttribute("context");Stringflag="交易日期,交易时间,摘要,借方发生额,贷方发生额,对方账号,对方户名,单位卡号,主卡标识,用途,备注"; IndexedCollectionindexedCollection=(IndexedCollection)context.getDataElement("parentCardDetails"); String[]sourceStrArray=flag.split(","); ListselValList=newArrayList(); selValList.add("序号#"); for(inti=0;i<sourceStrArray.length;i++){ selValList.add(sourceStrArray[i]+"#"); } WritableWorkbookbook=null; if(indexedCollection==null||indexedCollection.size()<0) {%> <scriptlanguage="javascript"> alert('对不起!当前条件没有数据,不能导出!'); //history.go(-1); </script> <% } else { response.reset(); response.setContentType("charset=UTF-8"); response.setContentType("application/vnd.ms-excel"); Stringfilename="regularInform.xls"; response.setHeader("Content-Disposition","attachment;filename="+filename); //解决HTTPS不能下载的问题 response.setHeader("Cache-Control","public"); book=Workbook.createWorkbook(response.getOutputStream()); //表头字体 WritableCellFormatwritableCellFormat=newWritableCellFormat(); writableCellFormat.setAlignment(Alignment.CENTRE); writableCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); writableCellFormat.setWrap(true); WritableFontcellFont=newWritableFont(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); //标题字体(左对齐) WritableCellFormatwritableCellFormat1=newWritableCellFormat(); writableCellFormat1.setAlignment(Alignment.CENTRE); writableCellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE); writableCellFormat1.setWrap(false); WritableFontcellFont1=newWritableFont(WritableFont.createFont("宋体"),14,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); writableCellFormat1.setFont(cellFont1); //数据字体(居中) WritableCellFormatwritableCellFormat2=newWritableCellFormat(); writableCellFormat2.setAlignment(Alignment.CENTRE); writableCellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE); writableCellFormat2.setWrap(true); WritableFontcellFont2=newWritableFont(WritableFont.createFont("宋体"),10,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); writableCellFormat2.setFont(cellFont2); writableCellFormat2.setBorder(Border.ALL,BorderLineStyle.THIN); WritableSheetsheet=book.createSheet("Sheet1",0); sheet=setSheetHeader(sheet,writableCellFormat,selValList); intstartRow=2; Labellabel1=null; intm=0; sheet.mergeCells(0,0,selValList.size()-1,0); if(indexedCollection!=null&&indexedCollection.size()>0) { for(inti=0;i<indexedCollection.size();i++){ KeyedCollectionkeyedCollection=(KeyedCollection)indexedCollection.get(i); StringTranDate=(String)keyedCollection.getDataValue("TranDate");//交易日期 Stringtt=(String)keyedCollection.getDataValue("TranTime"); tt=tt.substring(0,2)+":"+tt.substring(2,4);//交易时间 Stringnarr=utb.nvl(keyedCollection.getDataValue("narr"),"--");//摘要 StringentrAmt1=utb.formatMoney("DEBIT".equals((String)(keyedCollection.getDataValue("DCFlag")))?((String)keyedCollection.getDataValue("entrAmt")):"0.00");//借方发生额 StringentrAmt2=utb.formatMoney("DEBIT".equals((String)(keyedCollection.getDataValue("DCFlag")))?"0.00":((String)keyedCollection.getDataValue("entrAmt")));//贷方发生额 StringOppAc=utb.nvl(keyedCollection.getDataValue("OppAc"),"--");//对方账号 StringOppacName=utb.nvl(keyedCollection.getDataValue("OppacName"),"--");//对方户名 StringTransCardNo=utb.nvl(keyedCollection.getDataValue("TransCardNo"),"--");//单位卡号 Stringstdflag=(String)context.getDataValue("stdflag"); StringcardNo=(String)context.getDataValue("parentCardNo"); Stringmainflag="附";if(!"parent".equals(stdflag)){}else{mainflag=cardNo.equals((String)(keyedCollection.getDataValue("TransCardNo")))?"主":"附";}//主卡标识 StringUsage=utb.nvl(keyedCollection.getDataValue("Usage"),"--");//用途 StringReMark=utb.nvl(keyedCollection.getDataValue("ReMark"),"--");//备注 intk=0; sheet.setRowView(startRow,450); label1=newLabel(k,startRow,(startRow-1)+"",writableCellFormat2); sheet.addCell(label1); k++; //(列,行,值,格式) label1=newLabel(k,startRow,TranDate,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,tt,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,narr,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,entrAmt1,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,entrAmt2,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,OppAc,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,OppacName,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,TransCardNo,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,mainflag,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,Usage,writableCellFormat2); sheet.addCell(label1); k++; label1=newLabel(k,startRow,ReMark,writableCellFormat2); sheet.addCell(label1); k++; startRow=startRow+1; } } book.write(); try{ book.close(); response.flushBuffer(); }catch(Exceptione){ System.out.println("Excelisnotexist!"); } out=pageContext.pushBody(); } %><%!privatestaticWritableSheetsetSheetHeader(WritableSheetsheet,WritableCellFormatwritableCellFormat,ListselValList)throwsException { inti=0; intj=1; Labellabel=newLabel(i,j,"序号",writableCellFormat); sheet.addCell(label); sheet.setColumnView(i,"序号".length()*4); for(intw=1;w<selValList.size();w++) { label=newLabel(w,j,((String)selValList.get(w)).split("#")[0],writableCellFormat); sheet.addCell(label); sheet.setColumnView(w,((String)selValList.get(w)).split("#")[0].length()*5); } returnsheet; } %>
希望本文所述对大家JSP程序设计有所帮助。