POI implements Big Data excle import and export to resolve memory overflow problems

One important reason to use POI to export big data to ensure that memory does not overflow is that Sxssfworkbook generates 2007 versions of Excel, modifies the EXCEL2007 file suffix to zip open to see that each sheet is an XML file, Cell formatting and cell coordinates are represented by labels. Direct use of Sxssfworkbook to export Excel itself is a POI for large data volume export and tailored, so the export can be directly used Sxssfworkbook way.

For the sake of insurance, you can use a multi-sheet way to ensure that memory does not overflow. It is important to note that the sheet name cannot be duplicated, and you need to define a return header when downloading.

Response.setcontenttype ("Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

　Exporting Excel is simpler, creating workbook objects and sheet objects into the plug values. But when the import reads Excel, Sxssfworkbook does not read the file stream method, can only use Xssfworkbook to read, thousands of data may be memory overflow.

This is the time to use Opcpackage

 Public Static Opcpackage Open (Java.io.InputStream in)                        throws invalidformatexception,                               Package. Note-uses quite a bit more memory than open (String), which doesn ' t need to hold the whole zip file in memory, and can TA Ke advantage of native methodsParameters:    package  fromreturns:    A packagebase Objectthrows:    invalidformatexception    java.io.IOException

The POI-given API indicates that using opcpackage does not require that the file be fully read into memory.

Calling methods

File File =new  fileinputstream (file); Excelreader.readinputstream (is); excelreader.process ();

Excelreader.java

/*** Abstract Excel2007 Reader, excel2007 of the underlying data structure is an XML file, using Sax event-driven method parsing * XML, need to inherit DefaultHandler, when encountering the content of the file, the event will trigger, this practice can be greatly reduced * The cost of memory, especially for files that are used in large volumes of data. * */ Public classExcel2007readerextendsDefaultHandler {//Shared string Table    Privatesharedstringstable SST; //Last content    PrivateString lastcontents; Private Booleannextisstring; Private intSheetindex =-1; PrivateList<string> rowlist =NewArraylist<string>(); //Current Line    Private intCurrow = 0; //Current Column    Private intCurcol = 0; //Date Flag    Private BooleanDateflag; //Digital Signage    Private BooleanNumberflag; Private Booleanistelement; PrivateIrowreader Rowreader;  Public voidSetrowreader (Irowreader rowreader) { This. Rowreader =Rowreader; }        /**Traverse only one spreadsheet, where sheetID is the sheet index to traverse, starting with 1, 1-3 *@paramfilename *@paramsheetID *@throwsException*/     Public voidProcessonesheet (String filename,intsheetID)throwsException {opcpackage pkg=opcpackage.open (filename); Xssfreader R=NewXssfreader (pkg); Sharedstringstable SST=r.getsharedstringstable (); XMLReader Parser=Fetchsheetparser (SST); //Find sheet based on rid# or rsheet#InputStream Sheet2 = R.getsheet ("rId" +sheetID); Sheetindex++; InputSource Sheetsource=NewInputSource (Sheet2);        Parser.parse (Sheetsource);    Sheet2.close (); }    /*** Traverse all spreadsheets in the workbook *@paramfilename *@throwsException*/     Public voidProcess (String filename)throwsException {opcpackage pkg=opcpackage.open (filename); Xssfreader R=NewXssfreader (pkg); Sharedstringstable SST=r.getsharedstringstable (); XMLReader Parser=Fetchsheetparser (SST); Iterator<InputStream> sheets =R.getsheetsdata ();  while(Sheets.hasnext ()) {Currow= 0; Sheetindex++; InputStream sheet=Sheets.next (); InputSource Sheetsource=NewInputSource (sheet);            Parser.parse (Sheetsource);        Sheet.close (); }    }     PublicXMLReader Fetchsheetparser (sharedstringstable SST)throwssaxexception {XMLReader parser=xmlreaderfactory. Createxmlreader ("Org.apache.xerces.parsers.SAXParser");  This. SST =SST; Parser.setcontenthandler ( This); returnparser; }     Public voidstartelement (String uri, String localname, string name, Attributes Attributes)throwssaxexception {//c = Cell        if("C". Equals (name)) {            //Mark Nextisstring as True if the next element is an index of SSTString Celltype = Attributes.getvalue ("T"); if("s". Equals (Celltype)) {nextisstring=true; } Else{nextisstring=false; }            //Date FormatString Celldatetype = Attributes.getvalue ("s"); if("1". Equals (Celldatetype)) {Dateflag=true; } Else{Dateflag=false; } String Cellnumbertype= Attributes.getvalue ("s")); if("2". Equals (Cellnumbertype)) {Numberflag=true; } Else{Numberflag=false; }                    }        //When the element is T        if("T". Equals (name)) {istelement=true; } Else{istelement=false; }                //EmptyLastcontents = ""; }     Public voidendElement (String uri, String localname, string name)throwssaxexception {//The actual string to be stored based on the index value of the SST to the cell//At this point the characters () method may be called multiple times        if(nextisstring) {Try {                intIDX =Integer.parseint (lastcontents); Lastcontents=Newxssfrichtextstring (Sst.getentryat (IDX)). ToString (); } Catch(Exception e) {}}//The t element also contains a string        if(istelement) {String value=Lastcontents.trim ();            Rowlist.add (Curcol, value); Curcol++; Istelement=false; //v = value of the cell, if the cell is a string, the value of the V-label is the index of the string in the SST//Add the contents of the cell to the rowlist before you remove the whitespace before and after the string}Else if("V". Equals (name)) {String value=Lastcontents.trim (); Value= Value.equals ("")? " ": value; //Date format processing            if(dateflag) {Date Date=hssfdateutil.getjavadate (double.valueof (value)); SimpleDateFormat DateFormat=NewSimpleDateFormat ("Dd/mm/yyyy"); Value=Dateformat.format (date); }             //Numeric type handling            if(Numberflag) {BigDecimal bd=NewBigDecimal (value); Value= Bd.setscale (3, bigdecimal.round_up). toString ();            } rowlist.add (Curcol, value); Curcol++; }Else {            //if the label name is row, this means that it has reached the end of the line, calling the Optrows () method            if(Name.equals ("Row") {rowreader.getrows (sheetindex,currow,rowlist);                Rowlist.clear (); Currow++; Curcol= 0; }        }            }     Public voidCharacters (Char[] ch,intStartintlength)throwssaxexception {//get the value of the cell contentsLastcontents + =NewString (CH, start, length); }}

POI implements Big Data excle import and export to resolve memory overflow problems