TestNg data provider with Excel

[Yamini Mudliyar]

Hi all,

I am a beginner with Selenium and have started with Selenium Web driver. I was trying to read parameters from excel via Data Provider and confused of how to write selenium code for this. i have written script for loggin and it worked but the next steps are bit confusing eg: for element like linktext and the rest how will it work.

Please someone help me with this.

Thanks,
Yamini Mudliya

[djangofan]

Here is how I do it:

 

  @DataProvider( name = "local", parallel = false )
  public Object[][] getTestData() {
    String env = System.getProperty( "testenv" );
    Assert.assertTrue( env != null, "The 'testenv' environment variable is null." );
    Assert.assertTrue( env.length() > 0, "Maven needs '-Dtestng.testenv' arg passed in order to set the 'testenv' environment variable." );
    DataCSV localCsv = new DataCSV( filename );
    return localCsv.getCsvData( env );
  }

 

And the supporting code:

 

import java.io.File;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.metamodel.DataContext;
import org.apache.metamodel.DataContextFactory;
import org.apache.metamodel.csv.CsvConfiguration;
import org.apache.metamodel.data.DataSet;
import org.apache.metamodel.data.Row;
import org.apache.metamodel.schema.Schema;
import org.apache.metamodel.schema.Table;
import org.testng.Assert;

public class DataCSV extends Data { 

  private File csvFile;
  private String envName;
  private String randomName;
  private String[] columnNames;

  public DataCSV( String fileLoc )
  {
    super();
    log = new Log();
   
    log.logInfo( "Loading CSV file from '" + fileLoc + "'." );
    setCSVInputFile( fileLoc );
    log.logInfo( "Finished running DataCSV class constructor." );
  }
 
  public Log getLog( Log log )
  {
    log.logInfo("Will not return log instance that was initially passed into DataCSV.class from the outside.");
    return null;
  }
 
  public void setLog(Log log)
  {
    this.log = log;
  }

  public Object[][] getCsvData()
  { 
    CsvConfiguration conf = new CsvConfiguration( 1 ); //row 1 is the column names
    DataContext csvContext = DataContextFactory.createCsvDataContext( csvFile, conf );
    Schema schema = csvContext.getDefaultSchema();
    Table[] tables = schema.getTables();
    Table table = tables[0]; // table is basically the name of the file as a String
    setColumnNames( table.getColumnNames() );
    DataSet dataSet = csvContext.query()
        .from( table )
        .selectAll()
        .where( "runRow" ).eq( "Y" )
        .execute();
    List<Row> rows = dataSet.toRows();
    Object[][] myArray = getRowsAsObjectArray( rows );
    return myArray;
  }
 
  /**
   * Gets a set of Map objects that represent the Rows of a CSV file.
   * Requires first column be Y or N
   * Expects second column be a browser string value.  Parse it outside this class with parseBrowser method.
   * @param rows
   * @return
   */
  public Object[][] getRowsAsObjectArray( List<Row> rows ) {
    Object[][] myArray = new Object[rows.size()][2];
    int i = 0;
    Object[] cols = this.getColumnNames();
    String[] colsArray = Arrays.copyOf( cols, cols.length, String[].class );
    log.logInfo( "Processing rows with data provider..." );
    for ( Row r : rows ) {
      Object[] data = r.getValues();
      for ( int j = 0; j < colsArray.length; j++ ) {
        if ( data[j] == null ) {
          data[j] = ""; // force empty string where there are NULL values
        } else {
          data[j] = data[j].toString().trim();
        }
      }
      String[] dataArray = Arrays.copyOf( data, data.length, String[].class );
      Map<String, String> paramMap = new HashMap<String,String>();
      Assert.assertTrue( colsArray.length == dataArray.length, "Number of columns doesn't match data on row index: " + i );
      for ( int s = 0; s < colsArray.length; s++ ) {
        for ( int t = 0; t < dataArray.length; t++ ) {
          //log.info( "Add arg [" + colsArray[t] + "|" + dataArray[t] + "]" );
          paramMap.put( colsArray[t], dataArray[t] );
        }
      }
      myArray[i][0] = parseBrowser( paramMap.get( "browser" ) ); // 1st param
      myArray[i][1] = paramMap; // 2nd param
      log.logInfo( "----- Row " + (i+1) + " -----" );
      for ( String key: paramMap.keySet() ) {
        log.logInfo( "[" + key + "|" + paramMap.get( key ) + "]" );
      }
      log.logInfo( "-----------------------------" );
      i++;
    }
    return myArray;
  }

  private void setCSVInputFile( String loc ) {
        setCsvFile( new File( loc ) );       
  } 

  public void setCsvFile( File csvFile ) {
    this.csvFile = csvFile;
    Assert.assertTrue( this.csvFile.exists(), "The .csv file was not found at the location: " + this.csvFile.getAbsolutePath() );
  }

  public File getCsvFile() {
    return this.csvFile;
  }

  public String getEnvName() {
    return envName;
  }

  public void setEnvName(String envName) {
    this.envName = envName;
  }

  public String[] getColumnNames() {
    return columnNames;
  }

  public void setColumnNames(String[] columnNames) {
    this.columnNames = columnNames;
  }

  @Override
  protected Log getLog() {
    return this.log;
  }

}
 

[ymudliyar]

Hi,
Thanks for sharing this...Wanted to clarify one more thing.. Can in
excel/csv all the elments like xpaths and links also be read??
For eg: in excel/csv i have just put the element as xpath and then in
webdriver i just write click() function will this work and if this does work
then can you please give example.



Thanks,
Yamini Mudliyar



--
View this message in context: http://testng.1065351.n5.nabble.com/TestNg-data-provider-with-Excel-tp20205p20220.html
Sent from the testng-users mailing list archive at Nabble.com.

[ymudliyar]

Hi,
My apologies for posting this as i searched in for similar question but
cudnt find, My query is Can we also write Locators in Excel like Xpath, css
selectors etc and then using webdriver read those and accordingly execute.

for eg: driver.FindElement(By.xpath(here the xpath should be read from
excel)).click();
or driver.FindElement(By.link(Here the values or the link nam should be read
from excel)).submit();


Thanks,
Yamini



--
View this message in context: http://testng.1065351.n5.nabble.com/TestNg-data-provider-with-Excel-tp20205p20236.html

[Abhishek Bisht]

Yes yamini u can do that create an excel with element name and its locator and call it in a hash map with keys as element name and value as locator and then call that element value with key name on your keyword method

Regards
Abhishek Bisht

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to testng-users...@googlegroups.com.
To post to this group, send email to testng...@googlegroups.com.
Visit this group at http://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.

[ymudliyar]

Hi Abhishek thanks for sharing...can you also share one eg please.




--
View this message in context: http://testng.1065351.n5.nabble.com/TestNg-data-provider-with-Excel-tp20205p20257.html

[Yamini Mudliyar]

Hi all,


Please anyone help me.

Thanks and regards,

Yamini Mudliyar