POI import and export and dynamic complex header generation

Prior to opening the POI classification already existed three articles introduced the use of POI and the flow of the download, the recent project found before the use of defects, today to do a summary, from now on we can ignore the first three articles on the use of POI, but the flow of the load mode can be referred to, here or again to mention a pen, Streaming download using Ajax will be out of the ordinary, do not take effect, I was to submit form form the way to carry out the stream load, unfortunately, temporarily not get the return value

Let's move on to the use of POI

The first thing to emphasize is that POI is treated differently for Excel2003 and excel2007+, using Hssfworkbook and Xssfworkbook respectively, but they implement the same interface workbook, which provides some flexibility for our programming implementation.

Recommendation: For export, we directly use Xssfworkbook can, Hssfworkbook is a certain flaw, before the use of Hssfworkbook, the amount of data reached 65536 will be error export failure, recently do new projects to find the cause

Relative to the import work, need to dynamically identify the file Excel2003 or excel2007+, some articles written by the suffix name to identify, there is a problem, the user modified the suffix, the result is conceivable, fortunately, POI provides a convenient way to workbook create ( InputStream INP), created dynamically through the input stream, good ... Below, I will also provide the relevant usage. Here also to give a hint, using the 3.9 version of this method error, here, I would like to provide a Maven info

			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactid>poi</artifactid >
				<version>3.15</version>
			</dependency>
			<dependency>
				<groupid >org.apache.poi</groupId>
				<artifactId>poi-ooxml</artifactId>
				<version>3.15 </version>
			</dependency>
			<dependency>
				<groupid>org.apache.poi</ groupid>
				<artifactId>poi-ooxml-schemas</artifactId>
				<version>3.15</version >
			</dependency>

Yes, these three packages are needed, and that's exactly what I'm going to do to encapsulate a POI toolkit.

Import org.apache.poi.hssf.usermodel.*;
Import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
Import org.apache.poi.ss.usermodel.*;
Import org.apache.poi.ss.util.CellRangeAddress;
Import Org.apache.poi.ss.util.NumberToTextConverter;

Import Org.apache.poi.xssf.usermodel.XSSFWorkbook;
Import Java.io.FileOutputStream;
Import java.io.IOException;
Import Java.io.InputStream;
Import Java.text.DateFormat;
Import Java.text.SimpleDateFormat;

