Selenium - Parameterization with EXCEL

let's see how to make your selenium test data driven using excel. Using excel as a data driven has it's own advantage. The biggest advantage is it's in a Table format, means easy to understand and modify.
Now let's take the simple example of Login test where we have multiple scenarios like Valid Login, Wrong Password, Wrong Username and let's see how we will automate it using selenium and excel.

Note: Make sue the excel sheet where you write down the data in above format has name "login"

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

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

/**
 * @author Gaurang
 */
public class Excel {

 public static Connection con ;
 
 public static String dbURL =
     "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ= "+ "demo.xls;"
      + "DriverID=22;READONLY=false";
 public static  String  getValueFromExcel(String SheetName, String ColumnName, String Scenario) throws SQLException, ClassNotFoundException {
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

  Connection con = DriverManager.getConnection(dbURL);
    
   if(con == null)
    System.out.println("Not able to connect to MS EXCEL");
  Statement stmnt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  String sFormattedSheetName = "[" + SheetName + "$]";
  SheetName = sFormattedSheetName;
 
  String query = "Select "+ColumnName+" from "+SheetName+" Where TestScenario='"+Scenario+"'" ;
    
  ResultSet rs = stmnt.executeQuery( query );  
  
  rs.next(); //initially cursors is at -1 position
  String value = rs.getString(1);
  rs.close();
  
  return value;
 
 }
 
 @DataProvider(name = "fromExcel")
 public Object[][] createData1() {
  return new Object[][] {
    { "Valid Login" },
    { "Wrong Username"},
    {"Wrong Password"}
  };
 }
 
 @Test(dataProvider="fromExcel")
 public void dataDrivenUsingExcel(String scenarioName) throws SQLException, ClassNotFoundException {
  System.out.println("Scenario="+scenarioName+" UserName="+Excel.getValueFromExcel("login", "UserName", scenarioName));
  System.out.println("Scenario="+scenarioName+" Password="+Excel.getValueFromExcel("login", "Password", scenarioName));
 }
 
}