Using JavaScript, jQuery, XML, and XSL to bind a grid and automatically calculate column/row totals

This example uses JavaScript and a simple XSL transformation to bind XML data to a grid. The XSL transformation does the initial calculations for the rows and columns. There is also jQuery code to handle the blur() event on the textboxes to update the totals automatically.

by Matthew Pavey

HTML

<!-- results -->
<div id="pnlResults"></div>

<!-- xml data -->
<script id="xml" type="application/xml">
    <expenses>
        <x id="1" month="Jan" amount-gas="149.95" amount-electric="65.73" amount-water="29.73" amount-other="107.35" />
        <x id="2" month="Feb" amount-gas="125.95" amount-electric="67.99" amount-water="30.55" amount-other="105.99" />
        <x id="3" month="Mar" amount-gas="100.99" amount-electric="68.99" amount-water="29.99" amount-other="107.10" />
        <x id="4" month="Apr" amount-gas="100.59" amount-electric="67.29" amount-water="30.29" amount-other="100.50" />
        <x id="5" month="May" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="6" month="Jun" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="7" month="Jul" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="8" month="Aug" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="9" month="Sep" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="10" month="Oct" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="11" month="Nov" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
        <x id="12" month="Dec" amount-gas="0" amount-electric="0" amount-water="0" amount-other="0" />
    </expenses>
</script>

<!-- xsl data -->
<script id="xsl" type="application/xml">
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output media-type="html" omit-xml-declaration="yes" indent="yes" />
       
        <!-- variables -->
        <xsl:param name="SortExpression" />
        <xsl:param name="SortDirection" />
        <xsl:param name="SortType" />
        
        <!-- templates -->
        <xsl:template match="/">            
          <table id="expenses" border="0" cellpadding="3" cellspacing="0">
           ...

JavaScript

// format
String.prototype.format = function () {
    var args = arguments;
    return this.replace(/{(\d+)}/g, function (match, number) {
        return typeof args[number] != 'undefined'
          ? args[number]
          : match
        ;
    });
};

// format number with commas and decimal
Number.prototype.numberFormat = function (decimals, dec_point, thousands_sep) {
    dec_point = typeof dec_point !== 'undefined' ? dec_point : '.';
    thousands_sep = typeof thousands_sep !== 'undefined' ? thousands_sep : '';

    var parts = this.toFixed(decimals).split('.');
    parts[0] = parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, thousands_sep);

    return parts.join(dec_point);
};

// initialize
$(function () {
    // bind data
    BindData();

    // events
    $(".amount").blur(function () {
        // variables
        var row = $(this).attr("data-row");
        var column = $(this).attr("data-column");

        var selectorRow = ".total{0}".format(row);
        var selectorColumn = ".{0}".format(column);
        var selectorAll = ".amount";

        var lblTotalRow = $("#lbl-total-{0}".format(row));
        var lblTotalColumn = $("#lbl-total-{0}".format(column));
        var lblTotalAll = $("#lbl-total-sum");

        var totalRow = 0;
        var totalColumn = 0;
        var totalAll = 0;

        // format number
        $(this).val(parseFloat($(this).val().replace(/[^0-9$.,]/g, '').replace(/,/g, '').replace(/\$/g, '')).numberFormat(2));

        // get value
        var amount = $(this).val();

        // make sure we still have a valid number
        if (amount == 'NaN' || amount == NaN) {
            $(this).val('0.00');
        }

        // calculate total for this row
        $(selectorRow).each(function () {
            totalRow += Number($(this).val());
        });

        // calculate total for this column
        $(selectorColumn).each(function () {
            totalColumn += Number($(this).val());
        });

        // calculate total for all...