Easily define Watir tests in excel, OO, wikis and Google docs using Roo

I spent this evening playing with Roo, the ruby library for reading data from spreadsheets and I am very impressed. In a very small amount of time I was able to define tests in four different forms/places and could execute my tests from each of these:

  • An Excel file (.xls): stored locally
  • An OpenOffice (.ods): stored locally
  • An Excel file (.xls) stored in a Confluence wiki page with Confluence Office Connector; and
  • A Google Docs spreadsheet.

The great thing about Roo is that you don’t actually need Excel; Roo simply reads the file, unlike the ruby Excel COM WIN32 API I have used previously.

The spreadsheet (embedded in Confluence) looks like this:

excel-in-confluence

The cool thing about embedding it in Confluence is that you can click the title of the spreadsheet to edit it (in OpenOffice in my case).

I made some minor changes to my existing code that executed my depot tests from a wiki page, and it was as easy as that. A data driven Watir solution with four possible ways to define test cases. Cool.

You can find all the code needed below.


require 'watir'
require 'rubygems'
require 'roo'
require './Customer.rb'
require './Common.rb'

case ARGV[0]
when "excel"
	ss = Excel.new("watirmelon.xls")
when "wiki"
	ss = Excel.new("http://localhost:8080/download/attachments/2097153/watirmelon.xls")
when "gdocs"
	ss = Google.new("http://spreadsheets.google.com/ccc?key=pEcLrW3b2djraE8JF_2fJWA")
else
	ss = Openoffice.new("watirmelon.ods")
end

ss.default_sheet = ss.sheets.first
ss.first_row.upto(ss.last_row) do |line|
	if ss.cell(line,1).strip != "Function" then #We have an executable test
		begin
			module_name = ss.cell(line,1).strip
			method_name = ss.cell(line,2).downcase.strip.sub(' ','_') # automatically determine function name based upon method name.
			comments = ss.cell(line,3).strip
			expected_outcome = ss.cell(line,4).strip
			expected_error = ss.cell(line,5).strip
			required_module = Kernel.const_get(module_name)
			required_method = required_module.method(method_name)
			arity = required_method.arity() # this is how many arguments the method requires, it is negative if a 'catch all' is supplied.
			arity = ((arity * -1) - 1) if arity < 0
			parameters = []
			1.upto(arity) do |p|
				parameters.push(ss.cell(line,p+5))
			end
			actual_outcome, actual_output = required_method.call(*parameters)
			# determine the result.
			if (expected_outcome = 'Success') and actual_outcome then
			    result = "PASS"
			elsif (expected_outcome = 'Error') and (not actual_outcome) and (expected_error = actual_output) then
			    result = "PASS"
			else
			    result = "FAIL"
			end
			puts "\nRunning Test: #{method_name} for #{module_name}."
			puts "Expected Outcome: #{expected_outcome}."
			puts "Expected Error: #{expected_error}."
			puts "Actual Outcome: #{actual_outcome}."
			puts "Actual Output: #{actual_output}."
			puts "RESULT: #{result}"
		rescue
			puts "An error occurred: #{$!}"
		end
	end
end

See the full test code below the break.

Common.rb


module Common
    def Common.find_or_start_browser(title, url)
        begin
            browser = Watir::IE.attach(:title, title)
            browser.goto(url)
        rescue Watir::Exception::NoMatchingWindowFoundException
            browser = Common.start_new_browser(url)
        end
        return browser
    end

    def Common.start_new_browser(url)
        browser = Watir::IE.start(url)
        browser.speed = :fast
        return browser
    end

    def Common.close_browsers(titles)
        for title in titles
            Common.close_browser(title)
        end
        return true
    end

    def Common.close_browser(title)
        begin
            browser = Watir::IE.attach(:title, title)
            browser.close
        rescue Watir::Exception::NoMatchingWindowFoundException
        end
        return true
    end

end
 

Customer.rb


