Class: RubyFromExcel::FormulaBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/formulae/compile/formula_builder.rb

Constant Summary collapse

OPERATOR_CONVERSIONS = 
{ '^' => '**' }
COMPARATOR_CONVERSIONS = 
{'=' => '==' }

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(formula_cell = nil) ⇒ FormulaBuilder

Returns a new instance of FormulaBuilder.



51
52
53
 
# File 'lib/formulae/compile/formula_builder.rb', line 51

def initialize(formula_cell = nil)
  self.formula_cell = formula_cell
end

Instance Attribute Details

#formula_cell ⇒ Object

Returns the value of attribute formula_cell.



49
50
51
 
# File 'lib/formulae/compile/formula_builder.rb', line 49

def formula_cell
  @formula_cell
end

Class Method Details

.excel_function(name, name_to_use_in_ruby = name) ⇒ Object 



146
147
148
149
150
 
# File 'lib/formulae/compile/formula_builder.rb', line 146

def self.excel_function(name,name_to_use_in_ruby = name)
  define_method("#{name}_function") do |*args|
    standard_function name_to_use_in_ruby, args
  end
end

Instance Method Details

#area(start_area, end_area) ⇒ Object 



83
84
85
 
# File 'lib/formulae/compile/formula_builder.rb', line 83

def area(start_area,end_area)
  "a('#{cell(start_area)}','#{cell(end_area)}')"
end

#arithmetic(*strings) ⇒ Object 



314
315
316
 
# File 'lib/formulae/compile/formula_builder.rb', line 314

def arithmetic(*strings)
  strings.map { |s| s.visit(self) }.join
end

#attempt_to_calculate_index(lookup_array, row_number, column_number = :ignore) ⇒ Object 



196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/formulae/compile/formula_builder.rb', line 196

def attempt_to_calculate_index(lookup_array,row_number,column_number = :ignore)
  lookup_array = range_for(lookup_array)
  row_number = single_value_for(row_number)
  column_number = single_value_for(column_number) unless column_number == :ignore
  return nil unless lookup_array
  return nil unless row_number
  return nil unless column_number
  if column_number == :ignore
    ref = FunctionCompiler.new(formula_cell.worksheet).calculate_index_formula(lookup_array,row_number,nil,:index_reference)
  else
    ref = FunctionCompiler.new(formula_cell.worksheet).calculate_index_formula(lookup_array,row_number,column_number,:index_reference)
  end
  return nil unless ref
  return nil if ref.is_a?(Symbol)
  return ref.to_ruby(true)
rescue DependsOnCalculatedFormulaError
  return nil
end

#attempt_to_calculate_match(lookup_value, lookup_array, match_type = :ignore) ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
 
# File 'lib/formulae/compile/formula_builder.rb', line 215

def attempt_to_calculate_match(lookup_value,lookup_array,match_type = :ignore)
    lookup_value = single_value_for(lookup_value)
    lookup_array = range_for(lookup_array)
    match_type = single_value_for(match_type) unless match_type == :ignore
    return nil unless lookup_value
    return nil unless lookup_array
    return nil if match_type == nil
    result = nil
    if match_type == :ignore
      result = FunctionCompiler.new(formula_cell.worksheet).match(lookup_value,lookup_array).to_f
    else
      result = FunctionCompiler.new(formula_cell.worksheet).match(lookup_value,lookup_array,match_type)
    end
    result.respond_to?(:to_f) ? result.to_f : result
  rescue DependsOnCalculatedFormulaError
    return nil
end

#attempt_to_parse_indirect(text_formula) ⇒ Object 



264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
 
# File 'lib/formulae/compile/formula_builder.rb', line 264

def attempt_to_parse_indirect(text_formula)
  #puts "Attempting to parse indirect #{text_formula.inspect}"
  return parse_and_visit(text_formula.first) if text_formula.type == :string
  return nil unless text_formula.type == :string_join
  reformated_indirect = text_formula.map do |non_terminal|
    if non_terminal.respond_to?(:type)
      case non_terminal.type
      when :string, :number
        non_terminal
      when :cell
        cell = formula_cell.worksheet.cell(non_terminal.visit(self))
        if cell
          return nil unless cell.can_be_replaced_with_value?
          cell.value_for_including
        else
          ""
        end
      when :sheet_reference, :named_reference, :table_reference, :local_table_reference
        reference = non_terminal.visit(self)
        # puts reference
        return nil unless reference =~ /^(sheet\d+)\.([a-z]+\d+)$/
        cell = formula_cell.worksheet.workbook.worksheets[$1].cell($2)
        if cell
          return nil unless cell.can_be_replaced_with_value?
          cell.value_for_including
        else
          ""
        end
      else
        return nil
      end
    else
      non_terminal
    end
  end
  # puts "Reformatted indirect: #{reformated_indirect.join}"
  parse_and_visit(reformated_indirect.join)
