开发者

jQuery: Calculate column totals for sections in table

I have the following table:

<table class="stripeMe" cellpadding="0" cellspacing="0" width="100%">
<tbody>
    <tr>
        <th>
            MEALS
        </th>
        <th style="text-align: center;">
            02/6
        </th>
        <th style="text-align: center;">
            02/7
        </th>
        <th style="text-align: center;">
            02/8
        </th>
        <th style="text-align: center;">
            02/9
        </th>
        <th style="text-align: center;">
            02/10
        </th>
        <th style="text-align: center;">
            02/11
        </th>
        <th style="text-align: center;">
            02/12
        </th>
        <th style="text-align: center;">
            Total
        </th>
    </tr>
    <tr>
        <td>
            CONGREGATE - HEALTHY BALANCE
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/6/2011"
            name="116|2/6/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/7/2011"
            name="116|2/7/2011" value="18" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/8/2011"
            name="116|2/8/2011" value="33" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/9/2011"
            name="116|2/9/2011" value="20" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/10/2011"
            name="116|2/10/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/11/2011"
            name="116|2/11/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="116|2/12/2011"
            name="116|2/12/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" readonly="readonly" size="3" class="total"
            id="116|Total" name="116|Total" value="71" />
        </td>
    </tr>
    <tr style="background: #79a2cb;">
        <td>
            <strong>Totals</strong>
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/6/2011"
            name="MEALS|2/6/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/7/2011"
            name="MEALS|2/7/2011" value="18" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/8/2011"
            name="MEALS|2/8/2011" value="33" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/9/2011"
            name="MEALS|2/9/2011" value="20" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/10/2011"
            name="MEALS|2/10/2011" value="0" />
        &开发者_如何学JAVAlt;/td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/11/2011"
            name="MEALS|2/11/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="MEALS|2/12/2011"
            name="MEALS|2/12/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" readonly="readonly" size="3" class="total"
            id="MEALS|Total" name="MEALS|Total" value="71" />
        </td>
    </tr>
    <tr>
        <th>
            BEVERAGES
        </th>
        <th style="text-align: center;">
            02/6
        </th>
        <th style="text-align: center;">
            02/7
        </th>
        <th style="text-align: center;">
            02/8
        </th>
        <th style="text-align: center;">
            02/9
        </th>
        <th style="text-align: center;">
            02/10
        </th>
        <th style="text-align: center;">
            02/11
        </th>
        <th style="text-align: center;">
            02/12
        </th>
        <th style="text-align: center;">
            Total
        </th>
    </tr>
    <tr>
        <td>
            SKIM MILK
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/6/2011"
            name="67|2/6/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/7/2011"
            name="67|2/7/2011" value="4" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/8/2011"
            name="67|2/8/2011" value="10" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/9/2011"
            name="67|2/9/2011" value="5" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/10/2011"
            name="67|2/10/2011" value="10" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/11/2011"
            name="67|2/11/2011" value="4" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="sum" id="67|2/12/2011"
            name="67|2/12/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" readonly="readonly" size="3" class="total"
            id="67|Total" name="67|Total" value="33" />
        </td>
    </tr>

    <tr style="background: #79a2cb;">
        <td>
            <strong>Totals</strong>
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/6/2011"
            name="BEVERAGES|2/6/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/7/2011"
            name="BEVERAGES|2/7/2011" value="4" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/8/2011"
            name="BEVERAGES|2/8/2011" value="10" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/9/2011"
            name="BEVERAGES|2/9/2011" value="5" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/10/2011"
            name="BEVERAGES|2/10/2011" value="10" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/11/2011"
            name="BEVERAGES|2/11/2011" value="4" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" size="3" class="totalCategory" id="BEVERAGES|2/12/2011"
            name="BEVERAGES|2/12/2011" value="0" />
        </td>
        <td style="text-align: center;">
            <input type="text" style="text-align: center;" readonly="readonly" size="3" class="total"
            id="BEVERAGES|Total" name="BEVERAGES|Total" value="33" />
        </td>
    </tr>

</tbody>

I'd like to be able to sum each column and insert the value into a texbox. I have it working for totals on a row level using the following jQuery:

$('input.sum').change(function () {
            var sum = 0;
            $(this).closest('tr').find('input.sum').each(function (i) {
                var val = parseInt($(this).val(), 10);
                if (isNaN(val) || val === undefined) {
                    return;
                }
                sum += val;
            });
            $(this).closest('tr').find('input.total').val(sum);
        });

Each class for the column textbox totals has a name of totalCategory.

Any help with this on how to calculate the column totals would be greatly appreciated.

=========Edit============================

Ok. I'm working through this from the help below. I've changed my javascript to the following:

$('input.sum').change(function () {
            var sum = 0;
            $(this).closest('tr').find('input.sum').each(function (i) {
                var val = parseInt($(this).val(), 10);
                if (isNaN(val) || val === undefined) {
                    return;
                }
                sum += val;
            });
            $(this).closest('tr').find('input.total').val(sum);

            var tableID = $(this).closest('tr').parents('table').attr('id');
            var columnIndex = $(this).closest('td').attr("cellIndex");
            sumOfColumns(tableID, columnIndex, true);

        });

I've also added a new function:

 function sumOfColumns(tableID, columnIndex, hasHeader) {
        var tot = 0;
        $("#" + tableID + " tr" + (hasHeader ? ":gt(0)" : ""))
          .children("td:nth-child(" + columnIndex + ") input.sum")
          .each(function () {
              tot += parseInt($(this).val());
          });
          alert(tot);
    }

