Class: TestCentricity::ExcelData

Inherits:
Object
  • Object
show all
Defined in:
lib/testcentricity_web/data_objects/excel_helper.rb

Class Method Summary collapse

Class Method Details

.read_range_data(file, sheet, rangespec) ⇒ Object 



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/testcentricity_web/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) ⇒ Object 



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/testcentricity_web/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) ⇒ Object 



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/testcentricity_web/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) ⇒ Boolean

Returns:

  • (Boolean) 


26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/testcentricity_web/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) ⇒ Boolean

Returns:

  • (Boolean) 


11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/testcentricity_web/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