Taking excel data and creating nested default data hash in Ruby

Question

I think what I'm doing is trying to create a nested hash. I'm trying to replace the file based default data with data from an excel spreadsheet. I need to assign the keys in DEFAULT_DATA to the appropriate value from the data_fill[] hash.. i.e. :insured_name_first => data_fill['fname'],

I toyed with just putting the data_fill hash in the populate method, but I want to be able to pass changes to default values where necessary

When it goes to load the file at the start of the test run I get:

undefined local variable or method `data_fill' for InsuredInformationPage:Class (NameError)

Here is the code I am using..

require 'gainweb_chrome'
require 'win32ole'
require 'roo'
require 'cgi'

class InsuredInformationPage
   include WatirHelper
   include GainwebChrome

  def initialize(browser)
    @browser = browser
    @state = get_state
    key = $excel.row(1)
    state_data = $excel.find(:all, :conditions => {'Policy_State' => "#{@state}"},:array => true)
    data_fill = Hash[key.zip state_data[0]]
  end

def populate(data = {})

page_data = DEFAULT_DATA.merge(data)
self.effective_date_value = page_data[:effective_date] if data.has_key?(:effective_date)
self.insured_name_first_enabled_check = page_data[:insured_name_first]
self.insured_name_middle_initial_enabled_check = page_data[:insured_name_middle_initial]
self.insured_name_last_enabled_check = page_data[:insured_name_last]
self.coinsured_name_first_enabled_check = page_data[:coinsured_name_first]
self.coinsured_name_middle_initial_enabled_check = page_data[:coinsured_name_middle_initial]
self.coinsured_name_last_enabled_check = page_data[:coinsured_name_last]
self.insured_birth_date_value = page_data[:insured_birth_date]
self.insured_gender_enabled_check = page_data[:insured_gender]
self.check_address_override if page_data[:address_override]=~/checked/i
self.address_line_one_enabled_check = page_data[:address_line_1]
self.address_line_two_enabled_check = page_data[:address_line_2]
self.city_enabled_check = page_data[:city]
self.state_enabled_check = page_data[:state]
self.zip5_enabled_check = page_data[:zip_code]
if not page_data[:has_former_address].empty?
  self.check_has_former_address if page_data[:has_former_address]=~/checked/i
  self.former_address = page_data[:former_address]
  self.former_address2 = page_data[:former_address2]
  self.former_city = page_data[:former_city]
  self.former_state = page_data[:former_state] unless page_data[:former_state].to_s.empty? 
  self.former_zip = page_data[:former_zip]
end
self.phone_number_areacode = page_data[:phone_number_areacode]
self.phone_number_prefix = page_data[:phone_number_prefix]
self.phone_number_suffix = page_data[:phone_number_suffix]
self.email_address = page_data[:email_address]
self.client_id = page_data[:client_id]

$policy_id = self.policy_id_element.value.to_s
end

def next_page
wait_for_elements(:next_button)
next_button
PageFactory.new(@browser).construct :policy_information_page
end

text_field(:effective_date,               :id => /txtEffectiveDate/)
element(:effective_date_compare,          :id => /lblPolEffDateCompare/)
text_field(:insured_name_first,           :id => /txtInsuredName_First/)
text_field(:insured_name_middle_initial,  :id => /txtInsuredName_Middle/)
text_field(:insured_name_last,            :id => /txtInsuredName_Last/)
text_field(:insured_birth_date,           :id => /txtBirthDate/)
select_list(:insured_gender,              :id => /lstGender/)
text_field(:coinsured_name_first,         :id => /txtCoinsuredName_First/)
text_field(:coinsured_name_middle_initial,:id => /txtCoinsuredName_Middle/)
text_field(:coinsured_name_last,          :id => /txtCoinsuredName_Last/)
text_field(:address_line_one,             :id => /StreetAddress/)
text_field(:address_line_two,             :id => /AddressLine2/)
text_field(:city,                         :id => /City/)
select_list(:state,                       :id => /State/)
text_field(:zip5,                         :id => /MainContent_AddressScrub_AS_Zipcode/)
text_field(:zip4,                         :id => /Zip4Textbox/)
button(:street_zip_lookup,                :id => /StreetZipButton/)
text_field(:validated,                    :id => /ValidatedTextbox/)
checkbox(:address_override,               :id => /AddressScrub_cbxOverride/)
image(:address_override_help,             :id => /AddressScrub_hbOverride/)
div(:address_override_help_div,           :id => /hbOverride_answer/)
checkbox(:has_former_address,             :id => /chkFormerAddress/)
image(:has_former_address_help,           :id => /AddressScrub_hbOverride/)
div(:has_former_address_help_div,         :id => /hbFormerAddress_answer/)
text_field(:former_address,               :id => /txtFormerAddress/)
text_field(:former_address2,              :id => /txtFormerAddressLine2/)
text_field(:former_city,                  :id => /txtFormerCity/)
select_list(:former_state,                :id => /FormerState/)
text_field(:former_zip,                   :id => /txtFormerZip/)
text_field(:phone_number_areacode,        :id => /txtAreaCode/)
text_field(:phone_number_prefix,          :id => /txtPrefix/)
text_field(:phone_number_suffix,          :id => /txtSufix/) #suffix is misspelled on the page
text_field(:email_address,                :id => /txtEmailAddress/)
text_field(:client_id,                    :id => /txtClientId/)
image(:client_id_help,                    :id => /hbClientID/)
div(:client_id_help_div,                  :id => /hbClientID_answer/)
text_field(:agent_notes,                  :id => /txtPolicyNotes/)
image(:agent_notes_help,                  :id => /hbAgentNotes/)
div(:agent_notes_help_div,                :id => /hbAgentNotes_answer/)
image(:close_button,                      :title => /Click to close/)
element(:policy_id,                       :id => /hidAS_PolicyID/)

DEFAULT_DATA ={
:effective_date => '',
:insured_name_first => 'Wile',
:insured_name_middle_initial => 'e',
:insured_name_last => 'Coyote',
:insured_birth_date => '03/24/1947',
:insured_gender => 'Male',
:coinsured_name_first => '',
:coinsured_name_middle_initial => '',
:coinsured_name_last => '',
:address_line_1 => '671 S High Street',
:address_line_2 => 'Apt C',
:city => 'Columbus',
:state => 'Ohio',
:zip_code => '43206',
:address_override => '',
:has_former_address => '',
:former_address => '212 Summitview Drive',
:former_address2 => 'Apt C',
:former_city => 'Lancaster',
:former_state => 'Ohio',
:former_zip => '43130',
:phone_number_areacode => '614',
:phone_number_prefix => '445',
:phone_number_suffix => '2593',
:email_address => '',
:client_id => 'wilddog1234',
:agent_notes => 'this space for rent',
:policy_id => 'foo'
}

Please indent your code properly.

Andrew Grimm
– Andrew Grimm

2011-11-23 01:59:40 +00:00
Commented Nov 23, 2011 at 1:59 — Andrew Grimm
– Andrew Grimm, Commented Nov 23, 2011 at 1:59

Aaron Henderson · Accepted Answer · 2013-07-23 06:55:53Z

Here is a handy method to convert the data on any tab of a spreadsheet into a hash with the first row as the key values.

def self.excel_to_hash(folder_name, file_name, tab_name)
    # Takes an excel file name and a tab name, and returns an array of stripped, transposed rows
    # Sample call:  @@models = excel_to_hash File.join(Rails.root,'db/meta/model_headers.xlsx'), 'models'
    rows = []
    file = File.open(File.join(folder_name, file_name), mode = 'r')
    excel = Excelx.new(file.path, nil, :ignore)
    excel.default_sheet = excel.sheets.index(tab_name) + 1
    header = excel.row(1)
    (2..excel.last_row).each do |i|
      next unless excel.row(i)[0]
      row = Hash[[header, excel.row(i)].transpose]      
      row.each_key{|x| row[x] = row[x].to_s.strip if row[x]}
      rows << row
    end
    return rows
  end

Then to call it...

    data_folder = File.join(Rails.root, 'vendor','gems','omni','db','data')
    data_file = "my_data_spreadsheet"
    tab_name = "sheet1"
    my_data_in_a_hash = excel_to_hash data_folder, data_file, tab_name

This has been validated up to Roo gem 1.10.2

SamuraiJack · Accepted Answer · 2011-07-22 20:24:44Z

0

I figured it out..

I moved the data load to the populate function and I changed the spreadsheet header row to match the key names, then converted the string value to a symbol and eliminated the default_data hash completely.

New code for that part looks like this:

def populate(data = {})
  @state = get_state
  key = $excel.row(1)
  get_data = $excel.find(:all, :conditions => {'Policy_State' => "#{@state}"},:array => true)
  state_data = Hash[key.zip get_data[0]]
  # Convert column header strings to keys
  state_data = state_data.inject({}){|memo,(k,v)| memo[k.to_sym] = v; memo}
  page_data = state_data.merge(data)

answered Jul 22, 2011 at 20:24

SamuraiJack

505 bronze badges

Collectives™ on Stack Overflow

Taking excel data and creating nested default data hash in Ruby

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related