Appium Automation-----Data-driven read external Excel files

===== the excel====== to be operated on

650) this.width=650; "Src=" Https://s2.51cto.com/oss/201711/16/a369157c05c8aeffc8757150021cca8a.png-wh_500x0-wm_3 -wmp_4-s_1059370577.png "title=" qq picture 20171116174705.png "alt=" A369157c05c8aeffc8757150021cca8a.png-wh_ "/>

===== The operation of Excel above ==============

File operations for Excel
public class Excelutiltest {

Private Xssfworkbook Excelwbook;//excel working thin
Private Xssfsheet excelwsheet;//Worksheet
Private Xssfrow row;//Line
Private Xssfcell cell;//Column
Private String Filepath;//excel Workbook path

/** Construction Method */
Set the file path of Excel to manipulate and the sheet name in the Excel file
When you read and write Excel, you need to call this method, set the Excel file path to manipulate and the sheet name to manipulate.
Public Excelutiltest (String path,string sheetname) throws exception{
FileInputStream Excelfile;
This.filepath=path;

try {
FileInputStream object that instantiates an Excel file
Excelfile = new FileInputStream (path);
Xssfworkbook object that instantiates an Excel file
Excelwbook = new Xssfworkbook ();
Instantiates an Excel file's Xssfsheet object, specifying the sheet name in the Excel file;
Excelwsheet = Excelwbook.getsheet (sheetname);

} catch (Exception e) {
Throw (e);
}
}


/** read a function that specifies a cell in an Excel file that supports only Excel files with the suffix of xlsx */
Public String getcelldata (int rownum,int cellnum) throws exception{
try {
Gets the specified Cell object by specifying the row number and list of cells through the function parameter
Cell = Excelwsheet.getrow (rowNum). Getcell (Cellnum);
If the contents of the cell are of type string, use Getstringcellvalue () to get the contents of the cell
Gets the contents of a cell using Getnumericcellvalue () if the contents of the cell are numeric types
String celldata= "";
/** Get cell type */
if (cell.getcelltype () = = xssfcell.cell_type_string) {
Celldata = Cell.getstringcellvalue ();
}else if (cell.getcelltype () = = Xssfcell.cell_type_numeric) {
DecimalFormat df = new DecimalFormat ("0");//The method of preserving decimals, 0 is not preserving decimals
Celldata = Df.format (Cell.getnumericcellvalue ());//format () formatting
}
return celldata;
} catch (Exception e) {
TODO auto-generated Catch block
E.printstacktrace ();
Return "";
}

}

/** writes input data in the execution cell of an Excel file, which supports only file writes with the suffix of xlsx
* @param result Results
* @param number of rowNum lines
* @param cellnum number of columns
* */
public void Setcelldata (int rownum,int cellnum,string result) throws exception{

try{
Get the Row object in an Excel file
Row=excelwsheet.getrow (RowNum);
Returns null if the cell is empty
Cell = Row.getcell (cellnum,row.return_blank_as_null);

if (cell = = null) {
When the Cell object is null, the cell is created because the cell is empty and cannot be called by the Cell object's Setcellvalue method to set the cell value
Cell=row.createcell (Cellnum);
You can call the Cell object's Setcellvalue method to set the cell value after the cell is created
Cell.setcellvalue (result);
}else{
If there is content in the cell, you can directly call the Cell object's Setcellvalue method to set the cell value
Cell.setcellvalue (result);
SYSTEM.OUT.PRINTLN ("Cell value is set to complete");
}

Instantiate a file output stream object that is written to an Excel file
FileOutputStream fileout = new FileOutputStream (FilePath);
Write content to an Excel file
Excelwbook.write (fileout);
Call flush method to force flush to write to file
Fileout.flush ();
Close the file output stream object
Fileout.close ();

}catch (Exception e) {
E.printstacktrace ();
Throw (e);
}

}

/** static method for getting test data from an Excel file
* @throws IOException
* */
public static object[][] Gettestdata (String excelfilepath,string sheetname) throws ioexception{
Combines the absolute path of an Excel data file by the path and file name of the data file passed in by the parameter
File File=new file (Excelfilepath);

Create a FileInputStream object for reading an Excel file
FileInputStream InputStream = new FileInputStream (file);

Declaring Workbook objects
Workbook Workbook = null;

Gets the suffix name of the file type;
String fileextensionname = excelfilepath.substring (Excelfilepath.indexof ("."));

To determine if it is xlsx, use the Xssfworkbook object for instantiation
If it is XLS, it is instantiated with the Ssfworkbook object
if (Fileextensionname.equals (". xlsx")) {

WorkBook = new Xssfworkbook (InputStream);

}else if (fileextensionname.equals (". xls")) {
WorkBook = new Hssfworkbook (InputStream);
}

Generate sheet objects with the SheetName parameter
Sheet Sheet = Workbook.getsheet (sheetname);

Gets the number of rows of data in the Sheet1 in an Excel data file, the Getlastrownum method gets the last line number of the data
The Getfirstrownum method gets the first line number of the data, subtracting the number of rows from the data
int rowCount = Sheet.getlastrownum ()-sheet.getfirstrownum ();
System.out.println (sheet+ "Total number of rows is" +rowcount);

Create a list object named records to store data obtained from an Excel data file
List<object[]> records = new Arraylist<object[]> ();

Use 2 for loops to traverse all data in an Excel data file (except for the first row, the first row is the data column name), so starting with 1
for (int i =1;i<=rowcount;i++) {
Get a Row object using GetRow ()
Row Row=sheet.getrow (i);

/* Declare an array to store the test cases and data in each row of the Excel data file, and the size of the array is declared dynamically using GETLASTCELLNUM-1 to achieve the same number of test data and array size
* Because the final cell of the test data in the Excel data file is the result of the test execution, the second-to-last cell for this test data row
* Whether the state is running. The last two columns of cell data do not need to be passed into the test method, so the last two cell data in each row is removed using the GetLastCellNum-2 method
* Calculate the number of test data to be stored, and as the initialization size of the test data array
*/
String Fields[]=new string[row.getlastcellnum () -2];//creates an array of type string and specifies the size

/*if is used to determine if a data row is to be executed, the second-to-last behavior of the Excel file, the status bit of the data row, marked as *
* "Y" indicates that this data is to be executed by the test script, data rows marked as non-"Y" are considered not to be involved in the execution of the test script and will be skipped
* */
String Yesornot=row.getcell (Row.getlastcellnum ()-2). Getstringcellvalue ();
System.out.println (Row.getcell (Row.getlastcellnum ()-2). Getcelltype () ==xssfcell.cell_type_numeric);

if (Row.getcell (Row.getlastcellnum ()-2). Getstringcellvalue (). Equals ("Y")) {
for (int j=0;j<row.getlastcellnum () -2;j++) {
Determines whether the cell field of Excel is a number or a character, and the string format calls Getstringcellvalue () to get
Number format call Getnumericcellvalue () gets
System.out.println (Row.getcell (j). Getcelltype () = = Xssfcell.cell_type_numeric);
if (Row.getcell (j). Getcelltype () = = xssfcell.cell_type_string) {
Fields[j]=row.getcell (j). Getstringcellvalue ();
}else if (Row.getcell (j). Getcelltype () = = Xssfcell.cell_type_numeric) {
DecimalFormat df= New DecimalFormat ("0");
Fields[j]=df.format (Row.getcell (j). Getnumericcellvalue ());
}else{
System.out.println ("Malformed");
}
System.out.println (Fields[j]);
}

Fields data objects are stored in the records list
Records.add (fields);
}

}

Define function return value, i.e. object[][]
Converts a list of stored test data to a two-dimensional data of an object
Object[][] Results=new object[records.size () [];

Set values for each row of two-dimensional data, each of which is an object
for (int i=0;i<records.size (); i++) {
Results[i]=records.get (i);
}
Close Excel File
Inputstream.close ();
return results;
}


Traversing a two-dimensional array
public void PrintArray (object[][] arr) {
for (int x = 0; x < arr.length; × x + +) {
for (int y = 0; y < arr[x].length; y++) {
System.out.print (Arr[x][y] + "");
}
System.out.println ("");
}
}
}

======excel file Operation Class--Test class =====

public class Testexceluticl {

public static void Main (string[] args) throws Exception {
Excelutiltest eut = new Excelutiltest ("configs/test case. xlsx", "Login Data");//excel name, sheet name
Eut.getcelldata (3, 3);
Object[][] Arr=eut.gettestdata ("configs/test case. xlsx", "Login Data");
Eut.printarray (arr);
}

}

===== is applied to automated data drivers to automate data-driven reading of external Excel files =========

/** Find element */
@Test (dataprovider= "Logintestdata")
public static void TestCase (String logintestdata1,string loginTestData2) {
Thread.Sleep (5000);//wait
Enter
Driver.findelementbyid ("element location ID"). click ();
Phone number
Androidelement modile= Driver.findelementbyid ("element location ID");
Modile.clear ();
Modile.sendkeys (LOGINTESTDATA1);
Verification Code
Androidelement Pws=driver.findelementbyid ("element location ID");
Pws.clear ();
Pws.sendkeys (LOGINTESTDATA2);
Click Sign In
Driver.findelementbyid ("element location ID"). click ();

}

/** Login Data Driver
* */
@DataProvider (name= "Logintestdata")
public static object[][] data () throws IOException
{
Object[][] Arr=excelutiltest.gettestdata ("configs/test case. xlsx", "Login Data");
return arr;
}

Appium Automation-----Data-driven read external Excel files