# How To Get Sub Total And Grand Total

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>
<script type="text/javascript" src="jquery-3.2.1.min.js"></script>
<script type="text/javascript" src="dt.js"></script>
<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>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>

<script type="text/javascript">
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]
}
}
}
}
],
[2000, 1500, 1000, 500, -1],
[2000, 1500, 1000, 500, 'All'],
],
initComplete: function () {
this.api().columns([2]).every(function () {
//replace spaces with dashes
title = \$(title).html().replace(/[\W]/g, '-');
var column = this;
var select = \$('<select id="' + title + '" class="select2" ></select>')
.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 {
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;
}
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>

<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;">
<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>
<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);
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
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
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

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

1. 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">
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]
}
}
}
}
],
[2000, 1500, 1000, 500, -1],
[2000, 1500, 1000, 500, 'All'],
],
initComplete: function () {
this.api().columns([2]).every(function () {
//replace spaces with dashes
title = \$(title).html().replace(/[\W]/g, '-');
var column = this;
var select = \$('<select id="' + title + '" class="select2" ></select>')
.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>
``````

Best regards,
Lan Huang