Java-poi working with Excel file methods

There are lots of open source libraries for working with Excel files, Common poi, jxls .....

Focus on the method of POI processing Excel:

1. Write Files

//initialization of data tables with row precedence  Public Static voidCreaterows ()throwsIOException {Workbook WB=NewHssfworkbook (); //Create a tableSheet Sheet = Wb.createsheet ("Test sheet_01"); List<String> list =NewArraylist<string>(); List.add (A); List.add ("B"); intRowCount = List.size ();//set the number of rows based on the dataset         for(inti = 0; i < RowCount; i++) {row row= Sheet.createrow (i);//Create row, header is line No. 0             for(intj = 0; J < List.size (); J + +) {                //assign a value to the first cellCell cell = Row.createcell (0); Cell.setcellvalue ("Test Data"); //assign a value to the second cellCell Cell_1 = Row.createcell (1); Cell_1.setcellvalue ("Test"); }} FileOutputStream Fos=NewFileOutputStream ("Excel.xls of/users/file/test");        Wb.write (FOS); if(NULL!=Fos)        {Fos.close (); } System.out.println ("Test Data Completion output: "); }

2. Read the file

//converting the contents of a file to a two-dimensional array@SuppressWarnings ("Resource")    Private StaticString[][] GetData (Fileitem Fileitem,intignorerows)throwsFileNotFoundException, IOException {List<String[]> result =NewArraylist<string[]>(); introwsize = 0; Bufferedinputstream in=NewBufferedinputstream (Fileitem.getinputstream ());        Workbook WB; if(Fileitem.getname (). IndexOf (". xlsx") >-1) {WB=NewXssfworkbook (in); } Else{WB=NewHssfworkbook (in); } cell Cell=NULL;  for(intSheetindex = 0; Sheetindex < Wb.getnumberofsheets (); sheetindex++) {Sheet St=Wb.getsheetat (Sheetindex); //first act title, not take             for(intRowIndex = ignorerows; RowIndex <= st.getlastrownum (); rowindex++) {row row=St.getrow (RowIndex); if(Row = =NULL) {                    Continue; }                intTemprowsize = Row.getlastcellnum () + 1; if(Temprowsize >rowsize) {Rowsize=temprowsize; } string[] Values=NewString[rowsize]; Arrays.fill (values,""); BooleanHasValue =false;  for(intcolumnindex = 0; ColumnIndex <= row.getlastcellnum (); columnindex++) {String value= ""; Cell=Row.getcell (columnindex); if(Cell! =NULL) {                       //Data processing by cell datatype                        Switch(Cell.getcelltype ()) { CaseHSSFCell.CELL_TYPE_STRING:value=Cell.getstringcellvalue ();  Break;  CaseHssfcell.cell_type_numeric:if(hssfdateutil.iscelldateformatted (cell)) {Date Date=Cell.getdatecellvalue (); if(Date! =NULL) {Value=NewSimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"). Format (date); } Else{Value= ""; }                                } Else{Value=NewDecimalFormat ("0"). Format (Cell.getnumericcellvalue ()); }                                 Break;  CaseHssfcell.cell_type_formula://no value when data generated for a formula is imported                                if(!cell.getstringcellvalue (). Equals ("") ) {value=Cell.getstringcellvalue (); } Else{Value= Cell.getnumericcellvalue () + ""; }                                 Break;  CaseHssfcell.cell_type_blank: Break;  CaseHSSFCell.CELL_TYPE_ERROR:value= "";  Break;  CaseHSSFCell.CELL_TYPE_BOOLEAN:value= (Cell.getbooleancellvalue () = =true? "Y": "N");  Break; default: Value= ""; }                    }                    if(ColumnIndex = = 0 && value.trim (). Equals ("")) {                         Break; } Values[columnindex]=Righttrim (value); HasValue=true; }                if(hasValue) {Result.add (values);        }}} in.close (); String[][] ReturnArray=Newstring[result.size ()][rowsize];  for(inti = 0; i < returnarray.length; i++) {Returnarray[i]=(string[]) result.get (i); }        returnReturnArray; }            

The process of the above file processing can judge the data type of the cell, the judgment logic is more cumbersome, if it is a self-defined template file can be directly in the file template of the cell data type all processed into text type;

This way the daemon is all processed by the string data type, which is relatively easy and error-prone.

3. Errors that are easy to appear

Handling exceptions for office2003 and office2007, exceptions are as follows

Org.apache.poi.poifs.filesystem.OfficeXmlFileException:The supplied data appears to BES in the Office 2007+ XML. You is calling the part of the POI this deals with OLE2 Office Documents. You need the different part of the POI to process this data (eg XSSF instead of HSSF)

The solution is to follow the above

Fileitem.getname (). IndexOf (". xlsx") file suffix of the judgment logic to classify processing can be.

Java-poi working with Excel file methods