Module: Furud::Formula

Defined in:
lib/furud/formula.rb,
sig/furud.rbs

Defined Under Namespace

Classes: Lexer, Parser, Token

Constant Summary collapse

COLUMNS = 
(1..16_384).to_h do |number|
  value = number
  name = +""
  while value.positive?
    value, remainder = (value - 1).divmod(26)
    name.prepend((65 + remainder).chr)
  end
  [name, number]
end.freeze
COLUMN_NAMES = 
COLUMNS.invert.freeze
ERROR_CODES = 
Furud::ERROR_CODES.to_h { |code, text| [text.upcase, code] }.freeze
PRECEDENCE = 
{ "=" => 10, "<>" => 10, "<" => 10, ">" => 10, "<=" => 10, ">=" => 10,
"&" => 20, "+" => 30, "-" => 30, "*" => 40, "/" => 40, "^" => 50 }.freeze

Class Method Summary collapse

Class Method Details

.adjust(ast, operation) ⇒ Node

Parameters:

Returns:



65
66
67
68
 
# File 'lib/furud/formula.rb', line 65

def adjust(ast, operation)
  operation = normalize_adjustment(operation)
  adjust_node(ast, operation)
end

.adjust_node(node, operation) ⇒ Object 



166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/furud/formula.rb', line 166

def adjust_node(node, operation)
  case node.type
  when :reference, :qualified_reference
    adjust_reference(node.value, operation, node.type)
  when :range
    first_node, last_node = node.children
    first, last = first_node.value, last_node.value
    adjusted = adjust_range(first, last, operation)
    adjusted.is_a?(ErrorValue) ? Node.new(type: :error, value: adjusted) :
      Node.new(type: :range, value: nil, children: [ref_node(adjusted[0], first_node.type), ref_node(adjusted[1], last_node.type)])
  else
    children = node.children.map { |child| adjust_node(child, operation) }
    children == node.children ? node : Node.new(type: node.type, value: node.value, children: children)
  end
end

.adjust_range(first, last, operation) ⇒ Object 



198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/furud/formula.rb', line 198

def adjust_range(first, last, operation)
  axis = operation.type.to_s.end_with?("rows") ? :row : :column
  kind = operation.type.to_s.start_with?("insert") ? :insert : :delete
  sheet = first.sheet || last.sheet
  return [first, last] unless operation.sheet.nil? || sheet == operation.sheet

  start = [first.public_send(axis), last.public_send(axis)].min
  finish = [first.public_send(axis), last.public_send(axis)].max
  if kind == :insert
    if operation.at <= start
      start += operation.count
      finish += operation.count
    elsif operation.at <= finish
      finish += operation.count
    end
  else
    deleted_end = operation.at + operation.count - 1
    overlap = [finish, deleted_end].min - [start, operation.at].max + 1
    if overlap >= finish - start + 1
      return ErrorValue.new(code: :ref)
    elsif overlap.positive?
      finish -= overlap
      start = operation.at if start >= operation.at
    elsif start > deleted_end
      start -= operation.count
      finish -= operation.count
    end
  end
  first_value = first.public_send(axis)
  last_value = last.public_send(axis)
  reversed = first_value > last_value
  if axis == :row
    new_first = first.with(row: reversed ? finish : start)
    new_last = last.with(row: reversed ? start : finish)
  else
    new_first = first.with(column: reversed ? finish : start)
    new_last = last.with(column: reversed ? start : finish)
  end
  [new_first, new_last]
end

.adjust_reference(reference, operation, node_type) ⇒ Object 



182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
 
# File 'lib/furud/formula.rb', line 182

def adjust_reference(reference, operation, node_type)
  axis = operation.type.to_s.end_with?("rows") ? :row : :column
  kind = operation.type.to_s.start_with?("insert") ? :insert : :delete
  return ref_node(reference, node_type) unless operation.sheet.nil? || reference.sheet == operation.sheet

  coordinate = reference.public_send(axis)
  if kind == :insert
    coordinate += operation.count if coordinate >= operation.at
  elsif coordinate >= operation.at && coordinate < operation.at + operation.count
    return Node.new(type: :error, value: ErrorValue.new(code: :ref))
  elsif coordinate >= operation.at + operation.count
    coordinate -= operation.count
  end
  ref_node(reference.with(axis => coordinate), node_type)