module Customer

    URL = 'http://localhost:3000/store/'

    # Description::        Adds a book named 'bookTitle' to cart
    def Customer.add_book(bookTitle)
        browser = Common.find_or_start_browser('Pragprog Books Online Store', URL)
        # Check if title is already in cart
        browser.link(:text,'Show my cart').click

        prevCountInCart = 0
        prevCartTotal = 0.00
        if not browser.div(:text,'Your cart is currently empty').exist? then
            # We have a non-empty cart
            for row in browser.table(:index,1)
                if row[2].text == bookTitle then
                    prevCountInCart = row[1].text.to_i
                    break
                end
            end
            prevCartTotal = browser.cell(:id, 'totalcell').text[1..-1].to_f #remove $ sign
            browser.link(:text, 'Continue shopping').click
        end

        found = false
        1.upto(browser.divs.length) do |index|
            if (browser.div(:index,index).attribute_value('className') == 'catalogentry') and (browser.div(:index,index).h3(:text,bookTitle).exists?) then
                browser.div(:index,index).link(:class,'addtocart').click
                found = true
                break
            end
        end
        if not found then
            return false,'Could not locate title in store'
        end

        newCountInCart = 0       
        newCartTotal = 0.00
        for row in browser.table(:index,1)
            if row[2].text == bookTitle then
                newCountInCart = row[1].text.to_i
                break
            end
        end
        newCartTotal = browser.cell(:id, 'totalcell').text[1..-1].to_f # remove $ sign
        # TODO: Assertions around totals

        browser.link(:text, 'Continue shopping').click
        return true,''
    end

    def Customer.check_out(customerName, customerEmail, customerAddress, customerPaymentMethod)
        browser = Common.find_or_start_browser('Pragprog Books Online Store', URL)
        browser.link(:text,'Show my cart').click
        if browser.div(:text,'Your cart is currently empty').exist? then
            return false,'Your cart is currently empty'
        end
        browser.link(:text,"Checkout").click
        # Assert total value
        #b.cell(:id, 'totalcell').text.should == '$59.90'
        browser.text_field(:id, 'order_name').set(customerName)
        browser.text_field(:id, 'order_email').set(customerEmail)
        browser.text_field(:id, 'order_address').set(customerAddress)
        begin
            browser.select_list(:id, 'order_pay_type').select(customerPaymentMethod)
        rescue Watir::Exception::NoValueFoundException
            puts 'WARNING: could not locate customer payment method in drop down list: '+customerPaymentMethod
        end
        browser.button(:name, 'commit').click
        if browser.div(:id,'errorExplanation').exist? then
            error = browser.div(:id,'errorExplanation').li(:index,1).text
            browser.link(:text,'Continue shopping').click
            return false, error
        end
        return true,''
    end

    def Customer.empty_cart()
        browser = Common.find_or_start_browser('Pragprog Books Online Store', URL)
        puts 'INFO: Found Browser OK'
        browser.link(:text,"Show my cart").click
        if not browser.div(:text,"Your cart is currently empty").exist? then
            browser.link(:text,'Empty cart').click
            puts 'OK: Cart is now empty.'
            #TODO Assert message - cart is now empty
        end
        puts 'OK: Cart was never empty.'   
        return true,''
    end
end

Author: Alister Scott

Alister is an Excellence Wrangler for Automattic.

