1, the required jar
Jxl-2.6.10.jar
Jxls-core-1.0-rc-3.jar
Jxls-reader-1.0-rc-3.jar
2. Excel Modify line width encapsulation
Sheetcolumn.java
Package Com.tp.soft.common.excel; Import Jxl.write.WritableSheet; /** @author*/Publicinterface sheetcolumn { Public Abstract void Setcolumnview (writablesheet sheet);}
3. Excel generates encapsulation
Excelexportutils.java
PackageCom.tp.soft.common.excel;Importjava.util.List;ImportJava.util.Map;Importjavax.servlet.http.HttpServletRequest;ImportJavax.servlet.http.HttpServletResponse;ImportJXL. Cellview;ImportJXL. Workbook;Importjxl.format.Alignment;Importjxl.format.VerticalAlignment;ImportJxl.write.Label;ImportJxl.write.WritableCell;ImportJxl.write.WritableCellFormat;ImportJxl.write.WritableFont;ImportJxl.write.WritableSheet;ImportJxl.write.WritableWorkbook;Importcom.sun.xml.internal.messaging.saaj.packaging.mime.internet.MimeUtility;/****************************************************************************** * @Package: [ Com.tp.soft.common.excel.java] * @ClassName: [xmlexportutis] * @Description: [dynamically generate XML] * @Author: [TAOP] * @CreateDate: [2014-3-31 2:32:51] * @UpdateUser: [TAOP (such as multiple changes to the retention history, add changes to record)] * @UpdateDate: [2014-3-31 Noon 2:32:51, (such as multiple changes in retention history, increase the record of changes)] * @UpdateRemark: [Description of this modification, (such as multiple changes to the retention history, increase the record of changes)] * @Version: [v1.0]*/ Public classExcelexportutils {/*** * @Title: DownLoad * @Description: TODO (Export Excel) *@paramFileName Export File name *@paramTitles Excel title *@paramdataList Excel Data *@paramRequest *@paramResponse *@paramsheetcolumn void Setting cell width information *@throws */ Public Static voiddownLoad (String fileName, string[] titles, Map<String,List<List<String>>>data, HttpServletRequest request, httpservletresponse response, Sheetcolumn sheetcolumn) { //Create an ExcelWritableworkbook Excel =NULL ; Try { //set type to downloadResponse.setcontenttype ("Application/x-msdownload"); //Get Browser InformationString useragent = Request.getheader ("User-agent"). toLowerCase (); String RTN= "Filename=" +filename+ ". xls" ; if(Useragent.indexof ("MSIE")! =-1) {//IE BrowserRTN = "Filename=" + java.net.URLEncoder.encode (filename, "UTF-8") + ". xls" ; }Else if(Useragent.indexof ("opera")! =-1) {//Opera browser can only use filename*RTN = "Filename*=utf-8" "+ filename+". xls "; }Else if(Useragent.indexof ("Safari")! =-1) {//Safari BrowserRTN = "Filename=" +NewString (Filename.getbytes ("UTF-8"), "iso8859-1") + ". xls" ; }Else if(Useragent.indexof ("AppleWebKit")! =-1) {//Chrome Browserfilename = mimeutility.encodetext (filename, "UTF8", "B"); RTN= "filename=\" "+ filename +". Xls\ ""; }Else if(Useragent.indexof ("Mozilla")! =-1) {//Firefox BrowserRTN = "Filename=" +NewString (Filename.getbytes ("UTF-8"), "iso-8859-1") + ". xls"; } //set Download file nameResponse.setheader ("Content-disposition", "attachment;") +RTN); //Set window Popup typeResponse.setheader ("Windows-target", "_blank"); Excel=Workbook.createworkbook (Response.getoutputstream ()); Writablefont Font=NewWritablefont (writablefont.times,14, Writablefont.bold); Writablecellformat format=NewWritablecellformat (); //Set Text Centerformat.setalignment (alignment.centre); //set Text to center verticallyformat.setverticalalignment (verticalalignment.centre); //set up line wrappingFormat.setwrap (true); Writablecellformat Formatt=NewWritablecellformat (font); //Set Text Centerformatt.setalignment (alignment.centre); //set Text to center verticallyformatt.setverticalalignment (verticalalignment.centre); //set up line wrappingFormatt.setwrap (true); intK = 0 ; for(Map.entry<string, list<list<string>>>Entry:data.entrySet ()) { //Create a work spaceWritablesheet sheet = excel.createsheet (Entry.getkey (), k++); //Set Width if(sheetcolumn!=NULL) {Sheetcolumn.setcolumnview (sheet); }Else{ //Set Automatic sizeCellview Cellview =NewCellview (); Cellview.setautosize (true); for(inti= 0; I<titles.length; i++) {Sheet.setcolumnview (I, Cellview); }} List<List<String>> dataList =Entry.getvalue (); /** Add title*/ for(inti= 0; I<titles.length; i++) {Writablecell cell=NewLabel (i, 0, Titles[i],formatt); Sheet.addcell (cell); } /** Add Content*/ for(intj=0; J<datalist.size (); J + +) {List<String> contents = Datalist.get (j);//a piece of data for(inti=0; I<contents.size (); i++) {Writablecell cell=NewLabel (i, j+1, Contents.get (i), format); Sheet.addcell (cell); }}} excel.write (); } Catch(Exception e) {e.printstacktrace (); }finally{ Try{ if(excel!=NULL) Excel.close (); Excel=NULL ; }Catch(Exception e) {}}} }
4. Read the database data and call generate Excel download
/*** Export winning records **/@RequestMapping ("/dowinexcel") PublicModelandview Download (Buwinuser buwinuser,intZid, httpservletresponse response)throwsException {List<BuWinUser> winlist =winusersvc. Findwinlistbynopage (Buwinuser, Zid); Map<string, list<list<string>>> data =NewHashmap<string, list<list<string>>>(); Data.put ("award-winning user Information", Beantoarray (winlist)); String[] Titles= {"Number", "status", "Redemption Code", "OpenID", "Prize", "nickname", "Contact phone", "Gender", "Winning Time", "Awards Time" }; Excelexportutils.download ("award-winning user Information", titles, data, request, response,NewSheetcolumn () {@Override Public voidSetcolumnview (writablesheet sheet) {Sheet.setcolumnview (0, 15); Sheet.setcolumnview (1, 20); Sheet.setcolumnview (2, 20); Sheet.setcolumnview (3, 40); Sheet.setcolumnview (4, 20); Sheet.setcolumnview (5, 20); Sheet.setcolumnview (6, 30); Sheet.setcolumnview (7, 30); Sheet.setcolumnview (8, 20); Sheet.setcolumnview (9, 20); } }); return NULL; } PrivateList<list<string>> Beantoarray (list<buwinuser>data) {List<List<String>> dataList =NewArraylist<list<string>>(); for(Buwinuser winuser:data) {List<String> list =NewArraylist<string>(); List.add (Winuser.getwid ()+ ""); List.add (Winuser.getissend_name ()); List.add (Winuser.getsn_num ()); List.add (Winuser.getopenid ()); List.add (Winuser.getplevel_name ()); List.add (Winuser.getnick_name ()); List.add (Winuser.gettel ()); List.add (Winuser.getsex ()); List.add (Dateutil.timestamptostr (Winuser.getgmt_create (),"Yyyy-mm-dd HH:mm:ss")); List.add (Dateutil.timestamptostr (Winuser.getsend_time (),"Yyyy-mm-dd HH:mm:ss")); Datalist.add (list); } returndataList; }
JXL read database data to generate XLS and download