Module: RubyFromExcel::ExcelFunctions

Included in:
FunctionCompiler
Defined in:
lib/formulae/run/excel_functions.rb

Instance Method Summary collapse

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(method, *arguments, &block) ⇒ Object 



89
90
91
92
93
94
95
 
# File 'lib/formulae/run/excel_functions.rb', line 89

def method_missing(method,*arguments, &block)
  return super unless arguments.empty?
  return super unless block == nil
  return find_or_create_worksheet(method.to_s) if method.to_s =~ /sheet\d+/
  return super unless method.to_s =~ /[a-z]+\d+/
  0.0.extend(Empty)
end

Instance Method Details

#a(start_cell, end_cell) ⇒ Object 



452
453
454
 
# File 'lib/formulae/run/excel_functions.rb', line 452

def a(start_cell,end_cell)
  result_cache[[:a,start_cell,end_cell]] ||= Area.new(self,start_cell,end_cell)
end

#abs(value) ⇒ Object 



292
293
294
295
296
 
# File 'lib/formulae/run/excel_functions.rb', line 292

def abs(value)
  return value if iserr(value)
  return :value unless value.respond_to?(:abs)
  value.abs
end

#average(*args) ⇒ Object 



330
331
332
333
334
335
336
 
# File 'lib/formulae/run/excel_functions.rb', line 330

def average(*args)
  total = sum(*args)
  number = count(*args)
  return total if iserr(total)
  return number if iserr(number)
  total / number
end

#c(start_column_number, end_column_number) ⇒ Object 



460
461
462
 
# File 'lib/formulae/run/excel_functions.rb', line 460

def c(start_column_number,end_column_number)
  result_cache[[:r,start_column_number,end_column_number]] ||= Columns.new(self,start_column_number,end_column_number)
end

#calculate_index_formula(area, row_number, column_number = nil, method = :index) ⇒ Object 



395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
 
# File 'lib/formulae/run/excel_functions.rb', line 395

def calculate_index_formula(area,row_number, column_number = nil, method = :index)
  return area if iserr(area)
  return row_number if iserr(row_number)
  if area.single_row?
    return area.send(method,row_number,column_number) if column_number
    return area.send(method,1,row_number)
  elsif area.single_column?
    return area.send(method,row_number,column_number) if column_number
    return area.send(method,row_number,1)
  else
    return :ref unless row_number && column_number
    return area.column(column_number-1) if row_number == 0.0
    return area.row(row_number-1) if column_number == 0.0
    return area.send(method,row_number,column_number)
  end
end

#calculate_match(lookup_value, lookup_array, match_type = 1.0) ⇒ Object 



353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
 
# File 'lib/formulae/run/excel_functions.rb', line 353

def calculate_match(lookup_value,lookup_array,match_type = 1.0)
  return lookup_value if iserr(lookup_value)
  return lookup_array if iserr(lookup_array)
  return match_type if iserr(match_type)
  lookup_value = lookup_value.downcase if lookup_value.respond_to?(:downcase)
  case match_type      
  when 0, 0.0, false
    lookup_array.each_with_index do |item,index|
      item = item.downcase if item.respond_to?(:downcase)
      return index+1 if lookup_value == item
    end
    return :na
  when 1, 1.0, true
    lookup_array.each_with_index do |item, index|
      next if lookup_value.is_a?(String) && !item.is_a?(String)
      next if lookup_value.is_a?(Numeric) && !item.is_a?(Numeric)
      item = item.downcase if item.respond_to?(:downcase)
      if item > lookup_value
        return :na if index == 0
        return index
      end
    end
    return lookup_array.to_a.size
  when -1, -1.0
    lookup_array.each_with_index do |item, index|
      next if lookup_value.is_a?(String) && !item.is_a?(String)
      next if lookup_value.is_a?(Numeric) && !item.is_a?(Numeric)
      item = item.downcase if item.respond_to?(:downcase)
      if item < lookup_value
        return :na if index == 0
        return index
      end
    end
    return lookup_array.to_a.size - 1
  end
  return :na
end

#choose(choice, *choices) ⇒ Object 



222
223
224
225
 
# File 'lib/formulae/run/excel_functions.rb', line 222

