DataProvider in TestNG to pass data from Excel using Java WebDriver

Question

I have a login page which contains username and password. I have a class XcelParserTestNGLogin to create, update and to load from Excel sheet method. And another class Login which is a TestNG class. I am using DataProvider to pass data from Excel. But I am getting Exception The data provider is trying to pass 4 parameters but the method takes 2.

Here is my code for TestNG:

public class Login {

    private static WebDriver driver;
    XcelParserTestNGLogin login1 = new XcelParserTestNGLogin();
    Object[][] data1;

    /*public Login() throws IOException, InterruptedException {
        FileInputStream fis = new FileInputStream("Data//LoginPage.xls");
        XcelParserTestNGLogin login1 = new XcelParserTestNGLogin(fis, "Login");

        //this.data1 = login1.loadFromSpreadsheet(fis, "Login");
    }*/

    @BeforeClass
    public void test() throws Exception {
        System.setProperty("webdriver.chrome.driver",
                "C:\\Chrome\\chromedriver_win_26.0.1383.0\\chromedriver.exe");
        driver = new ChromeDriver();
        driver.get("Any Url");
    }
    @DataProvider
     public Object[][] dp() throws IOException {
        //login1.fileName = "Data//Login.xls";
        //login1.sheetName = "Sheet1";
        FileInputStream fis = new FileInputStream("Data//LoginPage.xls");
        String sheetName = "Login";
        login1.loadFromSpreadsheet(fis,sheetName);
        return login1.getData();        
    }

    @Test(dataProvider = "dp")
    public void devLogin(String UserName,String PassWord) throws InterruptedException, IOException {

        driver.findElement(By.name("txtUserName")).sendKeys(UserName);
        driver.findElement(By.name("txtPwd")).sendKeys(PassWord);
        driver.findElement(By.name("btnSignIn")).click();
        Thread.sleep(5000);

        if (driver.findElement(By.linkText("DashBoard")).isDisplayed()) {
            List<String> arrayList = new ArrayList<String>();
            arrayList.add("Pass");
            HSSFWorkbook workbook = new HSSFWorkbook();
            login1.createSheet("Login", workbook, arrayList);
        } 
        else {
            try{
            Alert alert=driver.switchTo().alert();
            String alertText=alert.getText();

            Assert.assertEquals("invalid username or password,please try again",alertText);
            alert.accept();
            }catch(UnhandledAlertException e){
                e.printStackTrace();
            }
            List<String> arrayList = new ArrayList<String>();
            arrayList.add("Fail");
            HSSFWorkbook workbook = new HSSFWorkbook();
            login1.createSheet("Login", workbook, arrayList);
        }
    }
}

Here is my code for XcelParserTestNGLogin()

public class XcelParserTestNGLogin {
    private transient Object[][] data;
    String fileName,sheetName;

    public XcelParserTestNGLogin() {

    }

    public XcelParserTestNGLogin(InputStream excelInputStream, String sheetName)
            throws IOException {
        this.data = loadFromSpreadsheet(excelInputStream, sheetName);
    }

    public Object[][] getData() {
        return data;

    }

    Object[][] loadFromSpreadsheet(InputStream excelFile, String sheetName)
            throws IOException {
        // TODO Auto-generated method stub
        HSSFWorkbook workbook = new HSSFWorkbook(excelFile);
        Sheet sheet = workbook.getSheet(sheetName);

        int numberOfColumns = countNonEmptyColumns(sheet);
        int numberOfRows = sheet.getLastRowNum() + 1;

        data = new Object[numberOfRows - 1][numberOfColumns - 1];

        for (int rowNum = 1; rowNum < numberOfRows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (isEmpty(row)) {
                break;
            } else {
                for (int column = 1; column < numberOfColumns; column++) {
                    Cell cell = row.getCell(column);
                    if (cell == null
                            || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        data[rowNum - 1][column - 1] = "";
                    } else {
                        data[rowNum - 1][column - 1] = objectFrom(workbook,
                                cell);
                    }
                }
            }
        }

        return data;
    }

