module ExcelFunctions
Public Instance Methods
# File src/excel/excel_functions/sumifs.rb, line 3 def _filtered_range(range, *criteria) # Sort out the sum range range = [range] unless range.is_a?(Array) range = range.flatten indexes = _filtered_range_indexes(range, *criteria) return indexes if indexes.is_a?(Symbol) range.values_at(*indexes) end
# File src/excel/excel_functions/sumifs.rb, line 13 def _filtered_range_indexes(range, *criteria) # Sort out the criteria 0.step(criteria.length-1,2).each do |i| if criteria[i].is_a?(Array) criteria[i] = criteria[i].flatten else criteria[i] = [criteria[i]] end end filtered = [] # Work through each part of the sum range range.each_with_index do |potential,index| #next unless potential.is_a?(Numeric) # If a criteria fails, this is set to false and no further criteria are evaluated pass = true 0.step(criteria.length-1,2).each do |i| check_range = criteria[i] required_value = criteria[i+1] || 0 if required_value.is_a?(String) any_start = required_value.start_with?('*') any_end = required_value.end_with?('*') if any_start || any_end required_value = ".*"+required_value[1..-1] if any_start required_value = required_value[0..-2]+".*" if any_end required_value = /^#{required_value}$/i end end return :value if index >= check_range.length check_value = check_range[index] pass = case check_value when String case required_value when String check_value.downcase == required_value.downcase when Regexp check_value =~ required_value when Numeric begin Float(check_value) == required_value.to_f rescue ArgumentError false end else check_value.downcase == required_value.to_s.downcase end when true, false check_value == required_value when nil required_value == "" when Numeric case required_value when Numeric check_value == required_value when String required_value =~ /^\s*(<=|>=|<|>)?\s*([-+]?[0-9]+\.?[0-9]*([eE][-+]?[0-9]+)?)\s*$/ if $1 && $2 check_value.send($1,$2.to_f) elsif $2 check_value == $2.to_f else false end else check_value == required_value end when Symbol check_value == required_value end # case check_value break unless pass end # criteria loop filtered << index if pass end return filtered end
# File src/excel/excel_functions/abs.rb, line 3 def abs(a) case a when Numeric; return a.abs when Symbol; return a when nil; return 0 when true; return 1 when false; return 0 else; return :value end end
# File src/excel/excel_functions/add.rb, line 6 def add(a,b) # return apply_to_range(a,b) { |a,b| add(a,b) } if a.is_a?(Array) || b.is_a?(Array) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) a + b end
ADDRESS(row_num,column_num,abs_num,a1,sheet_text) row_num The row number to use in the cell reference. column_num The column number to use in the cell reference. abs_num Specifies the type of reference to return. 1 or omitted returns the following type of reference: absolute. 2 returns the following type of reference: absolute row; relative column. 3 returns the following type of reference: relative row; absolute column. 4 returns the following type of reference: relative. a1 A logical value that specifies the A1 or R1C1 reference style. If a1 is TRUE or omitted, ADDRESS returns an A1-style reference. If a1 is FALSE, ADDRESS returns an R1C1-style reference. sheet_text Text specifying the name of the sheet to be used as the external reference. If sheet_text is omitted, no sheet name is used.
# File src/excel/excel_functions/address.rb, line 21 def address(row_num, column_num, abs_num = 1, a1 = true, sheet_text = nil) raise NotSupportedException.new("address() function R1C1 references not implemented") if a1 == false return row_num if row_num.is_a?(Symbol) return column_num if column_num.is_a?(Symbol) return abs_num if abs_num.is_a?(Symbol) return a1 if a1.is_a?(Symbol) return sheet_text if sheet_text.is_a?(Symbol) row_num = number_argument(row_num) column_num = number_argument(column_num) abs_num = number_argument(abs_num) return :value if row_num < 1 return :value if column_num < 1 return :value if abs_num < 1 return :value if abs_num > 4 row = row_num.to_i.to_s column = Reference.column_letters_for_column_number[column_num.to_i].to_s ref = case abs_num when 1; "$"+column+"$"+row when 2; "" +column+"$" +row when 3; "$"+column+"" +row when 4; ""+column+"" +row end if sheet_text ref = "'#{sheet_text}'!"+ref end ref end
# File src/excel/excel_functions/apply_to_range.rb, line 3 def apply_to_range(a,b) a = Array.new(b.length,Array.new(b.first.length,a)) unless a.is_a?(Array) b = Array.new(a.length,Array.new(a.first.length,b)) unless b.is_a?(Array) return :value unless b.length == a.length return :value unless b.first.length == a.first.length a.map.with_index do |row,i| row.map.with_index do |cell,j| yield cell, b[i][j] end end end
# File src/excel/excel_functions/average.rb, line 3 def average(*args) args = args.flatten error = args.find {|a| a.is_a?(Symbol)} return error if error args.delete_if { |a| !a.is_a?(Numeric) } return :div0 if args.empty? args.inject(0.0) { |m,i| m + i.to_f } / args.size.to_f end
See sumifs.rb for _filtered_range
# File src/excel/excel_functions/averageifs.rb, line 5 def averageifs(average_range, *criteria) filtered = _filtered_range(average_range, *criteria) average(*filtered) end
# File src/excel/excel_functions/ceiling.rb, line 3 def ceiling(number, multiple, mode = 0) return number if number.is_a?(Symbol) return multiple if multiple.is_a?(Symbol) return mode if mode.is_a?(Symbol) number = number_argument(number) multiple = number_argument(multiple) mode = number_argument(mode) return :value unless number.is_a?(Numeric) return :value unless multiple.is_a?(Numeric) return :value unless mode.is_a?(Numeric) return 0 if multiple == 0 if mode == 0 || number > 0 whole, remainder = number.divmod(multiple) num_steps = remainder > 0 ? whole + 1 : whole num_steps * multiple else # Need to round negative away from zero -ceiling(-number, multiple, 0) end end
# File src/excel/excel_functions/cell.rb, line 3 def cell(info_type, reference = nil) return info_type if info_type.is_a?(Symbol) return :value unless info_type.is_a?(String) case info_type.downcase when 'filename' original_excel_filename else :value end end
# File src/excel/excel_functions/char.rb, line 3 def char(a) a = number_argument(a) return a if a.is_a?(Symbol) return :value if a <= 0 return :value if a >= 256 a = a.floor "".force_encoding("Windows-1252") << a end
# File src/excel/excel_functions/choose.rb, line 3 def choose(index,*args) # If an argument is an error, return that return index if index.is_a?(Symbol) error = args.find {|a| a.is_a?(Symbol)} return error if error # If the index is out of bounds, return an error return :value unless index return :value if index < 1 return :value if index > args.length return args[index-1] || 0 end
# File src/excel/excel_functions/cosh.rb, line 3 def cosh(x) return x if x.is_a?(Symbol) x ||= 0 Math.cosh(x) end
# File src/excel/excel_functions/count.rb, line 3 def count(*args) args = args.flatten args.delete_if { |a| !a.is_a?(Numeric)} args.size end
# File src/excel/excel_functions/counta.rb, line 3 def counta(*args) args = args.flatten.compact args.size end
# File src/excel/excel_functions/countif.rb, line 3 def countif(count_range, criteria) return 0 if criteria.kind_of?(Array) # Who knows why, Excel count_range = [count_range] unless count_range.kind_of?(Array) rows = count_range.size if count_range.first && count_range.first.kind_of?(Array) sum_range = Array.new(rows, Array.new(count_range.first.size,1)) else sum_range = Array.new(rows, 1) end return sumif(count_range, criteria, sum_range) end
# File src/excel/excel_functions/countifs.rb, line 3 def countifs(*criteria) return 0 if criteria.empty? c = criteria[0].is_a?(Array) ? criteria[0] : [criteria[0]] count = c.map { 1 } return sumifs(count, *criteria) end
# File src/excel/excel_functions/scurve.rb, line 64 def curve(curveType, currentYear, startValue, endValue, duration, startYear = 2018) if curveType == "s" return scurve(currentYear, startValue, endValue, duration, startYear) elsif curveType == "hs" return halfscurve(currentYear, startValue, endValue, duration, startYear) else return lcurve(currentYear, startValue, endValue, duration, startYear) end end
# File src/excel/excel_functions/divide.rb, line 6 def divide(a,b) # return apply_to_range(a,b) { |a,b| divide(a,b) } if a.is_a?(Array) || b.is_a?(Array) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) return :div0 if b == 0 a / b.to_f rescue ZeroDivisionError :div0 end
# File src/excel/excel_functions/ensure_is_number.rb, line 3 def ensure_is_number(a) return number_argument(a) end
# File src/excel/excel_functions/and.rb, line 3 def excel_and(*args) # Flatten arrays args = args.flatten # If an argument is an error, return that error = args.find {|a| a.is_a?(Symbol)} return error if error # Replace 1 and 0 with true and false args.map! do |a| case a when 1; true when 0; false else; a end end # Remove anything not boolean args.delete_if { |a| !(a.is_a?(TrueClass) || a.is_a?(FalseClass)) } # Return an error if nothing less return :value if args.empty? # Now calculate and return args.all? {|a| a == true } end
# File src/excel/excel_functions/excel_equal.rb, line 5 def excel_equal?(a,b) return apply_to_range(a,b) { |a,b| excel_equal?(a,b) } if a.is_a?(Array) || b.is_a?(Array) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) return a.downcase == b.downcase if a.is_a?(String) && b.is_a?(String) a == b end
# File src/excel/excel_functions/excel_if.rb, line 3 def excel_if(condition,true_value,false_value = false) return condition if condition.is_a?(Symbol) return false_value if condition == 0 condition ? true_value : false_value end
FIXME: Excel doesn't use this algorithm for matching. Seems to do some bisecting. Only matters if doing an ordered search and the range is not in order
# File src/excel/excel_functions/excel_match.rb, line 6 def excel_match(lookup_value,lookup_array,match_type = 1) return lookup_value if lookup_value.is_a?(Symbol) return lookup_array if lookup_array.is_a?(Symbol) return match_type if match_type.is_a?(Symbol) lookup_value ||= 0 lookup_array ||= [0] match_type ||= 0 lookup_array = [lookup_array] unless lookup_array.is_a?(Array) lookup_array = lookup_array.flatten 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 ||= 0 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| item ||= 0 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| item ||= 0 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
# File src/excel/excel_functions/not.rb, line 3 def excel_not(a) return a if a.is_a?(Symbol) return :value if a.is_a?(String) return :value if a.is_a?(Array) a = false if a == nil a = false if a == 0 a = true if a.is_a?(Numeric) !a end
# File src/excel/excel_functions/or.rb, line 3 def excel_or(*args) # Flatten arrays args = args.flatten # If an argument is an error, return that error = args.find {|a| a.is_a?(Symbol)} return error if error # Replace 1 and 0 with true and false args.map! do |a| case a when 1; true when 0; false else; a end end # Remove anything not boolean args.delete_if { |a| !(a.is_a?(TrueClass) || a.is_a?(FalseClass)) } # Return an error if nothing less return :value if args.empty? # Now calculate and return args.any? {|a| a == true } end
# File src/excel/excel_functions/exp.rb, line 3 def exp(a) a = number_argument(a) return a if a.is_a?(Symbol) Math.exp(a) end
# File src/excel/excel_functions/find.rb, line 3 def find(find_text,within_text,start_number = 1) return find_text if find_text.is_a?(Symbol) return within_text if within_text.is_a?(Symbol) return start_number if start_number.is_a?(Symbol) # nils are treated as empty strings find_text ||= "" within_text ||= "" # Other things should be turned into a string find_text = find_text.to_s within_text = within_text.to_s # there are some cases where the start_number is remapped case start_number when nil; return :value when String; begin start_number = Float(start_number) rescue ArgumentError return :value end when true; start_number = 1 when false; return :value when Numeric; # ok else; return :value end # edge case return 1 if find_text == "" && within_text == "" && start_number == 1 # length check return :value if start_number < 1 return :value if start_number > within_text.length # Ok, lets go result = within_text.index(find_text,start_number - 1 ) result ? result + 1 : :value end
# File src/excel/excel_functions/floor.rb, line 3 def floor(number, multiple) return number if number.is_a?(Symbol) return multiple if multiple.is_a?(Symbol) number ||= 0 return :value unless number.is_a?(Numeric) return :value unless multiple.is_a?(Numeric) return :div0 if multiple == 0 return :num if multiple < 0 number - (number % multiple) end
# File src/excel/excel_functions/forecast.rb, line 3 def forecast(required_x, known_y, known_x) required_x = number_argument(required_x) return required_x if required_x.is_a?(Symbol) return :na unless known_y.is_a?(Array) return :na unless known_x.is_a?(Array) known_y = known_y.flatten known_x = known_x.flatten known_y.each { |y| return y if y.is_a?(Symbol) } known_x.each { |x| return x if x.is_a?(Symbol) } return :na unless known_x.length == known_y.length return :na if known_y.empty? return :na if known_x.empty? 0.upto(known_x.length-1).each do |i| known_x[i] = nil unless known_y[i].is_a?(Numeric) known_y[i] = nil unless known_x[i].is_a?(Numeric) end known_x.compact! known_y.compact! mean_y = known_y.inject(0.0) { |m,i| m + i.to_f } / known_y.size.to_f mean_x = known_x.inject(0.0) { |m,i| m + i.to_f } / known_x.size.to_f b_denominator = known_x.inject(0.0) { |s,x| x.is_a?(Numeric) ? (s + (x-mean_x)**2 ) : s } return :div0 if b_denominator == 0 b_numerator = 0.0 known_x.each.with_index do |x,i| y = known_y[i] next unless x.is_a?(Numeric) next unless y.is_a?(Numeric) b_numerator = b_numerator + ((x-mean_x)*(y-mean_y)) end b = b_numerator / b_denominator a = mean_y - (b * mean_x) return a + (b*required_x) end
# File src/excel/excel_functions/scurve.rb, line 28 def halfscurve(currentYear, startValue, endValue , duration, startYear = 2018) currentYear = number_argument(currentYear) startValue = number_argument(startValue) endValue = number_argument(endValue) duration = number_argument(duration) startYear = number_argument(startYear) return currentYear if currentYear.is_a?(Symbol) return startValue if startValue.is_a?(Symbol) return endValue if endValue.is_a?(Symbol) return duration if duration.is_a?(Symbol) return startYear if startYear.is_a?(Symbol) return startValue if currentYear < startYear scurve(currentYear + duration, startValue, endValue, duration * 2, startYear) - ((endValue - startValue) / 2.0) end
# File src/excel/excel_functions/hlookup.rb, line 3 def hlookup(lookup_value, lookup_table, row_number, match_type = true) return lookup_value if lookup_value.is_a?(Symbol) return lookup_table if lookup_table.is_a?(Symbol) return row_number if row_number.is_a?(Symbol) return match_type if match_type.is_a?(Symbol) return :na if lookup_value == nil return :na if lookup_table == nil return :na if row_number == nil return :na if match_type == nil lookup_value = lookup_value.downcase if lookup_value.is_a?(String) last_good_match = 0 return :value unless row_number > 0 return :ref unless row_number <= lookup_table.size lookup_table.first.each_with_index do |possible_match, column_number| next if lookup_value.is_a?(String) && !possible_match.is_a?(String) next if lookup_value.is_a?(Numeric) && !possible_match.is_a?(Numeric) possible_match = possible_match.downcase if lookup_value.is_a?(String) if lookup_value == possible_match return lookup_table[row_number-1][column_number] elsif match_type == true if possible_match > lookup_value return :na if column_number == 0 return lookup_table[row_number-1][last_good_match] else last_good_match = column_number end end end # We don't have a match if match_type == true return lookup_table[row_number - 1][last_good_match] else return :na end end
# File src/excel/excel_functions/hyperlink.rb, line 3 def hyperlink(url, text = url) u = string_argument(url) t = string_argument(text) "<a href=#{u.inspect}>#{t}</a>" end
# File src/excel/excel_functions/iferror.rb, line 3 def iferror(value,value_if_error) value_if_error ||= 0 return value_if_error if value.is_a?(Symbol) return value_if_error if value.is_a?(Float) && value.nan? value end
# File src/excel/excel_functions/index.rb, line 3 def index(array, row_number, column_number = :not_specified) return array if array.is_a?(Symbol) return row_number if row_number.is_a?(Symbol) return column_number if column_number.is_a?(Symbol) && column_number != :not_specified array = [[array]] unless array.is_a?(Array) if column_number == :not_specified if array.length == 1 # It is a single row index_for_row_column(array,1,row_number) elsif array.first.length == 1 # it is a single column index_for_row_column(array,row_number,1) else return :ref end else if row_number == nil || row_number == 0 index_for_whole_column(array,column_number) elsif column_number == nil || column_number == 0 index_for_whole_row(array,row_number) else index_for_row_column(array,row_number,column_number) end end end
# File src/excel/excel_functions/index.rb, line 30 def index_for_row_column(array,row_number,column_number) return :ref if row_number < 1 || row_number > array.length row = array[row_number-1] return :ref if column_number < 1 || column_number > row.length row[column_number-1] || 0 end
# File src/excel/excel_functions/index.rb, line 44 def index_for_whole_column(array,column_number) return :ref if column_number < 1 return :ref if column_number > array[0].length return index_for_row_column(array, 1, column_number) if array.length == 1 array.map { |row| [row[column_number-1]]} end
# File src/excel/excel_functions/index.rb, line 37 def index_for_whole_row(array,row_number) return :ref if row_number < 1 return :ref if row_number > array.length return index_for_row_column(array, row_number, 1) if array.first.length == 1 [array[row_number-1]] end
# File src/excel/excel_functions/int.rb, line 3 def int(number) number = number_argument(number) return number if number.is_a?(Symbol) number.floor.to_f end
# File src/excel/excel_functions/isblank.rb, line 3 def isblank(a) return true if a == nil false end
# File src/excel/excel_functions/iserr.rb, line 3 def iserr(a) return false if a == :na return true if a.is_a?(Symbol) false end
# File src/excel/excel_functions/iserror.rb, line 3 def iserror(a) return true if a.is_a?(Symbol) false end
# File src/excel/excel_functions/isnumber.rb, line 3 def isnumber(a) return true if a.is_a?(Numeric) false end
# File src/excel/excel_functions/large.rb, line 3 def large(range, k) range = [range] unless range.is_a?(Array) range = range.flatten error = range.find {|a| a.is_a?(Symbol)} error ||= k if k.is_a?(Symbol) return error if error range.delete_if { |v| !v.is_a?(Numeric) } return :value unless k.is_a?(Numeric) return :num unless k>0 && k<=range.size range.sort! range.reverse! range[k-1] end
# File src/excel/excel_functions/scurve.rb, line 45 def lcurve(currentYear, startValue, endValue , duration, startYear = 2018) currentYear = number_argument(currentYear) startValue = number_argument(startValue) endValue = number_argument(endValue) duration = number_argument(duration) startYear = number_argument(startYear) return currentYear if currentYear.is_a?(Symbol) return startValue if startValue.is_a?(Symbol) return endValue if endValue.is_a?(Symbol) return duration if duration.is_a?(Symbol) return startYear if startYear.is_a?(Symbol) return startValue if currentYear < startYear return endValue if currentYear > (startYear + duration) return startValue if currentYear < startYear startValue + (endValue - startValue) / duration * (currentYear - startYear) end
# File src/excel/excel_functions/left.rb, line 3 def left(string,characters = 1) return string if string.is_a?(Symbol) return characters if characters.is_a?(Symbol) return nil if string == nil || characters == nil return :value if characters < 0 string = "TRUE" if string == true string = "FALSE" if string == false string.to_s.slice(0,characters) end
# File src/excel/excel_functions/len.rb, line 3 def len(a) return a if a.is_a?(Symbol) a.to_s.length end
# File src/excel/excel_functions/less_than.rb, line 5 def less_than?(a,b) # return apply_to_range(a,b) { |a,b| less_than?(a,b) } if a.is_a?(Array) || b.is_a?(Array) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) a = 0 if a == nil b = 0 if b == nil case a when String case b when String a.downcase < b.downcase when Numeric false when TrueClass, FalseClass true end when TrueClass false when FalseClass b.is_a?(TrueClass) when Numeric case b when Numeric a < b when String true when TrueClass, FalseClass true end end end
# File src/excel/excel_functions/less_than_or_equal.rb, line 5 def less_than_or_equal?(a,b) opposite = more_than?(a,b) return opposite if opposite.is_a?(Symbol) !opposite end
# File src/excel/excel_functions/ln.rb, line 3 def ln(a) a = number_argument(a) return a if a.is_a?(Symbol) return :num if a <= 0 Math.log(a) end
# File src/excel/excel_functions/log.rb, line 3 def log(a, b = 10) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) return :num if a <= 0 return :num if b <= 0 Math.log(a) / Math.log(b) end
# File src/excel/excel_functions/lower.rb, line 3 def lower(string) return string if string.is_a?(Symbol) case string when nil; "" when String; string.downcase when Numeric if string.round == string string.to_i.to_s else string.to_s end when true; "true" when false; "false" else string.to_s end end
# File src/excel/excel_functions/max.rb, line 3 def max(*args) args = args.flatten error = args.find {|a| a.is_a?(Symbol)} return error if error args.delete_if { |a| !a.is_a?(Numeric) } return 0 if args.empty? args.max end
# File src/excel/excel_functions/mid.rb, line 3 def mid(text, start_num, num_chars) start_num = number_argument(start_num) num_chars = number_argument(num_chars) return text if text.is_a?(Symbol) return start_num if start_num.is_a?(Symbol) return num_chars if num_chars.is_a?(Symbol) text = text.to_s return :value if start_num < 1 return :value if num_chars < 0 return "" if start_num > text.length text[start_num - 1, num_chars] end
# File src/excel/excel_functions/min.rb, line 3 def min(*args) args = args.flatten error = args.find {|a| a.is_a?(Symbol)} return error if error args.delete_if { |a| !a.is_a?(Numeric) } return 0 if args.empty? args.min end
# File src/excel/excel_functions/mmult.rb, line 3 def mmult(array_a, array_b) return array_a if array_a.is_a?(Symbol) return array_b if array_b.is_a?(Symbol) return :value unless array_a.is_a?(Array) return :value unless array_b.is_a?(Array) columns = array_a.first.length rows = array_b.length error = Array.new([rows, columns].max) { Array.new([rows, columns].max, :value) } return error unless columns == rows return error unless array_a.all? { |a| a.all? { |b| b.is_a?(Numeric) }} return error unless array_b.all? { |a| a.all? { |b| b.is_a?(Numeric) }} result = Array.new(array_a.length) { Array.new(array_b.first.length) } indices = (0...rows).to_a result.map.with_index do |row, i| row.map.with_index do |cell, j| indices.inject(0) do |sum, n| sum = sum + (array_a[i][n] * array_b[n][j]) end end end end
# File src/excel/excel_functions/mod.rb, line 3 def mod(a,b) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) return :div0 if b == 0 a % b end
# File src/excel/excel_functions/more_than.rb, line 5 def more_than?(a,b) # return apply_to_range(a,b) { |a,b| more_than?(a,b) } if a.is_a?(Array) || b.is_a?(Array) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) a = 0 if a == nil b = 0 if b == nil case a when String case b when String a.downcase > b.downcase when Numeric true when TrueClass, FalseClass false end when TrueClass !b.is_a?(TrueClass) when FalseClass case b when TrueClass, FalseClass false else true end when Numeric case b when Numeric a > b else false end end end
# File src/excel/excel_functions/more_than_or_equal.rb, line 5 def more_than_or_equal?(a,b) opposite = less_than?(a,b) return opposite if opposite.is_a?(Symbol) !opposite end
# File src/excel/excel_functions/mround.rb, line 3 def mround(value, multiple) value = number_argument(value) multiple = number_argument(multiple) return value if value.is_a?(Symbol) return multiple if multiple.is_a?(Symbol) # Must both have the same sign return :num unless (value < 0) == (multiple < 0) # Zeros just return zero return 0 if value == 0 return 0 if multiple == 0 (value.to_f / multiple.to_f).round * multiple.to_f end
# File src/excel/excel_functions/multiply.rb, line 6 def multiply(a,b) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) a * b end
# File src/excel/excel_functions/na.rb, line 3 def na() return :na end
# File src/excel/excel_functions/negative.rb, line 3 def negative(a) return a.map { |c| negative(c) } if a.is_a?(Array) a = number_argument(a) return a if a.is_a?(Symbol) -a end
# File src/excel/excel_functions/not_equal.rb, line 5 def not_equal?(a,b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) return a.downcase != b.downcase if a.is_a?(String) && b.is_a?(String) a != b end
# File src/excel/excel_functions/npv.rb, line 3 def npv(rate, *values) # Turn the arguments into numbers rate = number_argument(rate) values = values.flatten.map { |v| number_argument(v) } # Check for errors return rate if rate.is_a?(Symbol) return :div0 if rate == -1 values.each { |v| return v if v.is_a?(Symbol) } npv = 0 values.each.with_index { |v, i| npv = npv + (v/((1+rate)**(i+1))) } npv end
This is a support function for mapping arguments that are numbers to numeric values deals with the fact that in Excel “2.14” == 2.14, TRUE == 1, FALSE == 0 and nil == 0
# File src/excel/excel_functions/number_argument.rb, line 5 def number_argument(a) case a when Symbol return a when String begin return Float(a) rescue ArgumentError return :value end when nil return 0 when true return 1 when false return 0 when Numeric return a when Array return number_argument(a[0][0]) else return :value end end
# File src/excel/excel_functions/number_or_zero.rb, line 3 def number_or_zero(a) return a if a.is_a?(Symbol) return a if a.is_a?(Numeric) 0 end
# File src/excel/excel_functions/pi.rb, line 3 def pi Math::PI end
# File src/excel/excel_functions/pmt.rb, line 3 def pmt(rate,number_of_periods,present_value, final_value = 0, type = 0) rate = number_argument(rate) number_of_periods = number_argument(number_of_periods) present_value = number_argument(present_value) final_value = number_argument(final_value) type = number_argument(type) return rate if rate.is_a?(Symbol) return number_of_periods if number_of_periods.is_a?(Symbol) return present_value if present_value.is_a?(Symbol) return final_value if final_value.is_a?(Symbol) return type if type.is_a?(Symbol) return :num if number_of_periods == 0 raise NotSupportedException.new("pmt() function non zero final value not implemented") unless final_value == 0 raise NotSupportedException.new("pmt() function non zero type not implemented") unless type == 0 return -(present_value / number_of_periods) if rate == 0 -present_value*(rate*((1+rate)**number_of_periods))/(((1+rate)**number_of_periods)-1) end
# File src/excel/excel_functions/power.rb, line 6 def power(a,b) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) return 1 if b ==0 # Special case so can do the following negative number check return :num if a < 0 && b < 1 a**b end
# File src/excel/excel_functions/product.rb, line 3 def product(*args) args = args.flatten args.find {|a| a.is_a?(Symbol)} || args.delete_if { |a| !a.is_a?(Numeric) }.inject(0) { |m,i| m + i } end
# File src/excel/excel_functions/pv.rb, line 3 def pv(rate, nper, pmt, fv = nil, type = nil) # Turn the remainder into numbers rate = number_argument(rate) nper = number_argument(nper) pmt = number_argument(pmt) fv = number_argument(fv) type = number_argument(type) # Check for errors return rate if rate.is_a?(Symbol) return nper if nper.is_a?(Symbol) return pmt if pmt.is_a?(Symbol) return fv if fv.is_a?(Symbol) return :value unless (type == 1 || type == 0) return :value unless rate >= 0 # Sum the payments if rate > 0 present_value = -pmt * ((1 - ((1 + rate)**-nper))/rate) else present_value = -pmt * nper end # Adjust for the type, which governs whether payments at the beginning or end of the period present_value = present_value * ( 1 + rate) if type == 1 # Add on the final value present_value += -fv / ((1 + rate)**(nper)) # Return the answer present_value end
# File src/excel/excel_functions/rank.rb, line 3 def rank(number, list, order = 0) number = number_argument(number) order = number_argument(order) list = [[list]] unless list.is_a?(Array) return number if number.is_a?(Symbol) return order if order.is_a?(Symbol) return list if list.is_a?(Symbol) ranked = 1 found = false list.flatten.each do |cell| return cell if cell.is_a?(Symbol) next unless cell.is_a?(Numeric) found = true if cell == number if order == 0 ranked += 1 if cell > number else ranked +=1 if cell < number end end return :na unless found return ranked end
# File src/excel/excel_functions/rate.rb, line 3 def rate(periods, payment, presentValue, finalValue) raise NotSupportedException.new("rate() function has not been implemented fully. Edit src/excel/excel_functions/rate.rb") unless (payment || 0) == 0 return periods if periods.is_a?(Symbol) return presentValue if presentValue.is_a?(Symbol) return finalValue if finalValue.is_a?(Symbol) return :num unless periods.is_a?(Numeric) return :num unless presentValue.is_a?(Numeric) return :num unless finalValue.is_a?(Numeric) ((finalValue.to_f/(-presentValue.to_f)) ** (1.0/periods))-1.0 end
# File src/excel/excel_functions/replace.rb, line 3 def replace(string, start, length, replacement) return string if string.is_a?(Symbol) return start if start.is_a?(Symbol) return length if length.is_a?(Symbol) return replacement if replacement.is_a?(Symbol) string = string.to_s return string + replacement if start >= string.length string_join(left(string,start-1),replacement,right(string,string.length - (start - 1 + length))) end
This is a support function for reseting a spreadsheet's instance variables back to nil, allowing the results to be recalculated
# File src/excel/excel_functions/reset.rb, line 5 def reset() # Set all the instance variables to nil instance_variables.each do |iv| instance_variable_set(iv,nil) end # Reset the settable variables to their defaults initialize end
# File src/excel/excel_functions/right.rb, line 3 def right(string,characters = 1) return string if string.is_a?(Symbol) return characters if characters.is_a?(Symbol) return nil if string == nil || characters == nil return :value if characters < 0 string = "TRUE" if string == true string = "FALSE" if string == false string = string.to_s characters = string.length if characters > string.length string.slice(string.length-characters,characters) || "" end
# File src/excel/excel_functions/round.rb, line 3 def round(number,decimal_places) number = number_argument(number) decimal_places = number_argument(decimal_places) return number if number.is_a?(Symbol) return decimal_places if decimal_places.is_a?(Symbol) number.round(decimal_places) end
# File src/excel/excel_functions/rounddown.rb, line 3 def rounddown(number,decimal_places) number = number_argument(number) decimal_places = number_argument(decimal_places) return number if number.is_a?(Symbol) return decimal_places if decimal_places.is_a?(Symbol) (number * 10**decimal_places).truncate.to_f / 10**decimal_places end
# File src/excel/excel_functions/roundup.rb, line 3 def roundup(number,decimal_places) number = number_argument(number) decimal_places = number_argument(decimal_places) return number if number.is_a?(Symbol) return decimal_places if decimal_places.is_a?(Symbol) if number < 0 (number * 10**decimal_places).floor.to_f / 10**decimal_places else (number * 10**decimal_places).ceil.to_f / 10**decimal_places end end
# File src/excel/excel_functions/scurve.rb, line 3 def scurve(currentYear, startValue, endValue, duration, startYear = 2018.0) currentYear = number_argument(currentYear) startValue = number_argument(startValue) endValue = number_argument(endValue) duration = number_argument(duration) startYear = number_argument(startYear) return currentYear if currentYear.is_a?(Symbol) return startValue if startValue.is_a?(Symbol) return endValue if endValue.is_a?(Symbol) return duration if duration.is_a?(Symbol) return startYear if startYear.is_a?(Symbol) return startValue if currentYear < startYear x = (currentYear - startYear) / duration.to_f x0 = 0.0 a = endValue - startValue sc = 0.999 eps = 1.0 - sc mu = 0.5 beta = (mu - 1.0) / Math.log(1.0 / sc - 1) scurve = a * (((Math.exp(-(x - mu) / beta) + 1) ** -1) - ((Math.exp(-(x0 - mu) / beta) + 1) ** -1)) + startValue end
# File src/excel/excel_functions/sqrt.rb, line 3 def sqrt(a) return a if a.is_a?(Symbol) a ||= 0 return :value unless a.is_a?(Numeric) return :num if a < 0 Math.sqrt(a) end
This is a support function for mapping arguments that are not strings to their Excel string equivalent. e.g., true = TRUE, :div0 = “DIV/0!”
# File src/excel/excel_functions/string_argument.rb, line 5 def string_argument(a) case a when Symbol return { :name => "#NAME?", :value => "#VALUE!", :div0 => "#DIV/0!", :ref => "#REF!", :na => "#N/A", :num => "#NUM!", }[a] || :value when String return a when nil return "" when true return "TRUE" when false return "FALSE" when Numeric if a.round == a return a.to_i.to_s else return a.to_s end when Array return string_argument(a[0][0]) else return :value end end
# File src/excel/excel_functions/string_join.rb, line 3 def string_join(*strings) strings.find {|s| s.is_a?(Symbol)} || strings.map do |s| case s when nil; "0" when Numeric if s.round == s s.to_i.to_s else s.to_s end else s.to_s end end.join('') end
# File src/excel/excel_functions/substitute.rb, line 3 def substitute(text, old_text, new_text, instance_num = :any) # Check for errors return text if text.is_a?(Symbol) return old_text if old_text.is_a?(Symbol) return new_text if new_text.is_a?(Symbol) # Nils get turned into blanks text ||= "" new_text ||= "" old_text ||= "" return text if old_text == "" # Booleans get made into text, but need to be TRUE not true text = text.to_s.upcase if text.is_a?(TrueClass) || text.is_a?(FalseClass) old_text = old_text.to_s.upcase if old_text.is_a?(TrueClass) || old_text.is_a?(FalseClass) new_text = new_text.to_s.upcase if new_text.is_a?(TrueClass) || new_text.is_a?(FalseClass) # Other items get turned into text text = text.to_s old_text = old_text.to_s new_text = new_text.to_s # Now split based on whether instance_num is passed if instance_num == :any # The easy case text.gsub(old_text, new_text) else # The harder case return instance_num if instance_num.is_a?(Symbol) return :value unless instance_num.to_i > 0 instances_found = 0 text.gsub(old_text) do |match| instances_found += 1 if instances_found == instance_num new_text else old_text end end end end
# File src/excel/excel_functions/subtotal.rb, line 3 def subtotal(type,*args) case number_argument(type) when Symbol; 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 :value end end
# File src/excel/excel_functions/subtract.rb, line 6 def subtract(a,b) # return apply_to_range(a,b) { |a,b| subtract(a,b) } if a.is_a?(Array) || b.is_a?(Array) a = number_argument(a) b = number_argument(b) return a if a.is_a?(Symbol) return b if b.is_a?(Symbol) a - b end
# File src/excel/excel_functions/sum.rb, line 3 def sum(*args) args = args.flatten args.find {|a| a.is_a?(Symbol)} || args.delete_if { |a| !a.is_a?(Numeric) }.inject(0) { |m,i| m + i } end
# File src/excel/excel_functions/sumif.rb, line 3 def sumif(check_range,criteria,sum_range = check_range) sumifs(sum_range,check_range,criteria) end
# File src/excel/excel_functions/sumifs.rb, line 96 def sumifs(range,*criteria) filtered = _filtered_range(range,*criteria) sum(*filtered) end
# File src/excel/excel_functions/sumproduct.rb, line 3 def sumproduct(*args) error = args.find { |a| a.is_a?(Symbol) } return error if error return :value if args.any? { |a| a == nil } args = args.map { |a| a.is_a?(Array) ? a : [[a]] } first = args.shift accumulator = 0 first.each_with_index do |row,row_number| row.each_with_index do |cell,column_number| next unless cell.is_a?(Numeric) product = cell args.each do |area| return :value unless area.length > row_number r = area[row_number] return :value unless r.length > column_number c = r[column_number] if c.is_a?(Numeric) product = product * c else product = product * 0 break end end accumulator += product end end accumulator end
# File src/excel/excel_functions/text.rb, line 3 def text(number, format) number ||= 0 return "" unless format format = "0" if format == 0.0 if number.is_a?(String) begin number = Float(number) rescue ArgumentError => e # Ignore end end return number unless number.is_a?(Numeric) return format if format.is_a?(Symbol) case format when /^(0([.,]0*)?)%/ text(number*100, $1)+"%" when /^(0+)$/ sprintf("%0#{$1.length}.0f", number) when /^#[,.]#*(0[.,]0+)?(0*)$/ formated_with_decimals = text(number, $1 || "0") parts = formated_with_decimals.split('.') if $2 && parts[0].length < $2.length parts[0] = ("0"*($2.length - parts[0].length))+parts[0] end parts[0].gsub!(/(\d)(?=(\d\d\d)+(?!\d))/, "\\1,") parts.join('.') when /0[.,](0+)/ sprintf("%.#{$1.length}f", number) else raise ExcelToCodeException.new("in TEXT function format #{format} not yet supported by excel_to_code") end end
# File src/excel/excel_functions/trim.rb, line 3 def trim(text) return text unless text.is_a?(String) text.strip.gsub(/ +/,' ') end
# File src/excel/excel_functions/value.rb, line 3 def value(a) number_argument(a) end
# File src/excel/excel_functions/vlookup.rb, line 3 def vlookup(lookup_value,lookup_table,column_number, match_type = true) return lookup_value if lookup_value.is_a?(Symbol) return lookup_table if lookup_table.is_a?(Symbol) return column_number if column_number.is_a?(Symbol) return match_type if match_type.is_a?(Symbol) return :na if lookup_value == nil return :na if lookup_table == nil return :na if column_number == nil return :na if match_type == nil lookup_value = lookup_value.downcase if lookup_value.is_a?(String) last_good_match = 0 lookup_table.each_with_index do |row,index| possible_match = row.first next if lookup_value.is_a?(String) && !possible_match.is_a?(String) next if lookup_value.is_a?(Numeric) && !possible_match.is_a?(Numeric) possible_match = possible_match.downcase if lookup_value.is_a?(String) if lookup_value == possible_match return :value unless column_number <= row.length return row[column_number-1] elsif match_type == true if possible_match > lookup_value return :na if index == 0 previous_row = lookup_table[last_good_match] return :value unless column_number <= previous_row.length return previous_row[column_number-1] else last_good_match = index end end end # We don't have a match if match_type == true return lookup_table[last_good_match][column_number-1] else return :na end end