def choose(choice,*choices)
  return choice if iserr(choice)
  choices[choice - 1]
end

#count(*args) ⇒ Object 



227
228
229
230
231
 
# File 'lib/formulae/run/excel_functions.rb', line 227

def count(*args)
  flatten_and_inject(args) do |counter,arg|
    arg.is_a?(Numeric) && !arg.is_a?(Empty) ? counter + 1 : counter
  end
end

#counta(*args) ⇒ Object 



275
276
277
278
279
 
# File 'lib/formulae/run/excel_functions.rb', line 275

def counta(*args)
  flatten_and_inject(args) do |counter,arg|
    arg.is_a?(Empty) ? counter : counter + 1
  end
end

#countif(check_range, criteria, count_range = check_range) ⇒ Object 



233
234
235
 
# File 'lib/formulae/run/excel_functions.rb', line 233

def countif(check_range,criteria,count_range = check_range)
  countifs(count_range,check_range,criteria)
end

#countifs(count_range, *args) ⇒ Object 



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
273
 
# File 'lib/formulae/run/excel_functions.rb', line 237

def countifs(count_range,*args)
  return :na if iserr(count_range)
  return :na if args.any? { |c| iserr(c) }
  if count_range.is_a?(ExcelRange)
    return :na unless count_range.single_row_or_column?
    count_range = count_range.to_a
  else
    count_range = [count_range]
  end
  checks = Hash[*args].to_a.map do |check|
    check_range, check_value = check.first, check.last
    if check_range.is_a?(ExcelRange)
      return :na unless check_range.single_row_or_column?
      check_range = check_range.to_a
    else
      check_range = [check_range]
    end
    check_range = check_range.map { |c| c.to_s.downcase }
    check_value = check_value.to_s.downcase
    [check_range,check_value]
  end
  accumulator = 0
  count_range.each_with_index do |potential_count,i|
    next unless checks.all? do |c|
      if c.last =~ /^>([0-9.]+)$/
        c.first[i].to_f > $1.to_f
      elsif c.last =~ /^<([0-9.]+)$/
        c.first[i].to_f < $1.to_f
      else
        c.first[i] == c.last
      end
    end
    next unless potential_count.is_a?(Numeric) && !potential_count.is_a?(Empty)
    accumulator = accumulator + 1
  end
  accumulator
end

#excel_and(*args) ⇒ Object 



444
445
446
 
# File 'lib/formulae/run/excel_functions.rb', line 444

def excel_and(*args)
  args.all? {|a| a == true }
end

#excel_comparison(left, comparison, right) ⇒ Object 



431
432
433
434
435
 
# File 'lib/formulae/run/excel_functions.rb', line 431

def excel_comparison(left,comparison,right)
  left = left.downcase if left.is_a?(String)
  right = right.downcase if right.is_a?(String)
  left.send(comparison,right)
end

#excel_if(condition, true_value, false_value = false) ⇒ Object 



427
428
429
 
# File 'lib/formulae/run/excel_functions.rb', line 427

def excel_if(condition,true_value,false_value = false)
  condition ? true_value : false_value
end

#excel_or(*args) ⇒ Object 



448
449
450
 
# File 'lib/formulae/run/excel_functions.rb', line 448

def excel_or(*args)
  args.any? { |a| a == true }
end

#find(string_to_find, string_to_search, start_index = 1) ⇒ Object 



322
323
324
325
326
327
328
 
# File 'lib/formulae/run/excel_functions.rb', line 322

def find(string_to_find,string_to_search,start_index = 1)
  return string_to_find if iserr(string_to_find)
  return string_to_search if iserr(string_to_search)
  return start_index if iserr(start_index)
  result = string_to_search.index(string_to_find,start_index - 1 )
  result ? result + 1 : :value
end

#find_or_create_worksheet(worksheet_name) ⇒ Object 



97
98
99
100
101
102
103
104
105
 
# File 'lib/formulae/run/excel_functions.rb', line 97

