Soroban is a calculating engine that understands Excel formulas.

Code Climate Dependency Status Build Status

Getting Started

Simply sudo gem install soroban and then require 'soroban' in your code.

Look at the examples on this page, the tests and the API docs to get up to speed.

Example Usage

s =

s.A1 = 2
s.set('B1:B5' => [1,2,3,4,5])
s.C1 = "=SUM(A1, B1:B5, 5) + A1 ^ 3"
s.C2 = "=IF(C1>30,'Large','Tiny')"

puts s.C1             # => 30

s.bind(:input => :A1, :output => :C2)

puts s.output         # => "Tiny"

s.input = 3

puts s.output         # => "Large"
puts s.C1             # => 50


Soroban allows you to bind meaningful variable names to individual cells and to ranges of cells. When bound to a range, variables act as an array.

s.set(:A1 => 'hello', 'B1:B5' => [1,2,3,4,5])

s.bind(:foo => :A1, :bar => 'B1:B5')

puts            # => 'hello'
puts[0]         # => 1[0] = 'howdy'

puts s.B1             # => 'howdy'


Soroban formulas are strings that begin with the = symbol. It is therefore easy to persist them, which is mighty handy if you need to parse an Excel spreadsheet, rip out formulas, store everything to a database and then perform calculations based on user input.

Soroban makes this easy, as it can tell you which cells you need to add to make it possible to do the calculations you want, and it can iterate over all the cells you've defined, so you can easily rip them out for persistence.

s.F1 = "= E1 + SUM(D1:D5)"

puts s.missing        # => [:E1, :D1, :D2, :D3, :D4, :D5]

s.E1 = "= D1 ^ D2"
s.set("D1:D5" => [1,2,3,4,5])

puts s.missing             # => []

s.cells               # => {:F1=>"= E1 + SUM(D1:D5)", :E1=>"= D1 ^ D2", :D1=>"1", :D2=>"2", :D3=>"3", :D4=>"4", :D5=>"5"}


Soroban has a built-in importer for xlsx files. It requires the RubyXL gem. Use it as follows:

  :planet => :B1,
  :mass => :B2,
  :force => :B3

s = Soroban::Import::rubyXL("files/Physics.xlsx", 0, BINDINGS)

s.planet = 'Earth'
s.mass = 80
puts s.force          # => 783.459251241996

s.planet = 'Venus'
s.mass = 80
puts s.force          # => 710.044826106394

The above example parses the first sheet of Physics.xlsx, which you can download.

This import process returns a new Soroban::Sheet object that contains all the cells required to calculate the values of the bound variables, and which has the bindings set up correctly.

You can import other kinds of file using the following pattern:


Note that cells returns the label of the cell along with its raw contents. If you want to iterate over cell values (including computed values of formulas), then use walk.

s.set('D1:D5' => [1,2,3,4,5])
s.walk('D1:D5').reduce(:+)    # => 15


Soroban implements some Excel functions, but you may find that you need more than those. In that case, it's easy to add more.

Soroban::functions            # => ["AND", "AVERAGE", "EXP", "IF", "LN", "MAX", "MIN", "NOT", "OR", "SUM", "VLOOKUP"]

Soroban::define :FOO => lambda { |lo, hi|
  raise ArgumentError if lo > hi
  rand(hi-lo) + lo

s.g = "=FOO(10, 20)"

puts s.g              # => 17

Contributing to Soroban


Copyright (c) 2014 Agworld Pty. Ltd. See LICENSE.txt for further details.