    private boolean isEmpty(Row row) {
        // TODO Auto-generated method stub
        Cell firstCell = row.getCell(0);
        boolean rowIsEmpty = (firstCell == null)
                || (firstCell.getCellType() == Cell.CELL_TYPE_BLANK);
        return rowIsEmpty;
    }

    /**
     * Count the number of columns, using the number of non-empty cells in the
     * first row.
     */
    private int countNonEmptyColumns(Sheet sheet) {
        // TODO Auto-generated method stub
        Row firstRow = sheet.getRow(0);
        return firstEmptyCellPosition(firstRow);
    }

    private int firstEmptyCellPosition(Row cells) {
        // TODO Auto-generated method stub
        int columnCount = 0;
        for (Cell cell : cells) {
            if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            }
            columnCount++;
        }
        return columnCount;
    }

    private Object objectFrom(HSSFWorkbook workbook, Cell cell) {
        // TODO Auto-generated method stub
        Object cellValue = null;
        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            cellValue = cell.getRichStringCellValue().getString();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cellValue = getNumericCellValue(cell);
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cellValue = cell.getBooleanCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            cellValue = evaluateCellFormula(workbook, cell);
        }

        return cellValue;
    }

    private Object getNumericCellValue(final Cell cell) {
        Object cellValue;
        if (DateUtil.isCellDateFormatted(cell)) {
            cellValue = new Date(cell.getDateCellValue().getTime());
        } else {
            cellValue = cell.getNumericCellValue();
        }
        return cellValue;
    }

    private Object evaluateCellFormula(final HSSFWorkbook workbook,
            final Cell cell) {
        FormulaEvaluator evaluator = workbook.getCreationHelper()
                .createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        Object result = null;

        if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            result = cellValue.getBooleanValue();
        } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            result = cellValue.getNumberValue();
        } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            result = cellValue.getStringValue();
        }

        return result;
    }
    public void updateExcel(final InputStream excelFile, String SheetName,
            List<String> list) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = null;
        if (workbook.getSheetIndex(SheetName) > 0) {
            sheet = workbook.getSheet(SheetName);
            if (list != null && list.size() != sheet.getLastRowNum()) {
                workbook.removeSheetAt(workbook.getSheetIndex(SheetName));
                createSheet(SheetName, workbook, list);
            } else {
                createSheet(SheetName, workbook, list);
            }
        }

    }
    void createSheet(String SheetName, HSSFWorkbook workbook, List<String> list) {
        // TODO Auto-generated method stub
        String[] Heading = {"UserName", "Password",
                "Result" };
        Sheet sheet = workbook.createSheet(SheetName);
        HSSFRow row = null;
        HSSFCell cell = null;

        row = (HSSFRow) sheet.createRow(0);
        for (int cellNum = 0; cellNum < Heading.length; cellNum++) {
            cell = row.createCell(cellNum);
            cell.setCellValue(Heading[cellNum]);
        }   
        for (int rowNum = 1; rowNum <= list.size(); rowNum++) {
            String[] cellVals = {"uname",
                    "pswd", list.get(rowNum - 1) };

            row = (HSSFRow) sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < cellVals.length; cellNum++) {
                cell = row.createCell(cellNum);
                if (!(cellNum == cellVals.length))
                    cell.setCellValue(cellVals[cellNum]);
                else
                    cell.setCellValue(true);
            }
        }
        try {
            FileOutputStream out = new FileOutputStream("Data//LoginPage.xls");
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    }

Answer 1

package testng;

import java.util.Hashtable;