end

.coerce_reference(reference) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/furud/formula.rb', line 70

def coerce_reference(reference)
  case reference
  when Reference then reference
  when Hash
    Reference.new(sheet: reference[:sheet] || reference["sheet"],
                  row: reference[:row] || reference["row"] || 1,
                  column: reference[:column] || reference["column"] || 1,
                  absolute_row: reference[:absolute_row] || reference["absolute_row"],
                  absolute_column: reference[:absolute_column] || reference["absolute_column"])
  when nil then Reference.new(sheet: nil, row: 1, column: 1)
  else raise ArgumentError, "origin must be a Furud::Reference, Hash, or nil"
  end
end

.column_name(number) ⇒ Object 



88
89
90
 
# File 'lib/furud/formula.rb', line 88

def column_name(number)
  COLUMN_NAMES.fetch(number) { raise ParseError, "column outside A:XFD" }
end

.column_number(name) ⇒ Object 



84
85
86
 
# File 'lib/furud/formula.rb', line 84

def column_number(name)
  COLUMNS[name.upcase]
end

.normalize_adjustment(operation) ⇒ Object 



239
240
241
242
243
244
245
 
# File 'lib/furud/formula.rb', line 239

def normalize_adjustment(operation)
  return operation if operation.is_a?(Adjustment)

  values = operation.transform_keys(&:to_sym)
  Adjustment.new(type: values.fetch(:type), sheet: values[:sheet],
                 at: values.fetch(:at), count: values.fetch(:count, 1))
end

.parse(source, origin: nil) ⇒ Node

Parameters:

  • source (String)
  • origin: (Reference, Hash[Symbol, untyped], nil) (defaults to: nil)

Returns:



22
23
24
 
# File 'lib/furud/formula.rb', line 22

def parse(source, origin: nil)
  Parser.new(source.to_s, origin: coerce_reference(origin)).parse
end

.ref_error_node ⇒ Object 



162
163
164
 
# File 'lib/furud/formula.rb', line 162

def ref_error_node
  Node.new(type: :error, value: ErrorValue.new(code: :ref))
end

.ref_node(reference, node_type = :reference) ⇒ Object 



247
248
249
250
251
 
# File 'lib/furud/formula.rb', line 247

def ref_node(reference, node_type = :reference)
  return Node.new(type: :error, value: ErrorValue.new(code: :ref)) unless reference.row.between?(1, 1_048_576) && reference.column.between?(1, 16_384)

  Node.new(type: node_type, value: reference)
end

.references(ast) ⇒ Array[Reference | Area]

Parameters:

Returns:



30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/furud/formula.rb', line 30

def references(ast)
  result = []
  collect = lambda do |node|
    case node.type
    when :reference, :qualified_reference
      result << node.value
    when :range
      first, last = node.children.map(&:value)
      result << Area.new(sheet: first.sheet || last.sheet,
                         top: first.row, left: first.column,
                         bottom: last.row, right: last.column)
    else
      node.children.each { |child| collect.call(child) }
    end
  end
  collect.call(ast)
  result.uniq
end

.render(ast, origin: nil) ⇒ String

Parameters:

  • ast (Node)
  • origin: (Reference, Hash[Symbol, untyped], nil) (defaults to: nil)

Returns:

  • (String) 


26
27
28
 
# File 'lib/furud/formula.rb', line 26

def render(ast, origin: nil)
  "=#{render_node(ast, coerce_reference(origin))}"
end

.render_literal(value) ⇒ Object 



253
254
255
256
257
258
259
260
 
# File 'lib/furud/formula.rb', line 253

def render_literal(value)
  case value
  when String then "\"#{value.gsub('"', '""')}\""
  when true then "TRUE"
  when false then "FALSE"
  else value.to_s
  end
end

.render_node(node, origin, parent_precedence = 0) ⇒ Object 



103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/furud/formula.rb', line 103

