How To Get Sub Total And Grand Total

RAVI 896 Reputation points
2024-03-20T10:27:27.1766667+00:00

This Is My Aspx Page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default3" %>
<html>
<head runat="server">
<script type="text/javascript" src="jquery-3.2.1.min.js"></script> 
 <script type="text/javascript" src="dt.js"></script>
<link rel="stylesheet" type="text/css" href=dt.css />
<link rel="stylesheet" type="text/css" href=select.css />
     <script type="text/javascript" src="select.js"></script>
     
      <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.flash.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.print.min.js"></script>
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>


   
     <script type="text/javascript">
        $(document).ready(function () {
            var thArray = [];
            $('#list > thead > tr > th').each(function () {
                thArray.push($(this).text())
            })
            var rowCount = $('table#list tbody tr').length;
            sessionStorage.setItem("rowCount", rowCount);
            // Remove the formatting to get integer data for summation
            var intVal = function (i) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '') * 1 :
                    typeof i === 'number' ?
                        i : 0;
            };
            $('#list').DataTable({
                "ordering": false,
                dom: 'Bfrtip',
                "buttons": [{
                    extend: 'excel',
                    footer: true,
                   // title: "Test",
                    exportOptions: {
                        columns: [0, 1, 2, 3],
                        format: {
                            header: function (data, index, column) {
                                return thArray[index]
                            }
                        }
                    }
                }
                ],
                lengthMenu: [
                    [2000, 1500, 1000, 500, -1],
                    [2000, 1500, 1000, 500, 'All'],
                ],
                initComplete: function () {
                    this.api().columns([2]).every(function () {
                        var title = this.header();
                        var colTitle = this.header().innerHTML;
                        //replace spaces with dashes
                        title = $(title).html().replace(/[\W]/g, '-');
                        var column = this;
                        var select = $('<select id="' + title + '" class="select2" ></select>')
                            .appendTo($(column.header()).empty())
                            .on('change', function () {
                                //Get the "text" property from each selected data 
                                //regex escape the value and store in array
                                var data = $.map($(this).select2('data'), function (value, key) {
                                    return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
                                });
                                //if no data selected use ""
                                if (data.length === 0) {
                                    data = [""];
                                }
                                //join array into string with regex or (|)
                                var val = data.join('|');
                                //search for the option(s) selected
                                column
                                    .search(val ? val : '', true, false)
                                    .draw();
                            });
                        column.data().unique().sort().each(function (d, j) {
                            select.append('<option value="' + d + '">' + d + '</option>');
                        });
                        //use column title as selector and placeholder
                        $('#' + title).select2({
                            multiple: true,
                            closeOnSelect: false,
                            width: '100%',
                            placeholder: "" + colTitle
                        });
                        //initially clear select otherwise first option is selected
                        $('.select2').val(null).trigger('change');
                    });
                },
                footerCallback: function (tfoot, data, start, end, display) {
                    let api = this.api();
                    api.column(0).footer().innerHTML = "GRAND TOTAL";
                    // Total over all pages
                    total = api
                        .column(2, { search: 'applied' })
                        .data()
                        .reduce((a, b) => intVal(a) + intVal(b), 0);
                    // Update footer
                    api.column(2).footer().innerHTML = total;
                    
                    
                    
                    total1 = api
                        .column(3, { search: 'applied' })
                        .data()
                        .reduce((a, b) => intVal(a) + intVal(b), 0);
                    // Update footer
                    api.column(3).footer().innerHTML = total1;
                }
            });
        });
    </script>
    <style>
        .select2-results__options[aria-multiselectable="true"] li {
            padding-left: 30px;
            position: relative;
        }
            .select2-results__options[aria-multiselectable="true"] li:before {
                position: absolute;
                left: 8px;
                opacity: .6;
                top: 6px;
                font-family: "FontAwesome";
                content: "\f0c8";
            }
            .select2-results__options[aria-multiselectable="true"] li[aria-selected="true"]:before {
                content: "\f14a";
            }
        table {
            margin: 0 auto;
            margin-top: 20px;
            width: 100%;
            position: relative;
            overflow: auto;
        }
        th, thead {
            position: sticky;
            top: 0;
            border: 1px solid #dddddd;
            background-color: #1f2d54;
            text-align: center;
            table-layout: fixed;
            word-break: break-word;
            height: 45px;
        }
    </style>
         
         
          <script src="table2excel.js"></script>

