JSP: How to export an Excel file. jsp: How to export an excel file
This document describes how to export an Excel File Using JSP. We will share this with you for your reference. The details are as follows:
<% @ 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 = "transaction date, transaction time, summary, debit amount, credit amount, account of the other party, account name of the other party, organization card number, primary card ID, usage, note "; IndexedCollection indexedCollection = (IndexedCollection) context. getDataElement ("parentCardDetails"); String [] sourceStrArray = flag. split (","); List selValList = new ArrayList (); selValList. add ("Serial Number #"); for (int I = 0; I <sourceStrArray. length; I ++) {selValList. add (sourceStrA Rray [I] + "#");} WritableWorkbook book = null; if (indexedCollection = null | indexedCollection. size () <0) {%> <script language = "javascript"> alert ('Sorry! The current condition has no data and cannot be exported! '); // 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); // solve the problem that HTTPS cannot be downloaded. response. setHeader ("Cache-Control", "public"); book = Workbook. createWorkbook (response. getOutputStream (); // Header WritableCellFormat writableCellFormat = new WritableCellFormat (); writableCellFormat. setAlignment (Alignment. CENTRE); writableCellFormat. setVerticalAlignment (verticalignment. CENTRE); writableCellFormat. setWrap (true); WritableFont cellFont = new WritableFont (WritableFont. createFont (""), 14, WritableFont. BOLD, false, UnderlineStyle. NO_UNDERLINE, Colour. BLACK); writableCellFormat. setFont (cellFo Nt); writableCellFormat. setBorder (Border. ALL, BorderLineStyle. THIN); writableCellFormat. setBackground (Colour. SKY_BLUE, Pattern. SOLID); // title Font (left aligned) WritableCellFormat writableCellFormat1 = new WritableCellFormat (); writableCellFormat1.setAlignment (Alignment. CENTRE); writablecellformat1.setverticalignment (verticalignment. CENTRE); writableCellFormat1.setWrap (false); WritableFont cellFont1 = new W RitableFont (WritableFont. createFont (""), 14, WritableFont. NO_BOLD, false, UnderlineStyle. NO_UNDERLINE, Colour. BLACK); writableCellFormat1.setFont (cellFont1); // data font (center) WritableCellFormat writableCellFormat2 = new WritableCellFormat (); writableCellFormat2.setAlignment (Alignment. CENTRE); writablecellformat2.setverticalignment (verticalignment. CENTRE); writableCellFormat2.setWrap (true); Writabl EFont 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"); // Transaction date String tt = (String) keyedCollection. getDataValue ("TranTime"); tt = tt. substring (0, 2) + ":" + tt. substring (2, 4); // transaction time String narr = utb. nvl (keyedCollection. getDataVal Ue ("narr"), "--"); // abstract 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"); // credit occurrence String OppAc = utb. nvl (keyedCollection. getDataValue ("OppAc"), "--"); // String OppacName = utb. nvl (keyedCollection. getDataValue ("OppacName"), "--"); // recipient's account name String TransCardNo = utb. nvl (keyedCollection. getDataValue ("TransCardNo"), "--"); // unit card number String stdflag = (String) context. getDataValue ("stdflag"); String cardNo = (String) context. getDataVal Ue ("parentCardNo"); String mainflag = ""; if (! "Parent". equals (stdflag) {} else {mainflag = cardNo. equals (String) (keyedCollection. getDataValue ("TransCardNo ")))? "Main": "Appendix";} // main card ID String Usage = utb. nvl (keyedCollection. getDataValue ("Usage"), "--"); // 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 ++; // (column, row, value, format) 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, wr ItableCellFormat2); 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 (l Abel1); 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, "No.", writableCellFormat); sheet. addCell (label); sheet. setColumnView (I, "No ". 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;} %>
I hope this article will help you with JSP program design.