def find_or_create_worksheet(worksheet_name)
  @worksheets ||= {variable_name => self}
  return @worksheets[worksheet_name] if @worksheets.has_key?(worksheet_name)
  worksheet = self.class.class_eval("#{worksheet_name.capitalize}.new")
  worksheet.instance_variable_set("@worksheets",@worksheets)
  worksheet.instance_variable_set("@formula_cache",formula_cache)
  @worksheets[worksheet_name] = worksheet
  worksheet
end

#flatten_and_inject(args, &block) ⇒ Object 



307
308
309
310
311
312
313
314
 
# File 'lib/formulae/run/excel_functions.rb', line 307

def flatten_and_inject(args,&block)
  args = args.map do |arg|
    return arg if iserr(arg)
    arg.respond_to?(:to_a) ? arg.to_a : arg
  end
  args.flatten!
  args.inject(0,&block)
end

#formula_cache ⇒ Object 



65
66
67
 
# File 'lib/formulae/run/excel_functions.rb', line 65

def formula_cache
  @formula_cache ||= {}
end

#iferror(value, value_if_error) ⇒ Object 



437
438
439
440
441
442
 
# File 'lib/formulae/run/excel_functions.rb', line 437

def iferror(value,value_if_error)
  iserr(value) ? value_if_error : value
rescue  ZeroDivisionError => e
  puts e
  return :div0
end

#index(area, row_number, column_number = nil) ⇒ Object 



391
392
393
 
# File 'lib/formulae/run/excel_functions.rb', line 391

def index(area,row_number, column_number = nil)
  formula_cache[[:index,area,row_number,column_number]] ||= calculate_index_formula(area,row_number, column_number)
end

#indirect(reference_text, refering_cell = nil) ⇒ Object 



484
485
486
487
488
 
# File 'lib/formulae/run/excel_functions.rb', line 484

def indirect(reference_text,refering_cell = nil)
  parsed_reference = formula_cache[[:indirect,reference_text]] ||= Formula.parse(reference_text)
  reference = parsed_reference.visit(RuntimeFormulaBuilder.new(self,refering_cell && Reference.new(refering_cell)))
  formula_cache[[:indirect_result,reference]] ||= eval(reference)
end

#iserr(arg) ⇒ Object 



420
421
422
423
424
425
 
# File 'lib/formulae/run/excel_functions.rb', line 420

def iserr(arg)
  return true if arg.is_a? Symbol
  return true if arg.respond_to?(:nan?) && arg.nan?
  return true if arg.respond_to?(:infinite?) && arg.infinite?
  false
end

#left(string, characters = 1) ⇒ Object 



316
317
318
319
320
 
# File 'lib/formulae/run/excel_functions.rb', line 316

def left(string,characters = 1)
  return string if iserr(string)
  return characters if iserr(characters)
  string.slice(0,characters)
end

#m(*potential_excel_matrices, &block) ⇒ Object 



476
477
478
 
# File 'lib/formulae/run/excel_functions.rb', line 476

def m(*potential_excel_matrices,&block)
  ExcelMatrixCollection.new(*potential_excel_matrices).matrix_map(&block)
end

#match(lookup_value, lookup_array, match_type = 1.0) ⇒ Object 



349
350
351
 
# File 'lib/formulae/run/excel_functions.rb', line 349

def match(lookup_value,lookup_array,match_type = 1.0)
  formula_cache[[:match,lookup_value,lookup_array,match_type]] ||= calculate_match(lookup_value,lookup_array,match_type)
end

#max(*args) ⇒ Object 



281
282
283
284
285
286
287
288
289
290
 
# File 'lib/formulae/run/excel_functions.rb', line 281

def max(*args)
  args = args.map { |arg| arg.respond_to?(:to_a) ? arg.to_a : arg }
  args.flatten!
  if (error = args.find { |arg| iserr(arg) }) 
    return error
  end
  args.delete_if { |arg| !arg.kind_of?(Numeric) }
  args.delete_if { |arg| arg.kind_of?(Empty) }
  args.max
end

#min(*args) ⇒ Object 



298
299
300
301
302
303
304
305
 
# File 'lib/formulae/run/excel_functions.rb', line 298

def min(*args)
  args = args.map { |arg| arg.respond_to?(:to_a) ? arg.to_a : arg }
  args.flatten!
  args.delete_if { |arg| !arg.kind_of?(Numeric) }
  args.delete_if { |arg| arg.kind_of?(Empty) }
  return error if error = args.find { |arg| iserr(arg) }
  args.min    