<script type="text/javascript">
    function Export() {
        $("[id*=list]").table2excel({
            filename: "LEATHER_STOCK.xls"               
        });
      
    }
</script> 
      
</head>
<body>
    <form id="form1" runat="server">
    <div>
        &nbsp;<div class="container">
      
          <br />
        <table cellspacing="0" class="myClass" id="list" style="width: 400px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black;">
            <thead>
                <tr>
                    <th style="width:150px;">FIELD 1 </th>
                    <th style="width:150px;">FIELD 2 </th>
                    <th style="width:150px;">FIELD 3 </th>
                    <th style="width:150px;">FIELD 4 </th>
                  
                    
                </tr>
            </thead>
            <tbody style="border-collapse: collapse; border: 1px solid black;">
                <asp:Repeater ID="Repeater1" runat="server">
                    <ItemTemplate>
                        <tr>
                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("FIELD1")%></td>
                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("FIELD2")%></td>
                          
                            <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("FIELD3")%></td>
                              <td style="border-collapse: collapse; border: 1px solid black; font-size: larger;"><%#Eval("FIELD4")%></td>
                                
                        
                        
                        
                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </tbody>
            <tfoot>
                <tr>
                     
                         <td style="border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;"></td>
                    
                    <td style="border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;"></td>
                    <td style="border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;"></td>
                 <td style="border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;"></td>
                
               </tr>
            </tfoot>
        </table>
    </div>
    </div>
    </form>
</body>
</html>

This Is My Aspx C# Code

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Drawing;
using System.IO;
using System.Net;
using System.Net.Mail;
using System.Net.Configuration;
public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {

            

                DataTable dt = new DataTable();
                SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEMIMSConnectionString"].ConnectionString);
                con1.Open();
                SqlCommand cmd1 = new SqlCommand("select * from Table1", con1);
                SqlDataAdapter ada1 = new SqlDataAdapter(cmd1);
                ada1.Fill(dt);
                con1.Close();
                con1.Dispose();
                Repeater1.DataSource = dt;
                Repeater1.DataBind();
                

           


        }

    }
    
    protected void Button3_Click1(object sender, EventArgs e)
    {
        Response.Redirect(Request.RawUrl);
    }
 
}

This Is SQL Code

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Field1] [varchar](150) NULL,
	[Field2] [varchar](50) NULL,
	[Field3] [float] NULL,
	[Field4] [varchar](50) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Table1] ON
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (1, N'AAPLE', N'CA', 5, N'1')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (2, N'AAPLE', N'KL', 10, N'2')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (3, N'AAPLE', N'MN', 2, N'5')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (4, N'MANGO', N'JH', 10, N'4')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (5, N'MANGO', N'WE', 20, N'6')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (6, N'MANGO', N'RE', 5, N'2')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (7, N'BANANA', N'DS', 10, N'2')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (8, N'BANANA', N'SD', 10, N'1')
INSERT [dbo].[Table1] ([ID], [Field1], [Field2], [Field3], [Field4]) VALUES (9, N'BANANA', N'WS', 20, N'4')
SET IDENTITY_INSERT [dbo].[Table1] OFF

I need output like this total and Grand total using my above code with row color

abc

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,240 questions
{count} votes