The problem now is that I don't get the correct value from the function sumOfColumns. Am I missing something with the code?

Thanks for the help thus far.

======Solution=========

Here is the solution I came up with:

 function sumOfColumns(tableID, columnIndex, hasHeader) {
        var tot = 0;
        $("#" + tableID + " tr" + (hasHeader ? ":gt(0)" : ""))
          .children("td:nth-child(" + (columnIndex + 1) + ")").find('input.sum')
          .each(function () {
              var val = parseInt($(this).val(), 10);
              if (isNaN(val) || val === undefined) {
                  val = 0;
                  $(this).val(0);
              }
              tot += parseInt(val);
          });

          $("#" + tableID + " tr" + (hasHeader ? ":gt(0)" : "")).children("td:nth-child(" + (columnIndex + 1) + ")").find('input.totalCategory').val(tot);
    }


    $(document).ready(function () {
        $('input.sum').change(function () {
            //Initialize variable to hold the totals
            var sum = 0;
            var grandTotal = 0;

            //Sum the row numbers
            $(this).closest('tr').find('input.sum').each(function (i) {
                var val = parseInt($(this).val(), 10);
                if (isNaN(val) || val === undefined) {
                    return;
                }
                sum += val;
            });

            //Assign the row total to the textbox
            $(this).closest('tr').find('input.total').val(sum);

            //Calculate column totals
            var tableID = $(this).closest('tr').parents('table').attr('id');
            var columnIndex = $(this).closest('td').attr("cellIndex");
            sumOfColumns(tableID, columnIndex, true);

            //Calculate grand totals for the last row
            $("#" + tableID + " tr:last").find('input.totalCategory').each(function (i) {
                var val = parseInt($(this).val(), 10);
                if (isNaN(val) || val === undefined) {
                    return;
                }
                grandTotal += val;
            });

            $("#" + tableID + " tr:last").find('input.grandTotal').val(grandTotal);



        });
    });

Thanks for all the help!


Here's my solution. It's not pretty but it works.

 function sumOfColumns(tableID, columnIndex, hasHeader) {
        var tot = 0;
        $("#" + tableID + " tr" + (hasHeader ? ":gt(0)" : ""))
          .children("td:nth-child(" + (columnIndex + 1) + ")").find('input.sum')
          .each(function () {
              var val = parseInt($(this).val(), 10);
              if (isNaN(val) || val === undefined) {
                  val = 0;
                  $(this).val(0);
              }
              tot += parseInt(val);
          });

          $("#" + tableID + " tr" + (hasHeader ? ":gt(0)" : "")).children("td:nth-child(" + (columnIndex + 1) + ")").find('input.totalCategory').val(tot);
    }


    $(document).ready(function () {
        $('input.sum').change(function () {
            //Initialize variable to hold the totals
            var sum = 0;
            var grandTotal = 0;

            //Sum the row numbers
            $(this).closest('tr').find('input.sum').each(function (i) {
                var val = parseInt($(this).val(), 10);
                if (isNaN(val) || val === undefined) {
                    return;
                }
                sum += val;
            });

            //Assign the row total to the textbox
            $(this).closest('tr').find('input.total').val(sum);

            //Calculate column totals
            var tableID = $(this).closest('tr').parents('table').attr('id');
            var columnIndex = $(this).closest('td').attr("cellIndex");
            sumOfColumns(tableID, columnIndex, true);

            //Calculate grand totals for the last row
            $("#" + tableID + " tr:last").find('input.totalCategory').each(function (i) {
                var val = parseInt($(this).val(), 10);
                if (isNaN(val) || val === undefined) {
                    return;
                }
                grandTotal += val;
            });

            $("#" + tableID + " tr:last").find('input.grandTotal').val(grandTotal);



        });
    });


 $('input.sum').change(function () {
        var sum = 0;

        var tr = $(this).closest('tr');
        $('td', tr).not('.totalCategory').each(function(){
        var temp = $(this).find('input.sum').val();
            var val = parseInt(temp, 10);
            if (isNaN(val) || val === undefined) {
                return;
            }
            sum += val;
       });
       $(this).closest('tr').find('input.total').val(sum);
    });


You can try something like this (based on your exact HTML with an extra row added for the totals):

$('tr:last').find('input').each(function() { // get "total row" inputs 
    var pos = $(this).closest('td').prevAll().length; // get current column
    var tot = 0;
    var that = this; // reference to last row total field
    var temp = $(this).closest('tr').prevAll() // In all rows above,
      .find('td:eq(' + pos + ')') // find the same-indexed cell
      .find('input').each(function() { // and the input within
        tot += +$(this).val(); // add to total, ensuring number with unary operator
        $(that).val(tot); // final answer
    });
});

You could have these totals update dynamically by placing something like the above into a function, and attaching that function to the change event of the fields you'd be editing.

Here's a working example: http://jsfiddle.net/redler/J2e7v/1/


Another option would be to traverse the table and build the column collections first, then just reference those collections on edit (this is just meant to be a rough idea):

var temp = $('.stripeMe').find('td:first-child').next(); // get the first column
var tempInputs = temp.find('input.sum'); // get the inputs in the first column

while (temp.length > 0 && tempInputs.length > 0) {
    // save the column inputs as "data" attached to each input.
    tempInputs.each(function () { $(this).data("column", tempInputs); });

    temp = temp.next(); // move to next column
    tempInputs = temp.find('input.sum'); // get the inputs for the next column
}

$('input.sum').change(function () {
    var sum = 0;

    // now just add up the values in the column inputs.
    $(this).data("column").each(function () {
        sum += parseInt($(this).val());
    });
    alert(sum);
});
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