Accessing Excel documents with POI components in Java

For Java access to Excel files, commonly used components have JXL and poi, JXL because there is no version update, and only access to EXCEL2007 previous file format, the xlsx file format is not currently supported, it is recommended to use POI,
The official POI is: http://poi.apache.org/
In essence, the EXCEL2007 document is an XML document, so there are two ways to read a POI file in Excel, one is DOM, the other is sax, Dom is simple and easy to use for Excel, but it takes up more memory, especially more than 10000 rows in the table. Memory overflow problems often occur, although you can change the JVM startup parameters, increase memory, but can not fundamentally solve the problem, so when the need to read large Excel files, we use the second way more reasonable.
For Excel document write operations also face the same problem, if the number of lines written too many, also encounter memory overflow, but the write operation to solve large files is relatively simple, set the write cache is OK, but because Excel sheet has the maximum number of rows limit (65534), so if the number of rows exceeds this limit , you need to create a new sheet, the following is an example of a large file read and write operation to Excel.

Import Java.io.inputstream;import java.util.date;import Java.util.iterator;import Java.util.map;import Java.util.regex.pattern;import Org.apache.poi.openxml4j.opc.opcpackage;import Org.apache.poi.ss.usermodel.builtinformats;import Org.apache.poi.ss.usermodel.dateutil;import Org.apache.poi.xssf.eventusermodel.xssfreader;import Org.apache.poi.xssf.model.sharedstringstable;import Org.apache.poi.xssf.model.stylestable;import Org.apache.poi.xssf.usermodel.xssfcellstyle;import Org.apache.poi.xssf.usermodel.xssfrichtextstring;import Org.xml.sax.attributes;import Org.xml.sax.InputSource; Import Org.xml.sax.saxexception;import Org.xml.sax.xmlreader;import Org.xml.sax.helpers.defaulthandler;import Org.xml.sax.helpers.xmlreaderfactory;public class Testexcelreader {public static void main (string[] args) throws Excep    tion {String path = "C://cache//test.xlsx";        Excelhandler handler = new Excelhandler ();    Handler.process (path); } public static class Excelhandler extends DefaulthaNdler {public Excelhandler () {} private stylestable stylestable;        Private Celldatatype DataType;        Private Sharedstringstable SST; Private map&lt; String, string&gt;        Datas        Private short DataFormat;        Private String dataformatstring;        Private Boolean tagstring;        Private Boolean telement = false;        Private String content;        private coordinate coordinate; @Override public void Startelement (string uri, String localname, String qName, Attributes Attributes) throws Saxexc            eption {super.startelement (URI, LocalName, qName, attributes);                if ("C". Equals (QName)) {this.setdatatype (attributes);            coordinate = Getcoordinate (Attributes.getvalue ("R"));            } else if ("T". Equals (QName)) {telement = true;            } else {telement = false;        } content = "";  } @Override      public void EndElement (string uri, String localname, String qName) throws Saxexception {Super.endelement            (URI, LocalName, QName);                if (tagstring) {int idx = integer.parseint (content);            Content = new Xssfrichtextstring (Sst.getentryat (IDX)). ToString ();                } if (telement) {String value = Content.trim ();                Setdatavalue (datas, value, true);            TElement = false;            } if ("V". Equals (QName)) {Setdatavalue (datas, Content.trim (), false);                } if ("Row". Equals (QName)) {//process the row datas;                System.out.println (datas);            Datas.clear ();            }} public void process (String path) {InputStream is = null;            Opcpackage pkg = null;                try {pkg = opcpackage.open (path);    Xssfreader reader = new Xssfreader (pkg);            stylestable = Reader.getstylestable ();                Sharedstringstable SST = reader.getsharedstringstable ();                THIS.SST = SST; Iterator&lt;inputstream&gt;                it = Reader.getsheetsdata ();                    if (It.hasnext ()) {is = It.next ();                    InputSource Source = new InputSource (IS);                    XMLReader parser = Xmlreaderfactory.createxmlreader ("Org.apache.xerces.parsers.SAXParser");                    Parser.setcontenthandler (this);                Parser.parse (source);            }} catch (Throwable ex) {ex.printstacktrace ();                    } finally {try {if (is! = null) is.close ();                if (pkg! = null) pkg.close (); } catch (Throwable e) {}}} public void Setdatavalue (map&lt; String, string&gt;      Datas, String value, Boolean t) {      String column = Coordinate.getcolumnname ();            if (t) {datas.put (column, value); } else {switch (dataType) {case Bool:char first = V                        Alue.charat (0); Datas.put (column, first = = ' 0 '?)                        "No": "Yes");                    Break                        Case error:string err = "\" ERROR: "+ value.tostring () + '" ';                        Datas.put (column, err);                    Break                        Case FORMULA:datas.put (column, value);                    Break                        Case inlinestr:xssfrichtextstring RTS = new Xssfrichtextstring (value.tostring ());                        String inline = rts.tostring ();                        Datas.put (column, inline);                    Break   Case Sstindex:string Sidx = value.tostring ();                     try {int idx = Integer.parseint (SIDX);                            Xssfrichtextstring rtss = new Xssfrichtextstring (Sst.getentryat (IDX));                            String Sstindex = rtss.tostring ();                            Datas.put (column, sstindex);                        RTSS = null;                            } catch (NumberFormatException ex) {ex.printstacktrace ();                        Datas.put (column, value);                    } break;                        Case Number:double db = double.parsedouble (value);                        Datas.put (column, string.valueof (db));                    Break                        Case date:double number = double.parsedouble (value);                        Date date = dateutil.getjavadate (number);           Datas.put (column, date.tostring ());             Break                        Default:datas.put (column, "");                Break }}} public void Setdatatype (Attributes Attributes) {String Celltype = attributes.ge            TValue ("T");            String cellstylestr = Attributes.getvalue ("s");            DataType = Celldatatype.number;            if ("B". Equals (Celltype)) {dataType = Celldatatype.bool;            } else if ("E". Equals (Celltype)) {dataType = Celldatatype.error;            } else if ("Inlinestr". Equals (Celltype)) {dataType = Celldatatype.inlinestr;            } else if ("s". Equals (Celltype)) {dataType = Celldatatype.sstindex;            } else if ("str". Equals (Celltype)) {dataType = Celldatatype.formula; } if (cellstylestr! = null) {int styleindex = Integer.parseint (cellstylESTR);                Xssfcellstyle style = Stylestable.getstyleat (Styleindex);                DataFormat = Style.getdataformat ();                dataformatstring = Style.getdataformatstring ();                    if (dataformatstring = = null) {DataType = Celldatatype.null;                dataformatstring = Builtinformats.getbuiltinformat (DataFormat);                        } if (DataFormat > 0) {if (!isscientificreg (dataformatstring)) {                    DataType = celldatatype.date;        }}}}} public static interface coordinate {String getcolumnname ();    int GetLine ();        public static coordinate getcoordinate (String row) {coordinate coordinate = null; if (row! = NULL &&! ").            Equals (row)) {int last =-1;             for (int i = 0; i < row.length (); i++) {if (Character.isdigit (Row.charat (i))) {       last = i;                Break }} String sline = last! =-1?            Row.substring (last): "0"; Final String column = last! =-1?            Row.substring (0, last): row;            Final int line = Integer.parseint (sline);                    coordinate = new coordinate () {@Override public String getcolumnname () {                return column;                } @Override public int getLine () {return line;        }            }; } if (coordinate = = NULL) {coordinate = new coordinate () {@Override Publ                IC String getColumnName () {return "";                } @Override public int getLine () {return 0;        }            };    } return coordinate; } public static Boolean Isscientificreg (String str) {Boolean result= false; if (str! = null) {String reg = "0.[            0]+[ee][+-]00 ";            Pattern pattern = Pattern.compile (reg);        result = Pattern.matcher (str). matches ();    } return result; } public static enum Celldatatype {BOOL, ERROR, FORMULA, Inlinestr, Sstindex, number, DATE, NULL}}

Accessing Excel documents with POI components in Java