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
Following is the sample test which will use the excel class to make test data-driven(parametrized)
DemoTest.rb
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
1 comments:
how would you loop the script to run through multiple rows from the xls
Post a Comment