Java+jxls using Excel templates for export

Most of the time there will be the ability to export Excel, the use of POI can be used to achieve a simple export, you can say that the POI is very powerful to achieve the details of the custom operation, but compared to excel in office operations, the use of POI fully generated Excel will be very complex, the details of the code will be particularly large. At this time we can use Offfice to make a template file, in the use of Jxls to the template file to achieve the export of complex excel.

Template production and general Excel writing is the same, in the need to fill the grid using {} to remove the value of the variable passed in Java,

In Java:

InputStream is =getfileinputstream (templatefilename); // get template input stream New  =transformer.transformmultiplesheetslist (is,prints/* list to print* /
Sheetnames/*sheet Name list*/, "print"/*/Excel's bean name * / New HashMap (), 0);

In Excel, the list of Jxls will be printed by the name of the bean that we define to traverse, in the source code

 PublicHssfworkbook Transformmultiplesheetslist (InputStream is, list objects, List newsheetnames, String beanname, Map Beanparams,intStartsheetnum)throwsparsepropertyexception {hssfworkbook Hssfworkbook=NULL; Try {            if(Beanparams! =NULL&&( Map) beanparams). ContainsKey (Beanname)) {                Throw NewIllegalArgumentException ("Selected bean name '" + beanname + "' already exists in the bean map"); }            if(Beanname = =NULL) {                Throw NewIllegalArgumentException ("Bean name must not is null"); }            if(Beanparams = =NULL) {Beanparams=NewHashMap (); } Poifsfilesystem FS=NewPoifsfilesystem (IS); Hssfworkbook=NewHssfworkbook (FS);  for(intSheetno = 0; Sheetno < Hssfworkbook.getnumberofsheets (); ++Sheetno) {String Spreadsheetname=Hssfworkbook.getsheetname (SHEETNO); if(! This. Isspreadsheettoremove (Spreadsheetname)) {                    if( This. Isspreadsheettorename (Spreadsheetname)) {Hssfworkbook.setsheetname (Sheetno, This. Getspreadsheettorename (Spreadsheetname)); } hssfsheet Hssfsheet=Hssfworkbook.getsheetat (SHEETNO); if(Startsheetnum = = Sheetno && Objects! =NULL&&!Objects.isempty ()) {                         for(inti = 0; I < objects.size (); ++i) {Object bean=Objects.get (i); String Beankey=Beanname; if(I! = 0) {Beankey= Beanname +i; Hssfsheet NewSheet=Hssfworkbook.createsheet (String) newsheetnames.get (i));                                Util.copysheets (NewSheet, Hssfsheet, Beanname, Beankey);                                Util.copypagesetup (NewSheet, Hssfsheet);                            Util.copyprintsetup (NewSheet, Hssfsheet); } Else{hssfworkbook.setsheetname (Sheetno, (String) newsheetnames.get (i)); } ((Map) beanparams). Put (Beankey, Bean);//in this time, we put our incoming list in the form of a map into Beanparams                        }                    }                } Else{hssfworkbook.removesheetat (SHEETNO); --Sheetno; }            }        } Catch(IOException var16) {var16.printstacktrace (); }        if(Hssfworkbook! =NULL) {             for(inti = 0; I < hssfworkbook.getnumberofsheets (); ++i) {Util.setprintarea (Hssfworkbook, i); }        }         This. Transformworkbook (Hssfworkbook, (MAP) beanparams);//producing Excel from Templates and Beanparams        returnHssfworkbook; }

View Code

In Excel, the values are evaluated in Beanparams's map form, somewhat similar to the Freemarker syntax, but unlike the Freemarker

In this case, I defined a class (domain) to hold the list, as follows (that is, the element of the list to be printed is the following class)

 Public classExcelprintdomainImplementsSerializable {PrivateList lista =NewArrayList (); PrivateObject DomainA; PrivateServletContext context; Private intPageNo;  PublicExcelprintdomain () {} PublicExcelprintdomain (ServletContext context) { This. Context =context; }     Public voidadddomain (Object obj) { This. Domains.add (obj); }     Public voidAddlista (Object obj) { This. Lista.add (obj); }     Public intGetpageno () {return  This. PageNo; }     Public voidSetpageno (intPageNo) {         This. PageNo =PageNo; }     PublicList Getlista () {return  This. Lista; }     Public voidSetlista (List lista) { This. Lista =lista; }     PublicObject Getdomaina () {return  This. DomainA; }     Public voidSetdomaina (Object domaina) { This. DomainA =DomainA; }}

View Code

So in the template ${print.domaina.orgname} is to take out the domain inside the DomainA inside the key is the value of OrgName,

${PRINT.LISTA.EC01002} represents the value of key ec01002 in the Lista contained in the map that is taken out of the traversal.

Java+jxls using Excel templates for export