class TestCentricity::ExcelData

Public Class Methods

read_range_data(file, sheet, rangespec) click to toggle source
# File lib/testcentricity/data_objects/excel_helper.rb, line 184
def self.read_range_data(file, sheet, rangespec)
  raise "File #{file} does not exists" unless File.exist?(file)
  work_book  = Spreadsheet.open file
  work_sheet = work_book.worksheet sheet
  # get column headings from row 0 of worksheet
  headings = work_sheet.row(0)
  column_number = 0
  found = false
  headings.each do |heading|
    if heading == 'ROW_NAME'
      found = true
      break
    end
    column_number += 1
  end
  raise "Could not find a column named ROW_NAME in worksheet #{sheet}" unless found
  # find cell(s) in ROW_NAME column containing a string that matches the rangespec parameter
  found      = []
  row_number = 0
  work_sheet.each do |row|
    if row[column_number] == rangespec
      found.push(row_number)
    elsif !found.empty?
      break
    end
    row_number += 1
  end
  raise "Could not find a row named '#{rangespec}' in worksheet #{sheet}" if found.empty?

  result = []
  found.each do |row|
    result.push(read_row_data(file, sheet, row))
  end
  result
end
read_row_data(file, sheet, rowspec, columns = nil) click to toggle source
# File lib/testcentricity/data_objects/excel_helper.rb, line 126
def self.read_row_data(file, sheet, rowspec, columns = nil)
  raise "File #{file} does not exists" unless File.exist?(file)
  work_book  = Spreadsheet.open file
  work_sheet = work_book.worksheet sheet
  # get column headings from row 0 of worksheet
  headings = work_sheet.row(0)
  # if rowspec is a string then we have to find a matching row name
  if rowspec.is_a? String
    column_number = 0
    found = false
    headings.each do |heading|
      if heading == 'ROW_NAME'
        found = true
        break
      end
      column_number += 1
    end
    raise "Could not find a column named ROW_NAME in worksheet #{sheet}" unless found
    # find first cell in ROW_NAME column containing a string that matches the rowspec parameter
    found = false
    row_number = 0
    work_sheet.each do |row|
      if row[column_number] == rowspec
        found = true
        break
      end
      row_number += 1
    end
    raise "Could not find a row named '#{rowspec}' in worksheet #{sheet}" unless found
    data = work_sheet.row(row_number)
    # if rowspec is a number then ensure that it doesn't exceed the number of available rows
  elsif rowspec.is_a? Numeric
    raise "Row # #{rowspec} is greater than number of rows in worksheet #{sheet}" if rowspec > work_sheet.last_row_index
    data = work_sheet.row(rowspec)
  end

  # if no columns have been specified, return all columns
  columns = headings if columns.nil?
  # create results hash table
  result = Hash.new
  columns.each do |column|
    column_number = 0
    found = false
    headings.each do |heading|
      if column == heading
        value = data[column_number].to_s
        value = calculate_dynamic_value(value) if value.start_with? 'eval!'
        result[column] = value
        found = true
        break
      end
      column_number += 1
    end
    raise "Could not find a column named '#{column}' in worksheet #{sheet}" unless found
  end
  result
end
read_row_from_pool(file, sheet, rowspec, columns = nil) click to toggle source
# File lib/testcentricity/data_objects/excel_helper.rb, line 60
def self.read_row_from_pool(file, sheet, rowspec, columns = nil)
  raise "File #{file} does not exists" unless File.exist?(file)
  work_book  = Spreadsheet.open file
  work_sheet = work_book.worksheet sheet

  pool_spec_key = "#{sheet}:#{rowspec}"
  if @mru.key?(pool_spec_key)
    pool_spec = @mru[pool_spec_key]
    row_start = pool_spec[:start_row]
    row_end   = pool_spec[:num_rows]
    pool_rows = (row_start..row_start + row_end - 1).to_a
    mru_rows  = pool_spec[:used_rows]
    new_row   = pool_rows.sample.to_i
    if mru_rows.size == pool_spec[:num_rows]
      mru_rows = [new_row]
    else
      while mru_rows.include?(new_row)
        new_row = pool_rows.sample.to_i
      end
      mru_rows.push(new_row)
      mru_rows.sort!
    end

    pool_spec = {
        :start_row => row_start,
        :num_rows  => row_end,
        :used_rows => mru_rows
    }
  else
    # get column headings from row 0 of worksheet
    headings      = work_sheet.row(0)
    column_number = 0
    found         = false
    headings.each do |heading|
      if heading == 'ROW_NAME'
        found = true
        break
      end
      column_number += 1
    end
    raise "Could not find a column named ROW_NAME in worksheet #{sheet}" unless found
    # find cell(s) in ROW_NAME column containing a string that matches the rowspec parameter
    found      = []
    row_number = 0
    work_sheet.each do |row|
      if row[column_number] == rowspec
        found.push(row_number)
      elsif !found.empty?
        break
      end
      row_number += 1
    end
    raise "Could not find a row named '#{rowspec}' in worksheet #{sheet}" if found.empty?

    new_row   = found.sample.to_i
    pool_spec = {
        :start_row => found[0],
        :num_rows  => found.size,
        :used_rows => [new_row]
    }
  end
  @mru[pool_spec_key] = pool_spec

  read_row_data(file, sheet, new_row, columns)
end
rowspec_exists?(file, sheet, rowspec) click to toggle source
# File lib/testcentricity/data_objects/excel_helper.rb, line 26
def self.rowspec_exists?(file, sheet, rowspec)
  exists = false
  if worksheet_exists?(file, sheet)
    work_book  = Spreadsheet.open file
    work_sheet = work_book.worksheet sheet
    # get column headings from row 0 of worksheet
    headings = work_sheet.row(0)
    # if rowspec is a string then we have to find a matching row name
    if rowspec.is_a? String
      column_number = 0
      exists = false
      headings.each do |heading|
        if heading == 'ROW_NAME'
          exists = true
          break
        end
        column_number += 1
      end
      raise "Could not find a column named ROW_NAME in worksheet #{sheet}" unless exists
      # find first cell in ROW_NAME column containing a string that matches the rowspec parameter
      exists = false
      row_number = 0
      work_sheet.each do |row|
        if row[column_number] == rowspec
          exists = true
          break
        end
        row_number += 1
      end
    end
  end
  exists
end
worksheet_exists?(file, sheet) click to toggle source
# File lib/testcentricity/data_objects/excel_helper.rb, line 11
def self.worksheet_exists?(file, sheet)
  exists = false
  if File.exist?(file)
    work_book  = Spreadsheet.open file
    worksheets = work_book.worksheets
    worksheets.each do |worksheet|
      if worksheet.name == sheet
        exists = true
        break
      end
    end
  end
  exists
end

Private Class Methods

calculate_dynamic_value(value) click to toggle source
# File lib/testcentricity/data_objects/excel_helper.rb, line 222
def self.calculate_dynamic_value(value)
  test_value = value.split('!', 2)
  parameter = test_value[1].split('.', 2)
  case parameter[0]
  when 'Date'
    result = eval("Chronic.parse('#{parameter[1]}')")
  when 'FormattedDate', 'FormatDate'
    date_time_params = parameter[1].split(' format! ', 2)
    date_time = eval("Chronic.parse('#{date_time_params[0].strip}')")
    result = date_time.to_s.format_date_time("#{date_time_params[1].strip}")
  else
    if Faker.constants.include?(parameter[0].to_sym)
      result = eval("Faker::#{parameter[0]}.#{parameter[1]}")
    else
      result = eval(test_value[1])
    end
  end
  result.to_s
end