Wan Wan - 5 months ago 82
HTML Question

How to parse formula of html table cell to acting like Excel, using Javascript

I am looking for a way to make html table acting like excel using Javascript (Jquery preferred). Here are my problem. I made a table like this:

<td id="A1">10</td>
<td id="B1">10.5</td>
<td id="A2">5</td>
<td id="B2" formula="A1+B1+A2"></td>

Every td has an id, some of those tds are added a formula attribute. And I wanna to calculate those formula, give the value of formula to the td.

In the table above, here, I wanna assign the B2 value 25.5(B2=A1+B1+A2).

Look like this:

<td id="B2" formula="A1+B1+A2">25.5</td>

Can anybody give me a clue? Thanks.

PS: Here are some resource that I found, but they do not solve my problem

  1. Spreadsheet-like formulas on the DOM

  2. http://zaach.github.com/jison/docs/

Thanks a lot @palindrom. I finally solved my problem based on your answer.

Here is my code:

//1 get formula attribute
var formula = $(this).attr('formula');

//2 use regex to make expression, with the surpport of "A122", "AB1" and more
var expression = formula.replace(/([A-Z]+[0-9]+)/g, "parseFloat(\$('#$1').html())") ;

//3 eval the expression
var result = eval( expression );

//4 set the value


Should work for simple math, didnt try:

$("td[formula]").each( function() {
  this.html( eval( this.attr("formula").replace(/[a-z][0-9]/g, "\$('#$0').html()") ) );
} );

Edit: If you have functions like sum in the formulas, implement them as javascript functions and they should work too.