Java uses Apache POI to manipulate Excel files

• Official introduction

HSSF is the POI Project's pure Java implementation of the Excel ' ( -2007) file format. XSSF is the POI Project's pure Java implementation of the Excel OOXML (. xlsx) file format. Learned from official documentation: the HSSF package provided by the POI is used to operate E The. xls file for Xcel ' 97 (-2007), while the XSSF package is used to manipulate the. xslx file after Excel2007.

• Required JAR Package

The POI official downloads the package and extracts the jar packages necessary for the Java operations Excel file: where Dom4j-1.6.1.jar and Xbean.jar (: http://mirror.bjtu.edu.cn/apache/xmlbeans/ binaries/Website: http://xmlbeans.apache.org
) is not included in the jar package provided by the POI and needs to be downloaded separately, otherwise the program throws an exception: Java.lang.ClassNotFoundException:org.apache.xmlbeans.XmlOptions.

• Specific code

Create a Java project in Eclipse and add the jar packages listed above to the project's classpath, otherwise the reference to the jar package will be an error. Direct code (code base frame from Apache POI official website, self-tuning section): Create an Excel file and write the content:

 Public Static voidCreateworkbook ()throwsIOException {Workbook WB=NewHssfworkbook (); String safeName1= Workbookutil.createsafesheetname ("[O ' Sheet1]"); Sheet Sheet1=Wb.createsheet (SAFENAME1); Creationhelper Createhelper=Wb.getcreationhelper (); //Create a row and put some cells in it. Rows are 0 based.Row row = Sheet1.createrow (( Short) 0); //Create a cell and put a value in it.Cell cell = Row.createcell (0); Cell.setcellvalue (1234); //Or do it on one line.Row.createcell (2). Setcellvalue (Createhelper.createrichtextstring ("This is a string")); Row.createcell (3). Setcellvalue (true); //We style the second cell as a date (and time). It's important to//Create a new cell style from the workbook otherwise your can end up//modifying the built in style and effecting not only this cell but//Other cells.CellStyle CellStyle =Wb.createcellstyle (); Cellstyle.setdataformat (Createhelper.createdataformat (). GetFormat ("M/d/yy h:mm")); Cell= Row.createcell (1); Cell.setcellvalue (NewDate ());        Cell.setcellstyle (CellStyle); //You can also set date as Java.util.CalendarCellStyle CellStyle1 =Wb.createcellstyle (); Cellstyle1.setdataformat (Createhelper.createdataformat (). GetFormat ("YyyyMMdd HH:mm:ss"));        Cellstyle1.setborderbottom (Cellstyle.border_thin);        Cellstyle1.setbottombordercolor (IndexedColors.BLACK.getIndex ());        Cellstyle1.setborderleft (Cellstyle.border_thin);        Cellstyle1.setleftbordercolor (IndexedColors.GREEN.getIndex ());        Cellstyle1.setborderright (Cellstyle.border_thin);        Cellstyle1.setrightbordercolor (IndexedColors.BLUE.getIndex ());        Cellstyle1.setbordertop (cellstyle.border_medium_dashed);        Cellstyle1.settopbordercolor (IndexedColors.BLACK.getIndex ()); Cell= Row.createcell (4);        Cell.setcellvalue (Calendar.getinstance ());        Cell.setcellstyle (CELLSTYLE1); FileOutputStream Fileout=NewFileOutputStream ("E:/test/workbook.xls");        Wb.write (fileout);    Fileout.close (); }

To read the contents of an Excel file:

 Public Static voidReadexcel ()throwsinvalidformatexception, IOException {//Use a fileWorkbook wb1 = Workbookfactory.create (NewFile ("E:/test/userinfo.xls")); Sheet Sheet= Wb1.getsheetat (0); //Decide which rows to process//int rowstart = Math.min (Ten, Sheet.getfirstrownum ()); //int rowend = Math.max (+, Sheet.getlastrownum ());        intRowstart =Sheet.getlastrownum (); intRowend = Sheet.getlastrownum () + 1;        Logger.info (Sheet.getfirstrownum ());        Logger.info (Sheet.getlastrownum ());  for(intRowNum = Rowstart; RowNum < Rowend; rownum++) {Row R=Sheet.getrow (RowNum); intLastcolumn = Math.max (R.getlastcellnum (), 10);            Logger.info (Lastcolumn); //to get the contents of a cell, you first need to know what kind//of Cell It is (asking a string cell for its numeric contents would//get a NumberFormatException for example). So, you'll want//to switch on the cell's type, and then call the appropriate//getter for that cell.             for(intCN = 0; CN < Lastcolumn; cn++) {                //cell cell = R.getcell (cn, row.return_blank_as_null);Cell cell =R.getcell (CN); Switch(Cell.getcelltype ()) { CaseCell.CELL_TYPE_STRING:logger.info (Cell.getrichstringcellvalue (). getString ());  Break;  CaseCell.cell_type_numeric:if(dateutil.iscelldateformatted (cell)) {Logger.info (Cell.getdatecellvalue ()); } Else{logger.info (Cell.getnumericcellvalue ()); }                     Break;  CaseCell.CELL_TYPE_BOOLEAN:logger.info (Cell.getbooleancellvalue ());  Break;  CaseCell.CELL_TYPE_FORMULA:logger.info (Cell.getcellformula ());  Break; default: Logger.info ("Empty"); }            }        }    }

Here is a concrete example of an Excel file in an instance that reads:

What our program does is to read the contents of each row of files according to the order of the first row headings, and the order of the actual headings and contents is indeterminate, but we require that the contents of the file be output in the order given. The code is as follows:

 Public Static voidReaduserinfo ()throwsinvalidformatexception, IOException {string[] titles= {"Toll number", "nature of Charges", "name", "Home Address", "Work unit", "Phone", "phone",                "Community Building Number", "Unit Number", "Floor", "Room number", "gross area (㎡)", "Area basis", "a area", "a Super",                "A light Body", "B area", "B-Super", "B-light Body", "User number", "the building before the table number" }; //the Map,key used to store the caption and order is the title, and value is the sequential numbermap<string, integer> titlemap =NewHashmap<string, integer>(); //write the given order to map         for(inti=0; i<titles.length; i++) {titlemap.put (titles[i], i); } Workbook WB= Workbookfactory.create (NewFile ("E:/test/userinfo.xls"));  for(intNumsheet = 0; Numsheet < Wb.getnumberofsheets (); numsheet++) {Sheet Xsheet=Wb.getsheetat (Numsheet); if(Xsheet = =NULL) {                Continue; }            //gets the header content of the first rowRow Trow = Xsheet.getrow (0); //an array that stores the order of headingsinteger[] Titlesort =NewInteger[trow.getlastcellnum ()]; //Loop title             for(intTitlenum = 0; Titlenum < Trow.getlastcellnum (); titlenum++) {Cell Tcell=Trow.getcell (Titlenum); String title= ""; if(Tcell = =NULL|| "". Equals (Tcell)) {                } Else if(Tcell.getcelltype () = = Xssfcell.cell_type_boolean) {//Boolean type handling//Logger.info (Xcell.getbooleancellvalue ());}Else if(Tcell.getcelltype () = = Xssfcell.cell_type_numeric) {//Numeric type handlingtitle =doubletostring (Tcell.getnumericcellvalue ()); } Else{//other types of processingtitle =Tcell.getstringcellvalue (); }                //read the straight signal from the map by getting the caption, and write the array that holds the title sequence numberInteger ts =Titlemap.get (title); if(ts! =NULL) {Titlesort[titlenum]=ts; }            }            //Looping rows Row             for(intRowNum = 1; RowNum < Xsheet.getlastrownum () + 1; rownum++) {Row Xrow=Xsheet.getrow (RowNum); if(Xrow = =NULL) {                    Continue; }                //Cyclic column cellString[] v =NewString[titlesort.length];  for(intCellnum = 0; Cellnum < Titlesort.length; cellnum++) {Cell XCell=Xrow.getcell (Cellnum); String value= ""; if(XCell = =NULL|| "". Equals (XCell)) {                    } Else if(Xcell.getcelltype () = = Xssfcell.cell_type_boolean) {//Boolean type handlingLogger.info (Xcell.getbooleancellvalue ()); } Else if(Xcell.getcelltype () = = Xssfcell.cell_type_numeric) {//Numeric type handlingValue =doubletostring (Xcell.getnumericcellvalue ()); } Else{//other types of processingValue =Xcell.getstringcellvalue (); }                                        //store Each row of records by number in the title OrderV[titlesort[cellnum]] =value;//logger.info ("v[" + Titlesort[cellnum] + "] =" + V[titlesort[cellnum] ");                }                //Loop result Array, obtained in the same order as given                 for(inti = 0; i < v.length; i++) {logger.info (v[i]); }            }        }    }

The tool class used in the previous program doubletostring (converts the double type in Excel to a string type, processing the number in the form of scientific notation):

Private StaticString doubletostring (Doubled) {String str=double.valueof (d). ToString (); //System.out.println (str);String result = ""; if(Str.indexof ("E") > 2) {            intindex = Str.indexof ("E"); intPower = Integer.parseint (str.substring (index + 1)); BigDecimal value=NewBigDecimal (str.substring (0, index)); Value=value.movepointright (Power); Result=value.tostring (); } Else {            if(Str.indexof (". 0") > 0) Result= str.substring (0, Str.indexof (". 0")); Elseresult=str; }        returnresult; }

At present, the application of POI is limited to this, and no further in-depth, after writing a new relevant content will continue to supplement, please greatly criticized!

Reprint Please specify source: http://www.cnblogs.com/bxljoy/p/3939409.html

Java uses Apache POI to manipulate Excel files