6 thoughts on “Easily define Watir tests in excel, OO, wikis and Google docs using Roo”

  1. Hi Alister,
    Am using Roo to connect to open office and i have a method defined in common_utils.rb (where we keep commonly used methods)to connect to a particular sheet. sheet_name is a constant that will be passed from the testcase.rb file. ‘tc’ is the testcase number which is passed from testcase.rb file

    def get_input_data_oo(tc, sheet_name)
          oo = Openoffice.new(INPUT_OO)
          oo.default_sheet = sheet_name
          header_row = oo.first_row  
          column = oo.first_column
          row = header_row + 1
        
        #find the row that has data for this test case
        
        while  oo.cell(row,column) != tc
          row += 1
        end
          
      test_data = { } #create a new hash
       
      #Populate the hash
       while  column !=(oo.last_column)
       data = oo.cell(header_row, column)#key of the hash
       value = oo.cell(row,column)  #value of the hash
       test_data[data] = value
       column = column.next 
      end
        return test_data
      end
    
    

    test_data is the hash that will be used to populate the fields in the testcase.rb file.

    Problem is when i run many testcases as a suite, i get the following error: running them individually works fine

    error:
    RangeError: RangeError
    c:/ruby/lib/ruby/gems/1.8/gems/roo-1.3.5/lib/roo/generic_spreadsheet.rb:24:in `default_sheet=’

    any idea of what is happening?

    Like

    1. I can’t reproduce your error. I tried this:

      require 'roo'
      
      def get_input_data_oo(tc, sheet_name)
        oo = Openoffice.new('./test.ods')
        oo.default_sheet = sheet_name
        header_row = oo.first_row
        column = oo.first_column
        row = header_row + 1
      
        #find the row that has data for this test case
      
        while oo.cell(row,column) != tc
          row += 1
        end
      
        test_data = { } #create a new hash
      
        2.upto(oo.last_column) do |col|
          data = oo.cell(header_row, col)#key of the hash
          value = oo.cell(row,col) #value of the hash
          test_data[data] = value
        end
      
        return test_data
      end
      
      puts get_input_data_oo('T3', 'Sheet1')
      puts get_input_data_oo('T4', 'Sheet1')
      puts get_input_data_oo('T1', 'Sheet1')
      puts get_input_data_oo('T2', 'Sheet1')
      
      

      and it works fine.

      Note, in your script, you are reading the first column also. Is this intentional.

      Like

  2. Installed ruby 1.8.7 (2010-08-16 patchlevel 302) [i386-mingw32]
    Requiring ‘rubygems’ returned ‘false’
    roo seemed to have dependencies for ‘zip’ and ‘google-spreadsheet-ruby’before requiring ‘roo’ returned true.

    Keep getting the following error trying to declare a Google Doc Spreadsheet:

    irb(main):001:0> require ‘roo’
    => true
    irb(main):002:0> ss = Google.new(“https://spreadsheets.google.com/ccc?key=0AsviuyIhEW9QdHN4VDFhY2FOabcdefg5aEs3eGtnQXc&hl=en#gid=0”)
    NoMethodError: private method `gsub’ called for nil:NilClass
    from C:/Ruby187/lib/ruby/1.8/cgi.rb:342:in `escape’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:95:in `encode_query’
    from (irb):2:in `map’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:95:in `each’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:95:in `map’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:95:in `encode_query’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:336:in `authenticate’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:148:in `login’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:127:in `login’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:22:in `login’
    from C:/Ruby187/lib/ruby/gems/1.8/gems/roo-1.9.3/lib/roo/google.rb:35:in `initialize’
    from (irb):2:in `new’
    from (irb):2
    irb(main):003:0>

    Like

      1. Hi Alister,
        The issue above was actually just a problem with my environment variables for user/password. Now roo seems not to like my URI for some reason (note- I changed the key for privacy):

        C:\sstest>sstest gdocs
        C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:280:in `request’: Response code 400 for get https://spreadsheets.google.com/feeds/worksheets/https://spreadsheets.google.com/ccc?key=abcdefgN2pLpl9hK7xkgAw&hl=en#gid=0/private/full: Invalid request URI (GoogleSpreadsheet::Error)
        from C:/Ruby187/lib/ruby/gems/1.8/gems/google-spreadsheet-ruby-0.1.2/lib/google_spreadsheet.rb:426:in `worksheets’
        from C:/Ruby187/lib/ruby/gems/1.8/gems/roo-1.9.3/lib/roo/google.rb:37:in `initialize’
        from C:/sstest/sstest.rb:14:in `new’
        from C:/sstest/sstest.rb:14

        Like

  3. Turns out constructing a proper URI just required the key (I had included some additional extraneous info).

    ss = Google.new(“JustMyGoogleKeyHere”)

    Now I can successfully retrieve Google Doc Spreadsheet data so long as the cell does not contain Unicode text (which is unfortunately what I need to do).

    Like

Comments are closed.