JXL read database data to generate XLS and download

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