首页 / 网页编程 / JSP / 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; }%>