end

#mod(number, divisor) ⇒ Object 



138
139
140
141
142
143
144
 
# File 'lib/formulae/run/excel_functions.rb', line 138

def mod(number,divisor)
  return number if iserr(number)
  return divisor if iserr(divisor)
  return :na unless number.is_a?(Numeric)
  return :na unless divisor.is_a?(Numeric)
  number % divisor
end

#na(*arg) ⇒ Object 



412
413
414
 
# File 'lib/formulae/run/excel_functions.rb', line 412

def na(*arg)
  :na
end

#npv(rate, *cashflows) ⇒ Object 



151
152
153
154
155
156
157
 
# File 'lib/formulae/run/excel_functions.rb', line 151

def npv(rate,*cashflows)
  discount_factor = 1
  flatten_and_inject(cashflows) do |pv,cashlfow|
    discount_factor = discount_factor * (1 + rate)
    cashlfow.is_a?(Numeric) ? pv + (cashlfow / discount_factor) : pv
  end
end

#pmt(rate, periods, principal) ⇒ Object 



146
147
148
149
 
# File 'lib/formulae/run/excel_functions.rb', line 146

def pmt(rate,periods,principal)
  return -(principal / periods) if rate == 0
  -principal*(rate*((1+rate)**periods))/(((1+rate)**periods)-1)
end

#r(start_row, end_row) ⇒ Object 



456
457
458
 
# File 'lib/formulae/run/excel_functions.rb', line 456

def r(start_row,end_row)
  result_cache[[:r,start_row,end_row]] ||= Rows.new(self,start_row,end_row)
end

#recalculate ⇒ Object 



69
70
71
72
 
# File 'lib/formulae/run/excel_functions.rb', line 69

def recalculate
  formula_cache.clear
  result_cache.clear
end

#ref(*arg) ⇒ Object 



416
417
418
 
# File 'lib/formulae/run/excel_functions.rb', line 416

def ref(*arg)
  :ref
end

#result_cache ⇒ Object 



61
62
63
 
# File 'lib/formulae/run/excel_functions.rb', line 61

def result_cache
  @result_cache ||= {}
end

#round(number, decimal_places) ⇒ Object 



114
115
116
117
118
119
120
 
# File 'lib/formulae/run/excel_functions.rb', line 114

def round(number,decimal_places)
  return number if iserr(number)
  return decimal_places if iserr(decimal_places)
  return :na unless number.is_a?(Numeric)
  return :na unless decimal_places.is_a?(Numeric)
  number.round(decimal_places)
end

#rounddown(number, decimal_places) ⇒ Object 



130
131
132
133
134
135
136
 
# File 'lib/formulae/run/excel_functions.rb', line 130

def rounddown(number,decimal_places)
  return number if iserr(number)
  return decimal_places if iserr(decimal_places)
  return :na unless number.is_a?(Numeric)
  return :na unless decimal_places.is_a?(Numeric)
   (number * 10**decimal_places).floor.to_f / 10**decimal_places
end

#roundup(number, decimal_places) ⇒ Object 



122
123
124
125
126
127
128
 
# File 'lib/formulae/run/excel_functions.rb', line 122

def roundup(number,decimal_places)
  return number if iserr(number)
  return decimal_places if iserr(decimal_places)
  return :na unless number.is_a?(Numeric)
  return :na unless decimal_places.is_a?(Numeric)
   (number * 10**decimal_places).ceil.to_f / 10**decimal_places
end

#s(full_name_of_worksheet) ⇒ Object 



464
465
466
467
 
# File 'lib/formulae/run/excel_functions.rb', line 464

def s(full_name_of_worksheet)
  full_name_of_worksheet = $1 if full_name_of_worksheet.to_s =~ /^(\d+)\.0+$/
  self.send(@worksheet_names[full_name_of_worksheet])
end

#set(cell, value) ⇒ Object 



74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
# File 'lib/formulae/run/excel_functions.rb', line 74