Accepted answer
  1. Lan Huang-MSFT 25,386 Reputation points Microsoft Vendor
    2024-03-22T06:58:34.9866667+00:00

    Hi @RAVI,

    After repeated testing, I have tested the code that meets your needs. You can refer to it.

     <script type="text/javascript">
         $(document).ready(function () {
             var thArray = [];
             $('#list > thead > tr > th').each(function () {
                 thArray.push($(this).text())
             })
             var rowCount = $('table#list tbody tr').length;
             sessionStorage.setItem("rowCount", rowCount);
             // Remove the formatting to get integer data for summation
             var intVal = function (i) {
                 return typeof i === 'string' ?
                     i.replace(/[\$,]/g, '') * 1 :
                     typeof i === 'number' ?
                         i : 0;
             };
             $('#list').DataTable({
                 "ordering": false,
                 dom: 'Bfrtip',
                 "buttons": [{
                     extend: 'excel',
                     footer: true,
                     // title: "Test",
                     exportOptions: {
                         columns: [0, 1, 2, 3],
                         format: {
                             header: function (data, index, column) {
                                 return thArray[index]
                             }
                         }
                     }
                 }
                 ],
                 lengthMenu: [
                     [2000, 1500, 1000, 500, -1],
                     [2000, 1500, 1000, 500, 'All'],
                 ],
                 initComplete: function () {
                     this.api().columns([2]).every(function () {
                         var title = this.header();
                         var colTitle = this.header().innerHTML;
                         //replace spaces with dashes
                         title = $(title).html().replace(/[\W]/g, '-');
                         var column = this;
                         var select = $('<select id="' + title + '" class="select2" ></select>')
                             .appendTo($(column.header()).empty())
                             .on('change', function () {
                                 //Get the "text" property from each selected data 
                                 //regex escape the value and store in array
                                 var data = $.map($(this).select2('data'), function (value, key) {
                                     return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
                                 });
                                 //if no data selected use ""
                                 if (data.length === 0) {
                                     data = [""];
                                 }
                                 //join array into string with regex or (|)
                                 var val = data.join('|');
                                 //search for the option(s) selected
                                 column
                                     .search(val ? val : '', true, false)
                                     .draw();
                             });
                         column.data().unique().sort().each(function (d, j) {
                             select.append('<option value="' + d + '">' + d + '</option>');
                         });
                         //use column title as selector and placeholder
                         $('#' + title).select2({
                             multiple: true,
                             closeOnSelect: false,
                             width: '100%',
                             placeholder: "" + colTitle
                         });
                         //initially clear select otherwise first option is selected
                         $('.select2').val(null).trigger('change');
                     });
                 },
                 footerCallback: function (tfoot, data, start, end, display) {
                     let api = this.api();
                     api.column(0).footer().innerHTML = "GRAND TOTAL";
                     // Total over all pages
                     total = api
                         .column(2, { search: 'applied' })
                         .data()
                         .reduce((a, b) => intVal(a) + intVal(b), 0);
                     // Update footer
                     api.column(2).footer().innerHTML = total;
                     total1 = api
                         .column(3, { search: 'applied' })
                         .data()
                         .reduce((a, b) => intVal(a) + intVal(b), 0);
                     // Update footer
                     api.column(3).footer().innerHTML = total1;
                 },
                 "drawCallback": function (settings) {
                     var api = this.api();
                     var rows = api.rows({ page: 'current' }).nodes();
                   
                     var colonne = api.row(0).data().length;
                     var totale = new Array();
                     totale['Totale'] = new Array();
                     var groupid = -1;
                     var subtotale = new Array();
                     var b = api.column(0).data().unique().reverse();
                     var last = b[0];
                     api.column(0, { page: 'current' }).data().each(function (group, i) {                       
                         if (last !== group) {                                            
                             groupid++;                        
                             $(rows).eq(i-1).after(
                                 '<tr class="group"  style="font-weight: bold;background:forestgreen; text-align: left;border: 1px solid black"><td colspan="2">' + last + '</td></tr>'
                             );                           
                             last = group;                                                                             
                         }
                         val = api.row(api.row($(rows).eq(i)).index()).data();      //current order index
                         $.each(val, function (index2, val2) {
                             if (typeof subtotale[groupid] == 'undefined') {
                                 subtotale[groupid] = new Array();
                             }
                             if (typeof subtotale[groupid][index2] == 'undefined') {
                                 subtotale[groupid][index2] = 0;
                             }
                             if (typeof totale['Totale'][index2] == 'undefined') { totale['Totale'][index2] = 0; }
                             valore = Number(val2);
                             subtotale[groupid][index2] += valore;
                             totale['Totale'][index2] += valore;
                         });
                     });
                     $('tbody').find('.group').each(function (i, v) {
                         $(this).find('td:first').append($('<span />', { 'class': 'rowCount-grid' }));
                         var subtd = '';
                         for (var a = 2; a < colonne; a++) {
                             subtd += '<td>' + subtotale[i][a] + '</td>';
                         }
                         $(this).append(subtd);
                     });
                 }
             });
         });
     </script>
    

    User's image

    User's image

    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


0 additional answers

Sort by: Most helpful