end

#boolean_false ⇒ Object 



332
333
334
 
# File 'lib/formulae/compile/formula_builder.rb', line 332

def boolean_false
  "false"
end

#boolean_true ⇒ Object 



328
329
330
 
# File 'lib/formulae/compile/formula_builder.rb', line 328

def boolean_true
  "true"
end

#brackets(*expression) ⇒ Object 



67
68
69
 
# File 'lib/formulae/compile/formula_builder.rb', line 67

def brackets(*expression)
  "(#{expression.map{ |e| e.visit(self)}.join})"
end

#cell(reference) ⇒ Object 



79
80
81
 
# File 'lib/formulae/compile/formula_builder.rb', line 79

def cell(reference)
  Reference.new(reference).to_ruby
end

#column_range(start_area, end_area) ⇒ Object 



87
88
89
 
# File 'lib/formulae/compile/formula_builder.rb', line 87

def column_range(start_area,end_area)
  "c('#{cell(start_area)}','#{cell(end_area)}')"
end

#comparator(string) ⇒ Object 



324
325
326
 
# File 'lib/formulae/compile/formula_builder.rb', line 324

def comparator(string)
  COMPARATOR_CONVERSIONS[string] || string
end

#comparison(left, comparator, right) ⇒ Object 



310
311
312
 
# File 'lib/formulae/compile/formula_builder.rb', line 310

def comparison(left,comparator,right)
  "excel_comparison(#{left.visit(self)},\"#{comparator.visit(self)}\",#{right.visit(self)})"
end

#external_reference(external_reference_number, remainder_of_reference) ⇒ Object 



95
96
97
98
 
# File 'lib/formulae/compile/formula_builder.rb', line 95

def external_reference(external_reference_number,remainder_of_reference)
  puts "Warning, external references not supported (#{formula_cell}) #{remainder_of_reference}"
  remainder_of_reference.visit(self)
end

#formula(*expressions) ⇒ Object 



55
56
57
 
# File 'lib/formulae/compile/formula_builder.rb', line 55

def formula(*expressions)
  expressions.map { |e| e.visit(self) }.join
end

#function(name, *args) ⇒ Object

Raises:



141
142
143
144
 
# File 'lib/formulae/compile/formula_builder.rb', line 141

def function(name,*args)
  raise ExcelFunctionNotImplementedError.new("#{name}(#{args})") unless self.respond_to?("#{name.downcase}_function")
  self.send("#{name.downcase}_function",*args)
end

#index_function(*args) ⇒ Object 



186
187
188
189
 
# File 'lib/formulae/compile/formula_builder.rb', line 186

def index_function(*args)
  attempt_to_calculate_index(*args) ||
  standard_function("index",args)
end

#indirect_function(text_formula) ⇒ Object 



260
261
262
 
# File 'lib/formulae/compile/formula_builder.rb', line 260

def indirect_function(text_formula)
  attempt_to_parse_indirect(text_formula) || (formula_cell.worksheet.workbook.indirects_used = true; "indirect(#{text_formula.visit(self)},'#{formula_cell && formula_cell.reference}')")
end

#local_table_reference(structured_reference) ⇒ Object 



121
122
123
 
# File 'lib/formulae/compile/formula_builder.rb', line 121

def local_table_reference(structured_reference)
  Table.reference_for_local_reference(formula_cell,structured_reference).to_s
end

#match_function(*args) ⇒ Object 



191
192
193
194
 
# File 'lib/formulae/compile/formula_builder.rb', line 191

def match_function(*args)
  attempt_to_calculate_match(*args) ||
  standard_function("match",args)
end

#named_reference(name, worksheet = nil) ⇒ Object 



71
72
73
74
75
76
77
 
# File 'lib/formulae/compile/formula_builder.rb', line 71

def named_reference(name, worksheet = nil)
  worksheet ||= formula_cell ? formula_cell.worksheet : nil
  return ":name" unless worksheet
  worksheet.named_references[name.to_method_name] || 
  worksheet.workbook.named_references[name.to_method_name] ||
  ":name"
end

#null ⇒ Object 



336
337
338
 
# File 'lib/formulae/compile/formula_builder.rb', line 336

def null
  0.0
end

#number(number_as_text) ⇒ Object 



59
60
61
 
# File 'lib/formulae/compile/formula_builder.rb', line 59

def number(number_as_text)
  number_as_text.to_f
end

#operator(excel_operator) ⇒ Object 



129
130
131
 
# File 'lib/formulae/compile/formula_builder.rb', line 129

def operator(excel_operator)
  OPERATOR_CONVERSIONS[excel_operator] || excel_operator
