TestNG: Iterate through array using the Dataprovider

Question

I'm conducting Automation testing using Selenium Webdriver, this code is for TestNg dataprovider, Summary: I am taking data from Excel sheet to data, it's working fine. When I'm debugging the code, I'm getting TestData as testGoogle1(String search1, String Search2) for 1st Iteration it Search1 = Webdriver, Search2 = Qtp, so on,,,, What I want is that it should directly return the array of values something like testGoogle1(String search[]) so that in the @Test itself I can add my function iterate all the rows and columns and test them.

Can anyone please give me the idea how to write it.

Test Data sheet

here is my code

package ExcelTest;
import com.thoughtworks.selenium.*;
import static org.testng.AssertJUnit.*;
import java.io.IOException;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeTest;   
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;   
import org.testng.annotations.AfterTest;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import jxl.*;


public class Sample{

      WebDriver driver;

        @BeforeTest
        public void startTest(){
            driver = Startup.basic();
        } 


@DataProvider(name = "DP1")
public Object[][] createData1() throws Exception{
     Object[][] retObjArr=getTableArray("G:\\Selenium Jar Files\\TestData\\Data.xls","DataPool");
    return(retObjArr);

}

@Test (dataProvider = "DP1")
public void testGoogle1(String search1, String Search2) throws Exception{
//selenium.open("http://www.google.co.in/");
//  driver.get("http://www.google.co.in/");

    //String hello = search.length;
//for(int i=0; i< search.length ;i++)
//{
System.out.println("param   " +search);
Thread.sleep(3000);
System.out.println("Opened");
WebElement element = driver.findElement(By.name("q"));
element.sendKeys(search);
element.submit();
System.out.println("Clicked");
}
//}

@AfterClass
public void tearDown() throws Exception {
//selenium.stop();
}


public String[][] getTableArray(String xlFilePath,String sheetName) throws Exception{

    String[][] tabArray=null;


      File inputWorkbook = new File(xlFilePath);
        Workbook w;
        int startRow,startCol, endRow, endCol,ci,cj;
        try {
            //w = Workbook.
            w = Workbook.getWorkbook(inputWorkbook);
            // Get the first sheet
            Sheet sheet = w.getSheet(sheetName);
            // Loop over first 10 column and lines
            endRow = sheet.getRows();
            endCol = sheet.getColumns();
            tabArray=new String[endRow-1][endCol-1];

            ci=0;

            for (int i=1;i<endRow;i++,ci++){
                cj=0;
                for (int j=1;j<endCol;j++,cj++){

                  Cell cell = sheet.getCell(j, i);
                tabArray[ci][cj] = cell.getContents(); 

                }
            //    System.out.println("");
            }
            //file.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }




       return(tabArray);
    }

}

Can anyone please share any idea ? Thanks

Edited Code:

public class Sample{

      WebDriver driver;

