Java parsing Excel

The previous two days summed up some examples of Excel and CSV combined with testng for data-driven testing, for Excel to store testcase and keywords How to parse, also do a corresponding summary, I hope in the study of the road forward, there is no place, I hope we point out common progress together.

The use of the POI to Excel parsing, the need for the jar package file:

Poi-3.11-20141221.jar

Poi-ooxml-3.11-20141221.jar

Poi-ooxml-schemas-3.11-20141221.jar

Xmlbeans-2.6.0.jar

The code is as follows:

 PackageCom.util.datadriver;ImportJava.io.File;ImportJava.io.FileInputStream;ImportJava.io.FileOutputStream;Importjava.util.ArrayList;Importjava.util.List;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;ImportOrg.apache.poi.ss.usermodel.Cell;ImportOrg.apache.poi.ss.usermodel.Row;ImportOrg.apache.poi.ss.usermodel.Sheet;ImportOrg.apache.poi.ss.usermodel.Workbook;ImportOrg.apache.poi.xssf.usermodel.XSSFWorkbook;/*** Basic operation of data in Excel via POI * * requires import of JAR package: Poi-3.11-20141221.jar, Poi-ooxml-3.11-20141221.jar, * poi-ooxml-sche Mas-3.11-20141221.jar, Xmlbeans-2.6.0.jar **/ Public classExcelcontroller {Workbook Workbook=NULL; Sheet Sheet=NULL;  Public voidsetexcelfile (String FilePath, String fileName, String sheetname) {Try{FileInputStream fis=NewFileInputStream (NewFile (FilePath)); String type= Filename.substring (Filename.indexof ("."))); if(Type.equals (". xlsx")) ) {Workbook=NewXssfworkbook (FIS); } Else if(Type.equals (". xls") ) {Workbook=NewHssfworkbook (FIS); } sheet=Workbook.getsheet (SheetName);        Fis.close (); } Catch(Exception e) {e.printstacktrace (); }            }    //get the value of a cell     PublicString Getcelldata (intRowintcolumn) {                Try {                        //get cell Cells ObjectCell cell =sheet.getrow (Row). Getcell (column); //set the type of cell return valueCell.setcelltype (cell.cell_type_string); //get the value into cell cellsString Celldata =Cell.getstringcellvalue (); returnCelldata; } Catch(Exception e) {Throw(e); }    }        //set the value of a cell@SuppressWarnings ("Static-access")     Public voidSetcelldata (String value,intRowCount,intColumnCount, String FilePath) {                Try{row row=Sheet.getrow (RowCount); Cell Cell=Row.getcell (Columncount,row.                        Return_blank_as_null); if(Cell = =NULL) {Row.createcell (ColumnCount). Setcellvalue (value); }Else{cell.setcellvalue (value); } FileOutputStream Fos=NewFileOutputStream (NewFile (FilePath));            Workbook.write (FOS);            Fos.flush ();                    Fos.close (); } Catch(Exception e) {e.printstacktrace (); }            }        //get the number of lines in Excel     Public intGetcolcount (Sheet Sheet1) {intFirstcolcount =Sheet1.getfirstrownum (); intLastcolcount =Sheet1.getlastrownum (); intSumcolcount = Lastcolcount-firstcolcount + 1; returnSumcolcount; }        //get data for each row in Excel, and return every row of data in an array to facilitate data-driven with testng         Publicobject[][] Getexceldata (String filePath, String fileName, string sheetname)throwsException {intSumrowcount =Getcolcount (sheet); List<Object[]> list =NewArraylist<object[]>(); //gets the row object for each row, the first behavior Information Bar, not counted, so starting from 1         for(inti = 1; i < Sumrowcount; i++) {row row=Sheet.getrow (i); //gets the count of the last cell in a row            intLastcellcount =Row.getlastcellnum (); //defines an array to hold the values in the cell, defining the length of the array according to the cell's lengthstring[] Fileds =NewString[lastcellcount];  for(intj = 0; J < Lastcellcount; J + +) {String Cellvalue=Row.getcell (j). Getstringcellvalue (); FILEDS[J]=Cellvalue;        } list.add (Fileds); }                //defines a two-dimensional array of object[][] that holds the values in the listObject[][] results =Newobject[list.size () []; //sets the value of each row of the two-dimensional array,         for(intA = 0; A < List.size (); a++) {Results[a]=List.get (a); }        returnresults; }    }

Java parsing Excel