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...

Highlighting table row on mouse over using JQuery...

<table border='0'>
        <thead>
            <tr>
                <th>head 1</th>
                <th>head 2</th>
                <th>head 3</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>data 1</td>
                <td>data 2</td>
                <td>data 3</td>
            </tr>
            <tr>
                <td>data 1</td>
                <td>data 2</td>
                <td>data 3</td>
            </tr>
            <tr>
                <td>data 1</td>
                <td>data 2</td>
                <td>data 3</td>
            </tr>
            <tr>
                <td>data 1</td>
                <td>data 2</td>
                <td>data 3</td>
            </tr>
        </tbody>
    </table>

Demo

head 1	head 2	head 3
data 1	data 2	data 3
data 1	data 2	data 3
data 1	data 2	data 3
data 1	data 2	data 3

Piece of cake...

$(document).ready( function() {
        $('tbody tr').hover(
            function() { $(this).css('background', '#cecece'); },
            function() { $(this).css('background', '#ffffff'); }
        );
    });

JQuery pluggins summary and best practices...

Writing jQuery plugins allows you to make the most out of the library and abstract your most clever and useful functions out into reusable code that can save you time and make your development even more efficient. Here's a brief summary of the post and what to keep in mind when developing your next jQuery plugin:

• Always wrap your plugin in (function( $ ){ // plugin goes here })( jQuery );
• Don't redundantly wrap the this keyword in the immediate scope of your plugin's function
• Unless you're returning an intrinsic value from your plugin, always have your plugin's function return the this keyword to maintain chainability.
• Rather than requiring a lengthy amount of arguments, pass your plugin settings in an object literal that can be extended over the plugin's defaults.
• Don't clutter the jQuery.fn object with more than one namespace per plugin.
• Always namespace your methods, events and data.
• jQuery.fn is pronounced jQuery effin'

Retrieving checkboxes values using JQuery...

I have an html like this in my page

    ...
    <div id="container1">
        <input type="checkbox" value="1" /> a
        <input type="checkbox" value="2" /> b
        <input type="checkbox" value="3" /> c
        <input type="checkbox" value="4" /> d
        <input type="checkbox" value="5" /> e
        <input type="checkbox" value="6" /> f
    </div>

    <div id="container2">
        <input type="checkbox" value="11" /> a1
        <input type="checkbox" value="12" /> b1
        <input type="checkbox" value="13" /> c1
        <input type="checkbox" value="14" /> d1
        <input type="checkbox" value="15" /> e1
        <input type="checkbox" value="16" /> f1
    </div>
    <input id="mybutton" type="button" value="click me" />
    ...

Demo

container1
a b c d e f

container2
a1 b1 c1 d1 e1 f1

result

Now what I want is, whenever I click the button I want to retrieve all the values from the checked checkboxes. This is pretty easy in JQuery. Here's how my JS will look like

    ...
    $("#mybutton").click( function() {
        var myarray = [];
        
        $('input:checkbox:checked').each( function() {
            myarray.push($(this).val());
        });
        
        alert(myarray.join(', '));
    } );
    ...

What if I want it only from the first <div>? I just need to pass context to the selector

    ...
    $("#mybutton").click( function() {
        var myarray = [];
        
        // passing container1 as context to selector
        $('input:checkbox:checked', '#container1').each( function() {
            myarray.push($(this).val());
        });
        
        alert(myarray.join(', '));
    } );
    ...

JQuery makes my life a lot easier :).

The beauty of JQuery...

Query by element tag name

    $("div")

Query by element id

    $("#myid")

Query by css class name

    $(".css-whatever")

Query by input type

    $("input:checkbox")

Query by a set of element ids

    $("#myid1,#myid2,#myid3")

Query by input type but exclude the one with 'whatever' id

    $("input:checkbox:not(#whatever)")