Import java.util.*; public class Poiutil {private Poiutil () {}/** * Excel2003 and excel2007+ are created differently * Excel2003 use Hssfwork 
    Book suffix xls * excel2007+ use xssfworkbook suffix xlsx * This method guarantees dynamic creation of workbook * @param is * @return */ public static Workbook Createworkbook (InputStream is) throws IOException, Invalidformatexception {return Wor
    Kbookfactory.create (IS); /** * Import data get data list * @param WB * @return */public static list<list<object>> GETDA Talist (WorkBook WB) {list<list<object>> rowlist = new arraylist<> (); for (int sheetnum = 0; Sheetnum < wb.getnumberofsheets (); sheetnum++) {Sheet Sheet = Wb.getsheetat (sheetnu
            m);
                for (int i = Sheet.getfirstrownum (); I <= sheet.getlastrownum (); i++) {Row row = Sheet.getrow (i);
                if (null = = row) continue;
                list<object> celllist = new arraylist<> ();
                    for (int j = Row.getfirstcellnum (); J < Row.getlastcellnum (); j + +) {cell cell = Row.getcell (j);
                Celllist.add (Getcellvalue (cell));
            } rowlist.add (Celllist);
    }} return rowlist;
        } private static String Getcellvalue (cell cell) {string cellvalue = ""; if (cell = null) {//Determine the current cell's type switch (Cell.getcelltype ()) {//If the current cell's Typ E isNUMERIC Case hssfcell.cell_type_numeric: {Short format = Cell.getcellstyle (). Getdataf
                    Ormat (); if (format = = | | | format = = | | | format = = | | | format = + +) {//excel in time formats Simpledateform
                        At SDF = new SimpleDateFormat ("Yyyy-mm-dd");
                        Double value = Cell.getnumericcellvalue ();
                        Date date = Dateutil.getjavadate (value);
                    Cellvalue = Sdf.format (date); }//Determine if the current cell is a date else if (hssfdateutil.iscelldateformatted (cell)) {//Note the date class first
                        The hssfdateutil.iscelldateformatted (cell) was found to recognize only 2014/02/02 of this format in real-world tests. If it is a date type, the date value of the cell is obtained//the 2014-02-02 format is not recognized as the DateTime format of the day date = Cell.getdatecellvalue (
                        );
                        DateFormat formater = new SimpleDateFormat ("Yyyy-mm-dd"); Cellvalue = Formater.format (dATE); } else {//if it is a pure number//Get the value of the current cell Cellvalue = Numbertotextconverter.totext

                    (Cell.getnumericcellvalue ());
                } break; }//If the TYPE of the current cell is Strin case hssfcell.cell_type_string://Get the current cell word
                    Character string cellvalue = Cell.getstringcellvalue (). ReplaceAll ("'", "" ");
                Break
                    Case HSSFCell.CELL_TYPE_BLANK:cellvalue = null;
                Break
                Default Cell Value: {cellvalue = "";
        }}}} else {cellvalue = "";
    } return cellvalue; }/** * This method generates a table header and writes the header name * * @param nodes node * @param sheet Workbook * @param style cell style * @r Eturn Data Load Start line */public static int Generateheader (LIST&LT;HEADERNODE&GT;
        Nodes, Sheet Sheet, CellStyle style) {map<rowkey, row> hssfrowmap = new hashmap<> ();
        int datastartrow = 0; for (Headernode node:nodes) {if (!) ( Node.firstrow = = Node.getlastcol () | | Node.getfirstcol () = = Node.getlastcol ()) {cellrangeaddress CRA = new Cellrangeaddress (Node.getfirstrow ()
                , Node.getlastrow (), Node.getfirstcol (), Node.getlastcol ());
            Sheet.addmergedregion (CRA); } Datastartrow = Datastartrow >= node.getlastrow ()?
            DataStartRow:node.getLastRow ();
            RowKey key = new RowKey ();
            Key.setfirstrow (Node.getfirstrow ());
            Key.setlastrow (Node.getlastrow ());
            Row row = Hssfrowmap.get (key);
                if (null = = row) {row = Sheet.createrow (Node.getfirstrow ());
            Hssfrowmap.put (key, Row);
            Cell cell = Row.createcell (Node.getfirstcol ()); Cell.Setcellvalue (Node.getname ());
        Cell.setcellstyle (style);
    } return datastartrow+1;
        } public static class Headernode {private String name;
        private int firstrow;
        private int lastrow;
        private int firstcol;

        private int lastcol;
        Public String GetName () {return name;
        } public void SetName (String name) {this.name = name;
        } public int Getfirstrow () {return firstrow;
        } public void Setfirstrow (int firstrow) {this.firstrow = FirstRow;
        } public int Getlastrow () {return lastrow;
        } public void Setlastrow (int lastrow) {this.lastrow = LastRow;
        } public int Getfirstcol () {return firstcol;
        } public void Setfirstcol (int firstcol) {this.firstcol = Firstcol;
 } public int Getlastcol () {           return lastcol;
        } public void Setlastcol (int lastcol) {this.lastcol = lastcol;
        }} private static class RowKey {private int firstrow;

        private int lastrow;
        public int Getfirstrow () {return firstrow;
        } public void Setfirstrow (int firstrow) {this.firstrow = FirstRow;
        } public int Getlastrow () {return lastrow;
        } public void Setlastrow (int lastrow) {this.lastrow = LastRow;
            } @Override public boolean equals (Object o) {if (this = = O) return true; if (! (
            o instanceof RowKey)) return false;
            RowKey key = (RowKey) o;
        return firstrow = = Key.firstrow && lastrow = = Key.lastrow;
        } @Override public int hashcode () {return Objects.hash (firstrow, lastrow); }} publicstatic void Main (string[] args) {//First step, create a webbook, corresponding to an Excel file Workbook Workbook = new Xssfworkbook ();
        In the second step, add a Sheet in WebBook, corresponding to the Sheet Sheet Sheet = Workbook.createsheet ("Test") in the Excel file;
        Fourth step, create the cell, and set the value header to set the table header centered CellStyle style = Workbook.createcellstyle (); Style.setalignment (HorizontalAlignment.Center); Horizontal Center Format style.setverticalalignment (verticalalignment.center);
        Vertically centered list 

The following is the exported report and the flow of the code downloaded

       list<poiutil.headernode> nodes = new arraylist<> ();
            for (Rpstyleitem item:styleitems) {Poiutil.headernode Headernode = new Poiutil.headernode ();
            Headernode.setfirstrow (Item.getrow ()-1);
            Headernode.setlastrow (Headernode.getfirstrow () +item.getsizey ()-1);
            Headernode.setfirstcol (Item.getcol ()-1);
            Headernode.setlastcol (Headernode.getfirstcol () + Item.getsizex ()-1);
            Headernode.setname (Item.getname ());
        Nodes.Add (Headernode); }//The first step, create a webbook, corresponding to an Excel file//Xssfworkbook for excel2007+//Hssfworkbook for Excel2003 (data over 6553
        6 will error) Workbook Workbook = new Xssfworkbook ();
        In the second step, add a Sheet in WebBook, corresponding to the Sheet Sheet Sheet = Workbook.createsheet (Template.getname ()) in the Excel file;
        The third step is to create a cell style CellStyle style = Workbook.createcellstyle (); Style.setalignment (HorizontalAlignment.Center); Horizontal Center Format STYLE.SEtverticalalignment (Verticalalignment.center);
        vertically centered int rowNum = Poiutil.generateheader (Nodes,sheet,style);
        String sql = Template.getreportsql (). replace ("\ n", "");
        String reportdate = Com.jrq.components.date.DateUtil.dateFormat (new date (), "yyyyMMdd");
        Test data String reportdate = "20170711";
        list<map<string, object>> datas = Reportservice.listreportdata (reportdate, SQL);
            For (map<string, object> map:datas) {collection<object> values = map.values (); int index = 0;
            Cell cells index Row row = Sheet.createrow (rownum++);
            for (Object obj:values) {Row.createcell (index++). Setcellvalue (Obj.tostring ()); }/* Directly save the file locally for test style use try {outputstream output=new fileoutputstream ("E:\\workbook.
            XLS ");
            Workbook.write (output);
            Output.flush ();
        Output.close (); } catch (IOExceptione) {e.printstacktrace ();
        }*/string downfilename = new String (Template.gettype (). GetName () +reportdate+ ". xls");
        try {//If not coded in IE will be garbled downfilename = Urlencoder.encode (Downfilename, "UTF-8");
        } catch (Unsupportedencodingexception e) {e.printstacktrace ();
            } try {//Empty response response.reset (); Response.setcontenttype ("Application/msexcel");//Set the generated file type response.setcharacterencoding ("UTF-8");//Set the file header encoding method and FileName Response.setheader ("content-disposition", "attachment;
            Filename= "+ downfilename);
            OutputStream Os=response.getoutputstream ();
            Workbook.write (OS);
            Os.flush ();
        Os.close ();
            } catch (IOException e) {log.error ("file stream operation exception");
            Jr.setret ("File stream operation exception");
        Return JR;
        } jr.setsuc (jsonresponse.success); Return JR;
 

The following is the code for the import function

        Multiparthttpservletrequest multipartrequest = (multiparthttpservletrequest) request;
        Multipartfile file = Multipartrequest.getfile ("FileUpload");
        if (!file.isempty ()) {
            Workbook Workbook = Poiutil.createworkbook (File.getinputstream ());
            if (null! = Workbook) {
                list<list<object>> List = poiutil.getdatalist (workbook);}}
Can see here is the front-end uploaded Excel file here, the front-end HTML is also posted

   <form method= "POST" enctype= "Multipart/form-data" id= "Form1"
                                  action= "/center/addition/list/uploadfile" >
                                <input class= "Typeahead scrollable" type= "file" id= "FileUpload"
                                       name= "FileUpload"/>
                                <input Type= "Submit" value= "Upload" id= "Btn_submit"/>
                            </form>