WATIR: Parameterization with Excel

Ruby has already a functionality under win32ole package to make your test data-driven.
It has functions to read and write data from and to cell. if you need to read the data from a column and 10th row the code will the something like below.
value = worksheet.Range("a10").Value

However the problem with above code is, when we use that in automation we need to remember what data does "a" column contains, is it username or password or error messages and which scenario does 10th row contain, valid or invalid ?

To get rid of all this question i have write the excel class which will read the data based on given column name(data field name) and row name(scenario name).

The excel file we will access should look like below, where first row should contain data field names and first column should contain scenario names.

Following is the excel class which will read the data based on given data field name and scenario name
Excel.rb

require 'win32ole'

=begin
Purpose: Class to read Excel file given the column name and scenario name
Author:  Gaurang Shah
=end

class Excel

  @@map = Array["a","b","c","d","e","f","g","h","i","j","k","l","m","o","p","q","r","s","t","u","v","w","x","y","z"]
  #provide the path of the EXCEL file.. i.e. c:/demo.xls
  #Provide the worksheet number if you have multiple worksheets
  #If you will not provide the worksheet number by default it will take 1
  def initialize(path, workSheetNumber=1)
    @path=path
    @workSheetNumber=workSheetNumber
  end

  def openExcel
    # puts "inside excel"
    @excel = WIN32OLE::new("excel.Application")

    @workbook = @excel.Workbooks.Open("#{File.dirname(__FILE__)}/#{@path}")

    @worksheet = @workbook.WorkSheets(@workSheetNumber)
    # just to make sure macros are executed, if your sheet doesn't have macros you can skip this step.
    @worksheet.Select

    column = @worksheet.range("a1:a100").Value
    index=0
    while(column[index].to_s != "") do
      index = index+1
    end

    @scenarios = Array.new(index)
    for i in(0...index)do
      @scenarios[i] = column[i]
    end
  end

  #Returns the EXCEL cell value based on provided ScenarioName and columnName
  def getValue(scenarioName,columnName)

    openExcel()
    #get the first line of excel
    columnNames = @worksheet.Rows(1).value[0]

    #Find out the total number of columns
    name=0
    while(columnNames[name+=1] != columnName) do
    end
    #puts name

    begin
      colData = @worksheet.Range("#{@@map[name]}1:#{@@map[name]}100").Value
    rescue
      return "Data Not Found: Invalid Column"
    end

    totalScenarios  = @scenarios.length
    for index in(0..totalScenarios) do
      if(@scenarios[index].to_s == scenarioName) then
        break;
      end
    end

    if(index >= totalScenarios) then
      return "Data Not Found: Invalid Scenario"
    end
    index+=1;

    value = @worksheet.range("#{@@map[name]}#{index}").value
    @workbook.close
    @excel.Quit

    if(value.nil?) then
      return ""
    end
    return value
  end
end

Following is the sample test which will use the excel class to make test data-driven(parametrized)
DemoTest.rb

require 'test/unit'
require 'rubygems'
require 'watir'
require 'Excel'

class DemoTest < Test::Unit::TestCase
  def setup
    @browser = Watir::Browser.new
    @browser.goto("http://gmail.com")
    @browser.maximize
  end

  def test_gmail
    readExcel = Excel.new("gmail.xls")
    puts readExcel.getValue("noPassword","username")
    @browser.text_field(:id,"Email").set(readExcel.getValue("noPassword","username"))
    @browser.text_field(:id,"Passwd").set(readExcel.getValue("noPassword","password"))
    @browser.button(:id,"signIn").click()
    assert((@browser.text.include? "Enter your password."),"Verify Error Messages")
  end

  def teardown
    @browser.close()
  end
end