        @BeforeTest
        public void startTest(){
            driver = Startup.basic();
        } 


@DataProvider(name = "DP1")
public Object[][][] createData1() throws Exception{
     Object[][][] retObjArr=getTableArray("G:\\Selenium Jar Files\\TestData\\Data.xls","DataPool");
    return (retObjArr);

}

@Test (dataProvider = "DP1")
public void testGoogle1(String search, String het) throws Exception{

System.out.println("param   " +search);
Thread.sleep(3000);
System.out.println("Opened");
WebElement element = driver.findElement(By.name("q"));
element.sendKeys(search);
element.submit();
System.out.println("Clicked");
}
//}

@AfterClass
public void tearDown() throws Exception {
//selenium.stop();
}


public Object[][][] getTableArray(String xlFilePath,String sheetName) throws Exception{

    Object[][] tabArray=null;


      File inputWorkbook = new File(xlFilePath);
        Workbook w;
        int startRow,startCol, endRow, endCol,ci,cj,ck;
        try {
            //w = Workbook.
            w = Workbook.getWorkbook(inputWorkbook);
            // Get the first sheet
            Sheet sheet = w.getSheet(sheetName);
            // Loop over first 10 column and lines
            endRow = sheet.getRows();
            endCol = sheet.getColumns();
            tabArray=new String[endRow-1][endCol-1];

            ci=0;

            for (int i=1;i<endRow;i++,ci++){
                cj=0;

                for (int j=1;j<endCol;j++,cj++){

                  Cell cell = sheet.getCell(j, i);
                tabArray[ci][cj] = cell.getContents(); 

                }
            //    System.out.println("");
            }
            //file.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }



       return(tabArray);  /// Here Getting the error **Type mismatch: cannot convert from Object[][] to Object[][][]**
    }

}

Answer 1

This is how a @DataProvider works, assuming I have the following array:

[[value1, value2],
 [value3, value4],
 [value5, value6]]

Note that there are 3 rows and 2 columns. The Test will be run 3 times, and passed in 2 values each time. It doesn't matter what the value is.

Now, if you want the test to only be run once, your array should look like this:

[[value1]]

We can make value1 to be whatever we want it to be, so if value1 is the array above, then it will pass in the entire array to the dataProvider. Thus, your return statement should return {{tabArray}}

Answer 2

Looking at your code, you are trying to create a dataprovider function that returns Object[][][]. You cannot do that; the dataprovider function must return either Object[][] (a two dimensional array of any Object), or an Iterator which will perform the same function but in a slightly different way.

You can certainly nest another array inside of the 2D array that is returned; that is why it is Object[][] and not a specific type.

Also make sure you construct your return result correctly. For example, you can't do the following because that does not construct the Object[][] array:

return {{myArray}};

Instead, you would do this:

return new Object[][]{{myArray}};

Answer 3

I think jxl is harder than it needs to be. I wrote an example of a TestNG DataProvider reading Excel data using Apache MetaModel (which recently became a full fledged Apache project) , and you can find that here.

public static Object[][] getCsvData( File csvFile ) 
    {   
        CsvConfiguration conf = new CsvConfiguration( 1 );
        DataContext csvContext = DataContextFactory.createCsvDataContext( csvFile, conf );
        Schema schema = csvContext.getDefaultSchema();
        Table[] tables = schema.getTables();
        Table table = tables[0]; // a representation of the csv file name including extension
        DataSet dataSet = csvContext.query()
                .from( table )
                .selectAll()
                .where("run").eq("Y")
                .execute();
        List<Row> rows = dataSet.toRows();
        Object[][] myArray = get2ArgArrayFromRows( rows );
        return myArray;
    }

And second part of that:

public static Object[][] get2ArgArrayFromRows( List<Row> rows ) {
        Object[][] myArray = new Object[rows.size()][2];
        int i = 0;
        SelectItem[] cols = rows.get(0).getSelectItems();
        for ( Row r : rows ) {
            Object[] data = r.getValues();
            for ( int j = 0; j < cols.length; j++ ) {
                if ( data[j] == null ) data[j] = ""; // force empty string where there are NULL values
            }
            myArray[i][0] = cols;
            myArray[i][1] = data;
            i++;
        }
        logger.info( "Row count: " + rows.size() );
        logger.info( "Column names: " + Arrays.toString( cols ) );
        return myArray;
    }

And an example of a test that uses this DataProvider:

@Test( dataProvider = "csv" )
public void testPrintCsvRowToLog( SelectItem[] cols, Object[] data ) {
    String theCols = Joiner.on("|").join( cols );
    String aRow = Joiner.on("|").join( data );
    logger.info( theCols ); 
    logger.info( aRow ); 
}

Answer 4

You cannot use TestNg Parameter and Dataprovider for @Test annotation.

Better to use DataProvider annotation for BeforeTest/Beforeclass annotation and Use @Parameter annotataion for @Test annotation