def set(cell,value)
  instance_variable_name = "@#{cell}"
  unless instance_variable_defined?(instance_variable_name)
    self.class.class_eval do
      if method_defined?(cell) && !method_defined?("old_#{cell}")
        alias_method "old_#{cell}", cell
        define_method(cell) do
          instance_variable_get(instance_variable_name) || self.send("old_#{cell}")
        end
      end
    end
  end
  instance_variable_set(instance_variable_name, value)
end

#subtotal(type, *args) ⇒ Object 



338
339
340
341
342
343
344
345
346
347
 
# File 'lib/formulae/run/excel_functions.rb', line 338

def subtotal(type,*args)
  case type
  when 1.0, 101.0; average(*args)
  when 2.0, 102.0; count(*args)
  when 3.0, 103.0; counta(*args)
  when 9.0, 109.0; sum(*args)
  else 
    raise Exception.new("subtotal type #{type} on #{args} not implemented")
  end
end

#sum(*args) ⇒ Object 



159
160
161
162
163
 
# File 'lib/formulae/run/excel_functions.rb', line 159

def sum(*args)
  flatten_and_inject(args) do |counter,arg|
    arg.is_a?(Numeric) ? counter + arg.to_f : counter
  end
end

#sumif(check_range, criteria, sum_range = check_range) ⇒ Object 



165
166
167
 
# File 'lib/formulae/run/excel_functions.rb', line 165

def sumif(check_range,criteria,sum_range = check_range)
  sumifs(sum_range,check_range,criteria)
end

#sumifs(sum_range, *args) ⇒ Object 



169
170
171
172
173
174
175
176
177
178
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
 
# File 'lib/formulae/run/excel_functions.rb', line 169

def sumifs(sum_range,*args)
  return :na if iserr(sum_range)
  return :na if args.any? { |c| iserr(c) }
  if sum_range.is_a?(ExcelRange)
    return :na unless sum_range.single_row_or_column?
    sum_range = sum_range.to_a
  else
    sum_range = [sum_range]
  end
  checks = Hash[*args].to_a.map do |check|
    check_range, check_value = check.first, check.last
    if check_range.is_a?(ExcelRange)
      return :na unless check_range.single_row_or_column?
      check_range = check_range.to_a
    else
      check_range = [check_range]
    end
    check_range = check_range.map { |c| c.to_s.downcase }
    check_value = check_value.to_s.downcase
    [check_range,check_value]
  end
  accumulator = 0
  sum_range.each_with_index do |potential_sum,i|
    next unless checks.all? do |c|
      if c.last =~ /^>([0-9.]+)$/
        c.first[i].to_f > $1.to_f
      elsif c.last =~ /^<([0-9.]+)$/
        c.first[i].to_f < $1.to_f
      else
        c.first[i] == c.last
      end
    end
    next unless potential_sum.is_a?(Numeric)
    accumulator = accumulator + potential_sum
  end
  accumulator
end

#sumproduct(*ranges) ⇒ Object 



207
208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/formulae/run/excel_functions.rb', line 207

def sumproduct(*ranges)
  ranges.map do |range|
    return :na unless range.respond_to?(:to_a)
    range.to_a
  end.transpose.map do |values|
    values.inject(1) do |cell,total|
      total * cell
    end
  end.inject(0) do |product,total| 
    total + product
  end
rescue IndexError
  return :value
end

#t(table_name) ⇒ Object 



469
470
471
472
473
474
 
# File 'lib/formulae/run/excel_functions.rb', line 469

def t(table_name)
  table = @workbook_tables[table_name]
  return :ref unless table
  return table if table.is_a?(Table)
  @workbook_tables[table_name] = eval(table)
end

#text(number, format) ⇒ Object

Raises:

  • (Exception) 


107
108
109
110
111
112
 
# File 'lib/formulae/run/excel_functions.rb', line 107

def text(number,format)
  return number if iserr(number)
  return format if iserr(format)
  raise Exception.new("format #{format} not implemented") unless format.is_a?(Numeric)
  number.round(format).to_s
end

#variable_name ⇒ Object 



480
481
482
 
# File 'lib/formulae/run/excel_functions.rb', line 480

def variable_name
  @variable_name ||= self.class.to_s.downcase
end