import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class testNGParameterizationExcel {

  public static ExcelReader excel = null;

  @Test(dataProvider = "getData")
  public void testData(Hashtable<String, String> data) {
    // System.out.println(username + "---------" + password + "------------"
    // + is_correct);
    System.out.println(data.get("UserName"));
  }

  @DataProvider
  public static Object[][] getData() {
    if (excel == null) {
      excel = new ExcelReader("C:\\Users\\shaanu\\Desktop\\Java\\Test.xlsx");
    }

    String sheetName = "new";
    int rows = excel.getRowCount(sheetName);
    int columns = excel.getColumnCount(sheetName);
    Object[][] data = new Object[rows - 1][1];
    Hashtable<String, String> table = null;
    for (int rowNums = 2; rowNums <= rows; rowNums++) {
      table = new Hashtable<String, String>();
      for (int colNum = 0; colNum < columns; colNum++) {
        // data[rowNums-2][colNum] = excel.getCellData(sheetName,
        // colNum, rowNums);
        table.put(excel.getCellData(sheetName, colNum, 1), excel.getCellData(sheetName, colNum, rowNums));
        data[rowNums - 2][0] = table;
      }
    }

    return data;
  }
}

Answer 2

package testng;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;


import java.io.*;
import java.util.Calendar;


public class ExcelReader {

    public  String path;
    public  FileInputStream fis = null;
    public  FileOutputStream fileOut =null;
    private XSSFWorkbook workbook = null;
    private XSSFSheet sheet = null;
    private XSSFRow row   =null;
    private XSSFCell cell = null;

    public ExcelReader(String path) {

        this.path=path;
        try {
            fis = new FileInputStream(path);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheetAt(0);
            fis.close();
        } catch (Exception e) {

            e.printStackTrace();
        } 

    }


    // returns the row count in a sheet
    public int getRowCount(String sheetName){
        int index = workbook.getSheetIndex(sheetName);
        if(index==-1)
            return 0;
        else{
        sheet = workbook.getSheetAt(index);
        int number=sheet.getLastRowNum()+1;
        return number;
        }

    }



    // returns the data from a cell
    public String getCellData(String sheetName,String colName,int rowNum){
        try{
            if(rowNum <=0)
                return "";

        int index = workbook.getSheetIndex(sheetName);
        int col_Num=-1;
        if(index==-1)
            return "";

        sheet = workbook.getSheetAt(index);
        row=sheet.getRow(0);
        for(int i=0;i<row.getLastCellNum();i++){
            //System.out.println(row.getCell(i).getStringCellValue().trim());
            if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
                col_Num=i;
        }
        if(col_Num==-1)
            return "";

        sheet = workbook.getSheetAt(index);
        row = sheet.getRow(rowNum-1);
        if(row==null)
            return "";
        cell = row.getCell(col_Num);

        if(cell==null)
            return "";

        if(cell.getCellType()==Cell.CELL_TYPE_STRING)
              return cell.getStringCellValue();
        else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

              String cellText  = String.valueOf(cell.getNumericCellValue());
              if (HSSFDateUtil.isCellDateFormatted(cell)) {

                  double d = cell.getNumericCellValue();

                  Calendar cal =Calendar.getInstance();
                  cal.setTime(HSSFDateUtil.getJavaDate(d));
                    cellText =
                     (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
                   cellText = cal.get(Calendar.DAY_OF_MONTH) + "/" +
                              cal.get(Calendar.MONTH)+1 + "/" + 
                              cellText;



                 }



              return cellText;
          }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
              return ""; 
          else 
              return String.valueOf(cell.getBooleanCellValue());

        }
        catch(Exception e){

            e.printStackTrace();
            return "row "+rowNum+" or column "+colName +" does not exist in xls";
        }
    }