end

#parse_and_visit(text) ⇒ Object 



303
304
305
306
307
308
 
# File 'lib/formulae/compile/formula_builder.rb', line 303

def parse_and_visit(text)
  ast = Formula.parse(text)
  # p [text,ast]
  return ":name" unless ast
  ast.visit(self.class.new(formula_cell))
end

#percentage(percentage_as_text) ⇒ Object 



63
64
65
 
# File 'lib/formulae/compile/formula_builder.rb', line 63

def percentage(percentage_as_text)
  (percentage_as_text.to_f/100).to_s
end

#prefix(prefix, thing) ⇒ Object 



318
319
320
 
# File 'lib/formulae/compile/formula_builder.rb', line 318

def prefix(prefix,thing)
 "#{prefix.visit(self)}#{thing.visit(self)}"
end

#range_for(ast) ⇒ Object 



250
251
252
253
254
255
256
257
258
 
# File 'lib/formulae/compile/formula_builder.rb', line 250

def range_for(ast)
  return nil unless ast.respond_to?(:visit)
  return nil unless formula_cell
  return nil unless formula_cell.worksheet
  ast = ast.visit(self)
  return nil unless ast =~ /^(sheet\d+)?\.?a\('([a-z]+\d+)','([a-z]+\d+)'\)$/
  worksheet = $1 ? formula_cell.worksheet.workbook.worksheets[$1] : formula_cell.worksheet
  FunctionCompiler.new(worksheet).a($2,$3)
end

#row_range(start_area, end_area) ⇒ Object 



91
92
93
 
# File 'lib/formulae/compile/formula_builder.rb', line 91

def row_range(start_area,end_area)
  "r(#{cell(start_area)},#{cell(end_area)})"
end

#sheet_reference(sheet_name, reference) ⇒ Object Also known as: quoted_sheet_reference 



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/formulae/compile/formula_builder.rb', line 100

def sheet_reference(sheet_name,reference)
  sheet_name = $1 if sheet_name.to_s =~ /^(\d+)\.0+$/
  if sheet_name =~ /^\[\d+\]/
    puts "Warning, #{formula_cell} refers to an external workbook in '#{sheet_name}'"
    sheet_name.gsub!(/^\[\d+\]/,'')
  end
  if reference.type == :named_reference
    return ":name" unless formula_cell
    worksheet = formula_cell.worksheet.workbook.worksheets[SheetNames.instance[sheet_name]]
    # raise Exception.new("#{sheet_name.inspect} not found in #{SheetNames.instance} and therefore in #{formula_cell.worksheet.workbook.worksheets.keys}") unless worksheet
    return ":ref" unless worksheet
    named_reference(reference.first,worksheet)
  else
    "#{SheetNames.instance[sheet_name]}.#{reference.visit(self)}"
  end
end

#single_value_for(ast) ⇒ Object 



233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/formulae/compile/formula_builder.rb', line 233

def single_value_for(ast)
  return nil unless ast.respond_to?(:visit)
  ast = ast.visit(self)
  return true if ast == "true"
  return false if ast == "false"
  return ast if ast.is_a?(Numeric)
  return ast if ast =~ /^[0-9.]+$/
  return $1 if ast =~ /^"([^"]*)"$/
  return nil unless formula_cell
  return nil unless formula_cell.worksheet
  return nil unless ast =~ /^(sheet\d+)?\.?([a-z]+\d+)$/
  cell = $1 ? formula_cell.worksheet.workbook.worksheets[$1].cell($2) : formula_cell.worksheet.cell($2)
  return nil unless cell
  return nil unless cell.can_be_replaced_with_value?
  cell.value_for_including
end

#standard_function(name_to_use_in_ruby, args) ⇒ Object 



182
183
184
 
# File 'lib/formulae/compile/formula_builder.rb', line 182

def standard_function(name_to_use_in_ruby,args)
  "#{name_to_use_in_ruby}(#{args.map {|a| a.visit(self) }.join(',')})"
end

#string(string_text) ⇒ Object 



137
138
139
 
# File 'lib/formulae/compile/formula_builder.rb', line 137

def string(string_text)
  string_text.gsub('""','"').inspect
end

#string_join(*strings) ⇒ Object 



133
134
135
 
# File 'lib/formulae/compile/formula_builder.rb', line 133

def string_join(*strings)
  strings.map { |s| s.type == :string ? s.visit(self) : "(#{s.visit(self)}).to_s"}.join('+')
end

#table_reference(table_name, structured_reference) ⇒ Object 



117
118
119
 
# File 'lib/formulae/compile/formula_builder.rb', line 117

def table_reference(table_name,structured_reference)
  Table.reference_for(table_name,structured_reference,formula_cell && formula_cell.reference).to_s
end