Sharing html table with sum formula that works on Excel and web page...

I'm in a situation where I need to include Excel SUM formula when exporting data from html. Questions raised in my head. What's the purpose of exporting it to Excel in the first place? It gives us the ability to manipulate data with whatever formulas available in Excel. Then why do we want to have it in html? I have no idea but at least I've found a way that I won't need to generate it twice. That matters to me.

Here's the trick. When generating an html, add the id to the cell that needs to be calculate exactly like cell in Excel. As for the cell with the sum value, add css class to it (doesn't need to be exist in css, it's just for the sake of searching later on).

<table>
        <thead>
            <th>header 1</th>
            <th>header 2</th>
            <th>header 3</th>
            <th>total</th>
        </thead>
        <tr>
            <td id='A2'>1</td>
            <td id='B2'>2</td>
            <td id='C2'>3</td>
            <td id='D2' class='sum'>=SUM(A2,B2,C2)</td>
        </tr>
        <tr>
            <td id='A3'>4</td>
            <td id='B3'>5</td>
            <td id='C3'>6</td>
            <td id='D3' class='sum'>=SUM(A3,B3,C3)</td>
        </tr>
        <tr>
            <td id='A4'>7</td>
            <td id='B4'>8</td>
            <td id='C4'>9</td>
            <td id='D4' class='sum'>=SUM(A4,B4,C4)</td>
        </tr>
        <tr>
            <td class='sum'>=SUM(A2,A3,A4)</td>
            <td class='sum'>=SUM(B2,B3,B4)</td>
            <td class='sum'>=SUM(C2,C3,C4)</td>
            <td class='sum'>=SUM(D2,D3,D4)</td>
        </tr>
    </table>

Now if you copy the html above, paste it into a notepad and save it to .xls. You'll get an Excel sheet with calculated value based on the SUM formula provided. But then of course the formula won't work on the web site. You'll see table like shown below.

Demo

header 1	header 2	header 3	total
1	2	3	=SUM(A2,B2,C2)
4	5	6	=SUM(A3,B3,C3)
7	8	9	=SUM(A4,B4,C4)
=SUM(A2,A3,A4)	=SUM(B2,B3,B4)	=SUM(C2,C3,C4)	=SUM(D2,D3,D4)

This is where JQuery come in handy. The logic,

• Search for all the elements that has 'sum' class
• Convert the formula into something that is understandable by JQuery
• Loop through the elements and change the value from formula into calculated value

$(document).ready( function() {
        // search the elements that has 'sum' class
        $('.sum').each(function() {
            // convert the formula from =SUM(A2,B2,C2) into #A2,#B2,#C2
            var formula = convert($(this).html());
            var total = 0;

            $(formula).each(function() {
                total += parseInt($(this).html());
            });

            // replace with calculated value
            $(this).html(total);
        });
    });
    
    // converting from =SUM(A2,B2,C2) into #A2,#B2,#C2
    function convert(formula)
    {
        var newform = formula.substring(formula.indexOf("(") + 1, formula.indexOf(")"));
        var arrayform = newform.split(",");
        return "#" + arrayform.join(",#");
    }

JQuery save the day...again...