    // returns the data from a cell
    public String getCellData(String sheetName,int colNum,int rowNum){
        try{
            if(rowNum <=0)
                return "";

        int index = workbook.getSheetIndex(sheetName);

        if(index==-1)
            return "";


        sheet = workbook.getSheetAt(index);
        row = sheet.getRow(rowNum-1);
        if(row==null)
            return "";
        cell = row.getCell(colNum);
        if(cell==null)
            return "";

      if(cell.getCellType()==Cell.CELL_TYPE_STRING)
          return cell.getStringCellValue();
      else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

          String cellText  = String.valueOf(cell.getNumericCellValue());
          if (HSSFDateUtil.isCellDateFormatted(cell)) {
               // format in form of M/D/YY
              double d = cell.getNumericCellValue();

              Calendar cal =Calendar.getInstance();
              cal.setTime(HSSFDateUtil.getJavaDate(d));
                cellText =
                 (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
               cellText = cal.get(Calendar.MONTH)+1 + "/" +
                          cal.get(Calendar.DAY_OF_MONTH) + "/" +
                          cellText;



             }



          return cellText;
      }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
          return "";
      else 
          return String.valueOf(cell.getBooleanCellValue());
        }
        catch(Exception e){

            e.printStackTrace();
            return "row "+rowNum+" or column "+colNum +" does not exist  in xls";
        }
    }




