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, range_spec) ⇒ Object 



179
180
181
182
183
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
 
# File 'lib/testcentricity_web/data_objects/excel_helper.rb', line 179

def self.read_range_data(file, sheet, range_spec)
  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 range_spec parameter
  found      = []
  row_number = 0
  work_sheet.each do |row|
    if row[column_number] == range_spec
      found.push(row_number)
    elsif !found.empty?
      break
    end
    row_number += 1
  end
  raise "Could not find a row named '#{range_spec}' 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, row_spec, columns = nil) ⇒ Object 



121
122
123
124
125
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
 
# File 'lib/testcentricity_web/data_objects/excel_helper.rb', line 121

def self.read_row_data(file, sheet, row_spec, 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 row_spec is a string then we have to find a matching row name
  if row_spec.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 row_spec parameter
    found = false
    row_number = 0
    work_sheet.each do |row|
      if row[column_number] == row_spec
        found = true
        break
      end
      row_number += 1
    end
    raise "Could not find a row named '#{row_spec}' in worksheet #{sheet}" unless found
    data = work_sheet.row(row_number)
    # if row_spec is a number then ensure that it doesn't exceed the number of available rows
  elsif row_spec.is_a? Numeric
    raise "Row # #{row_spec} is greater than number of rows in worksheet #{sheet}" if row_spec > work_sheet.last_row_index
    data = work_sheet.row(row_spec)
  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, row_spec, columns = nil) ⇒ Object 



55
56
57
58
59
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
 
# File 'lib/testcentricity_web/data_objects/excel_helper.rb', line 55

def self.read_row_from_pool(file, sheet, row_spec, 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}:#{row_spec}"
  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 row_spec parameter
    found      = []
    row_number = 0
    work_sheet.each do |row|
      if row[column_number] == row_spec
        found.push(row_number)
      elsif !found.empty?
        break
      end
      row_number += 1
    end
    raise "Could not find a row named '#{row_spec}' 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

.row_spec_exists?(file, sheet, row_spec) ⇒ Boolean

Returns:

  • (Boolean) 


23
24
25
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
 
# File 'lib/testcentricity_web/data_objects/excel_helper.rb', line 23

def self.row_spec_exists?(file, sheet, row_spec)
  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 row_spec is a string then we have to find a matching row name
    if row_spec.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 row_spec parameter
      exists = false
      work_sheet.each do |row|
        if row[column_number] == row_spec
          exists = true
          break
        end
      end
    end
  end
  exists
end

.worksheet_exists?(file, sheet) ⇒ Boolean

Returns:

  • (Boolean) 


8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
# File 'lib/testcentricity_web/data_objects/excel_helper.rb', line 8

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

.write_row_data(file, sheet, row_spec, row_data) ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
 
# File 'lib/testcentricity_web/data_objects/excel_helper.rb', line 215

def self.write_row_data(file, sheet, row_spec, row_data)
  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)
  # find a matching row name
  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 row_spec parameter
  found = false
  row_number = 0
  work_sheet.each do |row|
    if row[column_number] == row_spec
      found = true
      break
    end
    row_number += 1
  end
  raise "Could not find a row named '#{row_spec}' in worksheet #{sheet}" unless found
  # iterate through the row_data Hash
  row_data.each do |column, value|
    column_number = 0
    found = false
    # find the column heading that matches the specified column name
    headings.each do |heading|
      if heading == column
        found = true
        break
      end
      column_number += 1
    end
    raise "Could not find a column named '#{column}' in worksheet #{sheet}" unless found
    # set the value of the specified row and column
    work_sheet.rows[row_number][column_number] = value
  end
  # iterate through all worksheets so that all worksheets are saved in new Excel document
  worksheets = work_book.worksheets
  worksheets.each do |worksheet|
    headings = worksheet.row(0)
    worksheet.rows[0][0] = headings[0]
  end
  # write all changes to new Excel document
  outfile = file.gsub(File.basename(file), 'output.xls')
  work_book.write outfile
  # delete original Excel document
  File.delete(file)
  # rename new Excel document, replacing the original
  File.rename(outfile, file)
end