JXL read database data to generate XLS and download

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.