    // returns true if data is set successfully else false
    public boolean setCellData(String sheetName,String colName,int rowNum, String data){
        try{
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);

        if(rowNum<=0)
            return false;

        int index = workbook.getSheetIndex(sheetName);
        int colNum=-1;
        if(index==-1)
            return false;


        sheet = workbook.getSheetAt(index);


        row=sheet.getRow(0);
        for(int i=0;i<row.getLastCellNum();i++){
            //System.out.println(row.getCell(i).getStringCellValue().trim());
            if(row.getCell(i).getStringCellValue().trim().equals(colName))
                colNum=i;
        }
        if(colNum==-1)
            return false;

        sheet.autoSizeColumn(colNum); 
        row = sheet.getRow(rowNum-1);
        if (row == null)
            row = sheet.createRow(rowNum-1);

        cell = row.getCell(colNum); 
        if (cell == null)
            cell = row.createCell(colNum);


        cell.setCellValue(data);

        fileOut = new FileOutputStream(path);

        workbook.write(fileOut);

        fileOut.close();    

        }
        catch(Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }// returns true if data is set successfully else false
    public boolean setCellData(String sheetName,String colName,int rowNum, String data,String url){

        try{
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);

        if(rowNum<=0)
            return false;

        int index = workbook.getSheetIndex(sheetName);
        int colNum=-1;
        if(index==-1)
            return false;


        sheet = workbook.getSheetAt(index);

        row=sheet.getRow(0);
        for(int i=0;i<row.getLastCellNum();i++){

            if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName))
                colNum=i;
        }

        if(colNum==-1)
            return false;
        sheet.autoSizeColumn(colNum); 
        row = sheet.getRow(rowNum-1);
        if (row == null)
            row = sheet.createRow(rowNum-1);

        cell = row.getCell(colNum); 
        if (cell == null)
            cell = row.createCell(colNum);

        cell.setCellValue(data);
        XSSFCreationHelper createHelper = workbook.getCreationHelper();

        //cell style for hyperlinks

        CellStyle hlink_style = workbook.createCellStyle();
        XSSFFont hlink_font = workbook.createFont();
        hlink_font.setUnderline(XSSFFont.U_SINGLE);
        hlink_font.setColor(IndexedColors.BLUE.getIndex());
        hlink_style.setFont(hlink_font);
        //hlink_style.setWrapText(true);

        XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
        link.setAddress(url);
        cell.setHyperlink(link);
        cell.setCellStyle(hlink_style);

        fileOut = new FileOutputStream(path);
        workbook.write(fileOut);

        fileOut.close();    

        }
        catch(Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }



    // returns true if sheet is created successfully else false
    public boolean addSheet(String  sheetname){     

        FileOutputStream fileOut;
        try {
             workbook.createSheet(sheetname);   
             fileOut = new FileOutputStream(path);
             workbook.write(fileOut);
             fileOut.close();           
        } catch (Exception e) {         
            e.printStackTrace();
            return false;
        }
        return true;
    }

    // returns true if sheet is removed successfully else false if sheet does not exist
    public boolean removeSheet(String sheetName){       
        int index = workbook.getSheetIndex(sheetName);
        if(index==-1)
            return false;

        FileOutputStream fileOut;
        try {
            workbook.removeSheetAt(index);
            fileOut = new FileOutputStream(path);
            workbook.write(fileOut);
            fileOut.close();            
        } catch (Exception e) {         
            e.printStackTrace();
            return false;
        }
        return true;
    }
    // returns true if column is created successfully
    public boolean addColumn(String sheetName,String colName){


        try{                
            fis = new FileInputStream(path); 
            workbook = new XSSFWorkbook(fis);
            int index = workbook.getSheetIndex(sheetName);
            if(index==-1)
                return false;

        XSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        sheet=workbook.getSheetAt(index);

        row = sheet.getRow(0);
        if (row == null)
            row = sheet.createRow(0);


        if(row.getLastCellNum() == -1)
            cell = row.createCell(0);
        else
            cell = row.createCell(row.getLastCellNum());

            cell.setCellValue(colName);
            cell.setCellStyle(style);

            fileOut = new FileOutputStream(path);
            workbook.write(fileOut);
            fileOut.close();            

        }catch(Exception e){
            e.printStackTrace();
            return false;
        }

        return true;


    }// removes a column and all the contents
    public boolean removeColumn(String sheetName, int colNum) {
        try{
        if(!isSheetExist(sheetName))
            return false;
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);
        sheet=workbook.getSheet(sheetName);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
        XSSFCreationHelper createHelper = workbook.getCreationHelper();
        style.setFillPattern(HSSFCellStyle.NO_FILL);



        for(int i =0;i<getRowCount(sheetName);i++){
            row=sheet.getRow(i);    
            if(row!=null){
                cell=row.getCell(colNum);
                if(cell!=null){
                    cell.setCellStyle(style);
                    row.removeCell(cell);
                }
            }
        }
        fileOut = new FileOutputStream(path);
        workbook.write(fileOut);
        fileOut.close();
        }
        catch(Exception e){
            e.printStackTrace();
            return false;
        }
        return true;

    }


  // find whether sheets exists 
    public boolean isSheetExist(String sheetName){
        int index = workbook.getSheetIndex(sheetName);
        if(index==-1){
            index=workbook.getSheetIndex(sheetName.toUpperCase());
                if(index==-1)
                    return false;
                else
                    return true;
        }
        else
            return true;
    }


    // returns number of columns in a sheet 
    public int getColumnCount(String sheetName){
        // check if sheet exists
        if(!isSheetExist(sheetName))
         return -1;

        sheet = workbook.getSheet(sheetName);
        row = sheet.getRow(0);

        if(row==null)
            return -1;

        return row.getLastCellNum();



    }


    //String sheetName, String testCaseName,String keyword ,String URL,String message
    public boolean addHyperLink(String sheetName,String screenShotColName,String testCaseName,int index,String url,String message){


        url=url.replace('\\', '/');
        if(!isSheetExist(sheetName))
             return false;

        sheet = workbook.getSheet(sheetName);

        for(int i=2;i<=getRowCount(sheetName);i++){
            if(getCellData(sheetName, 0, i).equalsIgnoreCase(testCaseName)){

                setCellData(sheetName, screenShotColName, i+index, message,url);
                break;
            }
        }


        return true; 
    }
    public int getCellRowNum(String sheetName,String colName,String cellValue){

        for(int i=2;i<=getRowCount(sheetName);i++){
            if(getCellData(sheetName,colName , i).equalsIgnoreCase(cellValue)){
                return i;
            }
        }
        return -1;

    }


    // to run this on stand alone
    public static void main(String arg[]) throws IOException{


        ExcelReader datatable = null;


             datatable = new ExcelReader("C:\\CM3.0\\app\\test\\Framework\\AutomationBvt\\src\\config\\xlfiles\\Controller.xlsx");
                for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){
                    System.out.println(datatable.getCellData("TC5", col, 1));
                }
    }


}

Answer 3

Remember length of first square bracket from left of your object array indicates how many times your test method will be invoked.

Length of second square bracke indicates how many arguments your test methods should have. As per your test method's signature your array declaration should be obj = new Object[maxRows][2].