Module: LXL

Defined in:
lib/lxl.rb

Overview

LXL (Like Excel) is a mini-language that mimics Microsoft Excel formulas and is easily extended with new constants and functions.

Operators

a +  b # Add
a -  b # Subtract
a *  b # Multiply
a /  b # Divide
a =  b # Equal to
a <> b # Not equal to
a <  b # Less than
a >  b # Greater than
a <= b # Less than or equal to
a >= b # Greater than or equal to
a &  b # String concentation
a ^  b # Exponential

Constants

TRUE  # true
FALSE # false
NULL  # nil

Logical Functions

AND (a,b)
OR  (a,b)
NOT (cond)
IF  (cond,true,false)

Date/Time Functions

TODAY    ()      # current date value
NOW      ()      # current date/time value
DATE     (y,m,d) # date value
TIME     (h,m,s) # time value
DATETIME (string)  # convert a date/time string into a date/time value

List Functions

LIST (a,b,..)    # Variable length list
IN   (find,list) # True if find is in the given list (or string)

Percentages

25%   => .25
25.2% => .252

Ranges

LXL::Range provides Excel-style ranges.

Recognized formats

B3 | B3: | B3:D5 | Sheet1!B3:D5 | [Book1]Sheet1!B3:D5 | [file.xls]Sheet1!B3:D5

(the first two become B3:B3)

Collection

# Ruby Range
Range.new("B3", "D5").collect
# => ["B3", "B4", "B5", "B6", "B7", "B8", "B9",
      "C0", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9",
      "D0", "D1", "D2", "D3", "D4", "D5"]

# LXL Range
LXL::Range.new("B3", "D5").collect
# => ["B3", "B4", "B5", "C3", "C4", "C5", "D3", "D4", "D5"]

Namespaces

Extending the language is as easy as defining new constants and methods on an LXL::Namespace.

class MyLXLNamespace < LXL::Namespace
  NAME = 'John Doe'
  def upper(string) string.to_s.upcase end
end

class MyLXL < LXL::Parser
  def self.namespace_class() MyLXLNamespace end
end

MyLXL.eval('=UPPER(NAME)')
# => JOHN DOE

Symbol Registration

Register uppercase constants of the same name and value.

class MyNamespace < LXL::Namespace
  register_symbols :foo, :bar
end
LXL.new(MyNamespace.new).eval('=LIST(FOO, BAR)')
# => [:FOO, :BAR]

Deferred Calls

LXL::Deferred snapshots the symbol/arguments of a function call for later use.

class MyNamespace < LXL::Namespace
  register_deferred :foo
end
LXL.new(MyNamespace.new).eval('=FOO(1, "two", 3)')
# => <LXL::Deferred @args=[1, "two", 3] @symbol=:foo>

Notes

  • Values prefixed with = are formulas, anything else is assumed to be text.

    LXL.eval("5+5")  # => '5+5'
LXL.eval("=5+5") # => 10

  • Constant and Function names are case-insensitive.

  • The number zero is interpereted as FALSE.

  • Separating formulas with a semi-colon returns an Array of results.

Defined Under Namespace

Classes: Deferred, EmptyNamespace, Lexer, Namespace, Parser, Range, Token

Class Method Summary collapse

Class Method Details

.eval(formula) ⇒ Object

Evaluate a formula.



140
141
142
 
# File 'lib/lxl.rb', line 140

def eval(formula)
  parser_class.eval(formula)
end

.new(*args) ⇒ Object

Create a new Parser.



134
135
136
 
# File 'lib/lxl.rb', line 134

def new(*args)
  parser_class.new(*args)
end

.parser_class ⇒ Object

Default parser class.



128
129
130
 
# File 'lib/lxl.rb', line 128

def parser_class
  LXL::Parser
end

.to_b(obj) ⇒ Object

False if nil, false or zero.



146
147
148
 
# File 'lib/lxl.rb', line 146

def to_b(obj)
  [nil,false,0].include?(obj) ? false : true
end

.xlcolnum(colname) ⇒ Object

Convert an Excel column name to it’s numeric equivalent.

['A', 'F', 'AD', 'BK'].collect { |c| xlcolnum(c) } # => [1, 6, 30, 63]



154
155
156
157
158
159
160
161
 
# File 'lib/lxl.rb', line 154

def xlcolnum(colname)
  count = 0
  letters = colname.to_s.split(//)
  map = proc { |c| ('A'..'Z').collect.index(c)+1 }
  letters[0..-2].each { |l| count += 26*map.call(l) }
  count += map.call(letters.last)
  count
end