def render_node(node, origin, parent_precedence = 0)
  case node.type
  when :literal then render_literal(node.value)
  when :reference then render_reference_for(node.value, origin)
  when :qualified_reference then render_reference(node.value)
  when :range
    first, last = node.children
    if last.type == :reference && first.value.sheet == last.value.sheet
      last = Node.new(type: :reference, value: last.value.with(sheet: nil))
    end
    "#{render_node(first, origin, 100)}:#{render_node(last, origin, 100)}"
  when :name then node.value
  when :error then node.value.to_s
  when :array
    "{" + node.children.map { |row| row.children.map { |child| render_node(child, origin) }.join(",") }.join(";") + "}"
  when :call
    "#{node.value}(#{node.children.map { |child| render_node(child, origin) }.join(",")})"
  when :unary
    op = node.value
    child = render_node(node.children.first, origin, 60)
    wrap("#{op}#{child}", 60, parent_precedence)
  when :postfix
    wrap("#{render_node(node.children.first, origin, 70)}%", 70, parent_precedence)
  when :binary
    precedence = PRECEDENCE.fetch(node.value)
    left, right = node.children
    right_precedence = precedence + (node.value == "^" ? 0 : 1)
    wrap("#{render_node(left, origin, precedence)}#{node.value}#{render_node(right, origin, right_precedence)}",
         precedence, parent_precedence)
  else raise Error, "unknown formula node: #{node.type}"
  end
end

.render_reference(reference) ⇒ Object 



92
93
94
95
96
97
 
# File 'lib/furud/formula.rb', line 92

def render_reference(reference)
  prefix = reference.sheet ? "#{render_sheet(reference.sheet)}!" : ""
  row = reference.absolute_row ? "$#{reference.row}" : reference.row.to_s
  column = reference.absolute_column ? "$#{column_name(reference.column)}" : column_name(reference.column)
  "#{prefix}#{column}#{row}"
end

.render_reference_for(reference, origin) ⇒ Object 



99
100
101
 
# File 'lib/furud/formula.rb', line 99

def render_reference_for(reference, origin)
  render_reference(reference.sheet == origin.sheet ? reference.with(sheet: nil) : reference)
end

.render_sheet(sheet) ⇒ Object 



262
263
264
265
266
 
# File 'lib/furud/formula.rb', line 262

def render_sheet(sheet)
  return sheet if sheet.match?(/\A[A-Za-z_][A-Za-z0-9_.]*\z/)

  "'#{sheet.gsub("'", "''")}'"
end

.transform(node, &block) ⇒ Object 



144
145
146
147
148
149
150
 
# File 'lib/furud/formula.rb', line 144

def transform(node, &block)
  replaced = block.call(node)
  return replaced unless replaced.equal?(node)

  children = node.children.map { |child| transform(child, &block) }
  children == node.children ? node : Node.new(type: node.type, value: node.value, children: children)
end

.translate(ast, from:, to:) ⇒ Node

Parameters:

Returns:



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/furud/formula.rb', line 49

def translate(ast, from:, to:)
  from = coerce_reference(from)
  to = coerce_reference(to)
  transform(ast) do |node|
    case node.type
    when :range
      children = node.children.map { |child| translate_reference(child, from, to) }
      children.any? { |child| child.type == :error } ? ref_error_node : Node.new(type: :range, children: children)
    when :reference, :qualified_reference
      translate_reference(node, from, to)
    else
      node
    end
  end
end

.translate_reference(node, from, to) ⇒ Object 



152
153
154
155
156
157
158
159
160
 
# File 'lib/furud/formula.rb', line 152

def translate_reference(node, from, to)
  reference = node.value
  row = reference.absolute_row ? reference.row : to.row + reference.row - from.row
  column = reference.absolute_column ? reference.column : to.column + reference.column - from.column
  return ref_error_node if row < 1 || column < 1

  sheet = node.type == :reference && reference.sheet == from.sheet ? to.sheet : reference.sheet
  ref_node(reference.with(row: row, column: column, sheet: sheet), node.type)
end

.visit(node) {|node| ... } ⇒ Object

Yields:

  • (node) 


139
140
141
142
 
# File 'lib/furud/formula.rb', line 139

def visit(node, &block)
  yield node
  node.children.each { |child| visit(child, &block) }
end

.wrap(text, precedence, parent_precedence) ⇒ Object 



268
269
270
 
# File 'lib/furud/formula.rb', line 268

def wrap(text, precedence, parent_precedence)
  precedence < parent_precedence ? "(#{text})" : text
end