Spreadsheet-like formulas on the DOM

Question

I am looking for a way to dynamically bind formulas throughout my DOM.

We have a data intensive app, and currently I write many handlers to try and recalculate and update the appropriate cells. However, this is error-prone.

I saw something that might be capable, hashigo. However it hasn't been updated in about a year and a half.

Does anyone know of something similar that is under active development? I have been searching, but this is all I have managed to find.

Ideally I only need to setup the formula, and it will handle monitoring if fields in the formula have changed, and update the value accordingly.

EDIT: I also jQuerySheet however it is way more than I can use, I just need the formula parsing aspects of it. And it's calculation engine appears to revolve too much around cells with a column/row identifier.

EDIT2: This jQuery Calculation plugin is getting closer to what I need.

EDIT3: Ultimately, I'd love to be able to write out something as simple as

$('#output').formula(" ( SUM($('.x')) + $('#y') ) / ( funcThatReturnsValue() + 4 )");

Which would result in the value of #output being recalculated whenever a value in .x or #y changed.

However, I may would setting for something as basic as this

$('#output').formula({
    formula: "(SUM(x)+y)/(j+k)",
    variables: {
        x: $('.x'),
        y: $('#y'),
        j: function() {
            return 3;
        },
        k: 4
    }
    onblur: $('.x, #y')
});

Answer 1

You can use knockout.js to get the functionality you're looking for.

Knockout.js implements an mvvm pattern in your javascript. Here is how they define MVVM:

MVVM and View Models Model-View-View Model (MVVM) is a design pattern for building user interfaces. It describes how you can keep a potentially sophisticated UI simple by splitting it into three parts:

A model: your application’s stored data. This data represents objects and operations in your business domain (e.g., bank accounts that can perform money transfers) and is independent of any UI. When using KO, you will usually make Ajax calls to some server-side code to read and write this stored model data.

A view model: a pure-code representation of the data and operations on a UI. For example, if you’re implementing a list editor, your view model would be an object holding a list of items, and exposing methods to add and remove items.

Note that this is not the UI itself: it doesn’t have any concept of buttons or display styles. It’s not the persisted data model either - it holds the unsaved data the user is working with. When using KO, your view models are pure JavaScript objects that hold no knowledge of HTML. Keeping the view model abstract in this way lets it stay simple, so you can manage more sophisticated behaviors without getting lost.

A view: a visible, interactive UI representing the state of the view model. It displays information from the view model, sends commands to the view model (e.g., when the user clicks buttons), and updates whenever the state of the view model changes.

When using KO, your view is simply your HTML document with declarative bindings to link it to the view model. Alternatively, you can use templates that generate HTML using data from your view model.

So you'll create your "model" which includes the data in the spreadsheet, along with any functions you need to recalculate data. Then you'll have your view, which automatically updates (aka recalculates) the data as the user changes things on the page.

http://knockoutjs.com

Answer 2

I just develop plugin that parse formula, using jison parser in its core, currently the formula is still limited to sum, avg, min and max, but will add more function as requested.

http://xsanisty.com/calx/