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.



53
54
55
 
# File 'lib/formulae/compile/formula_builder.rb', line 53

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

Instance Attribute Details

#formula_cell ⇒ Object

Returns the value of attribute formula_cell.



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

def formula_cell
  @formula_cell
end

Class Method Details

.excel_function(name, name_to_use_in_ruby = name) ⇒ Object 



148
149
150
151
152
 
# File 'lib/formulae/compile/formula_builder.rb', line 148

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 



85
86
87
 
# File 'lib/formulae/compile/formula_builder.rb', line 85

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

#arithmetic(*strings) ⇒ Object 



316
317
318
 
# File 'lib/formulae/compile/formula_builder.rb', line 316

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

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



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

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 



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

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 



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
302
303
 
# File 'lib/formulae/compile/formula_builder.rb', line 266

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 



334
335
336
 
# File 'lib/formulae/compile/formula_builder.rb', line 334

def boolean_false
  "false"
end

#boolean_true ⇒ Object 



330
331
332
 
# File 'lib/formulae/compile/formula_builder.rb', line 330

def boolean_true
  "true"
end

#brackets(*expression) ⇒ Object 



69
70
71
 
# File 'lib/formulae/compile/formula_builder.rb', line 69

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

#cell(reference) ⇒ Object 



81
82
83
 
# File 'lib/formulae/compile/formula_builder.rb', line 81

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

#column_range(start_area, end_area) ⇒ Object 



89
90
91
 
# File 'lib/formulae/compile/formula_builder.rb', line 89

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

#comparator(string) ⇒ Object 



326
327
328
 
# File 'lib/formulae/compile/formula_builder.rb', line 326

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

#comparison(left, comparator, right) ⇒ Object 



312
313
314
 
# File 'lib/formulae/compile/formula_builder.rb', line 312

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 



97
98
99
100
 
# File 'lib/formulae/compile/formula_builder.rb', line 97

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 



57
58
59
 
# File 'lib/formulae/compile/formula_builder.rb', line 57

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

#function(name, *args) ⇒ Object

Raises:



143
144
145
146
 
# File 'lib/formulae/compile/formula_builder.rb', line 143

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 



188
189
190
191
 
# File 'lib/formulae/compile/formula_builder.rb', line 188

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

#indirect_function(text_formula) ⇒ Object 



262
263
264
 
# File 'lib/formulae/compile/formula_builder.rb', line 262

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 



123
124
125
 
# File 'lib/formulae/compile/formula_builder.rb', line 123

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

#match_function(*args) ⇒ Object 



193
194
195
196
 
# File 'lib/formulae/compile/formula_builder.rb', line 193

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

#named_reference(name, worksheet = nil) ⇒ Object 



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

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

#null ⇒ Object 



338
339
340
 
# File 'lib/formulae/compile/formula_builder.rb', line 338

def null
  0.0
end

#number(number_as_text) ⇒ Object 



61
62
63
 
# File 'lib/formulae/compile/formula_builder.rb', line 61

def number(number_as_text)
  number_as_text.to_f
end

#operator(excel_operator) ⇒ Object 



131
132
133
 
# File 'lib/formulae/compile/formula_builder.rb', line 131

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

#parse_and_visit(text) ⇒ Object 



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

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 



65
66
67
 
# File 'lib/formulae/compile/formula_builder.rb', line 65

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

#prefix(prefix, thing) ⇒ Object 



320
321
322
 
# File 'lib/formulae/compile/formula_builder.rb', line 320

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

#range_for(ast) ⇒ Object 



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

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 



93
94
95
 
# File 'lib/formulae/compile/formula_builder.rb', line 93

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 



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

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 



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

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 



184
185
186
 
# File 'lib/formulae/compile/formula_builder.rb', line 184

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 



139
140
141
 
# File 'lib/formulae/compile/formula_builder.rb', line 139

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

#string_join(*strings) ⇒ Object 



135
136
137
 
# File 'lib/formulae/compile/formula_builder.rb', line 135

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 



119
120
121
 
# File 'lib/formulae/compile/formula_builder.rb', line 119

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