Share via

carry forward total of each column

RAVI 1,056 Reputation points
2024-07-01T13:28:54.6333333+00:00

Hello

This is my aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="A.aspx.cs" Inherits="Default3" %> 

<html>
<head runat="server">
<title>demo</title>
 
 <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.13.17/css/bootstrap-select.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.1/umd/popper.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.13.17/js/bootstrap-select.min.js"></script>

 

    
<script> 

//$(document).ready(function() {
//    var table = $('#list').DataTable({
//        lengthMenu: [
//            [2000, 1500, 1000, 500, -1],
//            [2000, 1500, 1000, 500, 'All'],
//        ],
//        ordering: false, // Disable sorting for the entire table
//        initComplete: function () {
//            var api = this.api();

//            // Iterate over each column
//            this.api().columns().every( function () {
//                var column = this;
//                var columnIndex = column.index();
//                var columnHeader = $(column.header());
//                
//                var title = this.header();
//     //replace spaces with dashes
//     title = $(title).html().replace(/[\W]/g, '-');

//                // Create a select element
//               var select = $('<select class="form-control show-tick" data-container="body" data-header="Select option(s)" data-actions-box="true" data-live-search="true" title="' + title + '" data-selected-text-format="count > 0" multiple><option value=""></option></select>')
//         .appendTo(columnHeader.empty())
//                    .on('change', function () {
//                        var selectedValues = $(this).val() || [];

//                        // Filter based on selected values
//                        if (selectedValues.length === 0) {
//                            column.search('', true, false).draw();
//                        } else {
//                            var searchValue = '^(' + selectedValues.join('|') + ')$';
//                            column.search(searchValue, true, false).draw();
//                        }
//                    });

//                // Populate the select element with unique values from the column
//                var uniqueValues = column.data().unique().sort();
//                uniqueValues.each(function (value, index) {
//                    select.append('<option value="'+value+'">'+value+'</option>');
//                });

//                // Initialize selectpicker for Bootstrap Select
//                select.selectpicker('refresh');
//            });
//        },
//        footerCallback: function (row, data, start, end, display) {
//            var api = this.api();
//            var columnData = api.column(5, { page: 'current'}).data(); // Assuming 'Office' column index is 2
//            var total = columnData.length ?
//                        columnData.reduce(function (a, b) {
//                            return parseFloat(a) + parseFloat(b); // Assuming numeric values for sum
//                        }) :
//                        0;
//    
//            $(api.column(5).footer()).html('' + total); // Inject the total into the footer cell of the 'Office' column
//        }
//    });

//    // Redraw the table and refresh selectpicker on draw
//    table.on('draw', function () {
//        table.columns().indexes().each(function ( idx ) {
//            var select = $(table.column(idx).header()).find('select');
//            if (select.val().length === 0) {
//                select.empty().append('<option value=""/>');
//                table.column(idx, { search: 'applied'}).data().unique().sort().each(function ( d, j ) {
//                    select.append('<option value="'+d+'">'+d+'</option>');
//                });
//                select.selectpicker('refresh');
//            }
//        });
//    });
//}); 


$(document).ready(function() {
var thArray = [];
            $('#list > thead > tr > th').each(function () {
                thArray.push($(this).text())
            })
            var table = $('#list').DataTable({
                dom: 'Bfrtip',
                "buttons": [{
                    extend: 'excel',
                    footer: true,
                    title: "CHEMICAL DATE WISE ARRIVAL/ISSUES DETAILS",
                    className:'btn-success',
                    exportOptions: {
                        // columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
                        format: {
                            header: function (data, index, column) {
                                return thArray[index]
                            }
                        }
                    },
                    customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        // Set the alignment for all cells in the first column (column A)
                        $('row c[r^="A"],c[r^="B"],c[r^="C"]', sheet).each(function () {
                            $(this).attr('s', '51'); 
                        });                   
                    }
                }
                ],
        lengthMenu: [
            [3, 1, -1],
            [3, 1, -1, 'All'],
        ],
        ordering: false, // Disable sorting for the entire table
        initComplete: function () {
            var api = this.api();

            // Iterate over each column
            this.api().columns().every(function () {
                var column = this;
                var columnIndex = column.index();
                var columnHeader = $(column.header());
                
                var title = $(column.header()).text().trim();
                // Replace spaces with dashes
                title = title.replace(/\W/g, '-');

                // Create a select element
                var select = $('<select class="form-control show-tick" data-container="body" data-header="Select option(s)" data-actions-box="true" data-live-search="true" title="' + title + '" data-selected-text-format="count > 0" multiple><option value=""></option></select>')
                    .appendTo(columnHeader.empty())
                    .on('change', function () {
                        var selectedValues = $(this).val() || [];

                        // Filter based on selected values
                        if (selectedValues.length === 0) {
                            column.search('', true, false).draw();
                        } else {
    // Escape special characters in selected values
    var escapedValues = selectedValues.map(function(value) {
        return value.replace(/[\-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/g, "\\$&");
    });

    var searchValue = '^(' + escapedValues.join('|') + ')$';
    column.search(searchValue, true, false).draw();
}
                    });

                // Populate the select element with unique values from the column
                var uniqueValues = column.data().unique().sort();
                $.each(uniqueValues, function (index, value) {
                    select.append('<option value="'+value+'">'+value+'</option>');
                });

                // Initialize selectpicker for Bootstrap Select
                select.selectpicker('refresh');
            });
        },
        footerCallback: function (row, data, start, end, display) {
            var api = this.api();

            // Calculate sum for the third column (index 2)
            var column3Data = api.column(2, { page: 'current'}).data().reduce(function (a, b) {
                return parseFloat(a) + parseFloat(b);
            }, 0);
    
            
            // Update the footer
            $(api.column(2).footer()).html(column3Data.toFixed(0)); // Assuming 2 decimal places
            
        }
    });

    // Redraw the table and refresh selectpicker on draw
    table.on('draw', function () {
        table.columns().indexes().each(function ( idx ) {
            var select = $(table.column(idx).header()).find('select');
            if (select.val().length === 0) {
                select.empty().append('<option value=""/>');
                table.column(idx, { search: 'applied'}).data().unique().sort().each(function ( d, j ) {
                    select.append('<option value="'+d+'">'+d+'</option>');
                });
                select.selectpicker('refresh');
            }
        });
    });
});
 
 
 
</script>

 
 
 <style> 
 th, thead {
     position: sticky;
     top: 0;
     border: 1px solid #dddddd;
     background-color: #ABEBC6;
     text-align: center;
     table-layout: fixed;
    
     height: 25px;
 }
    </style>
     
     <script>
    function exportToExcel() {
    const table = document.getElementById("list");
    const rows = table.getElementsByTagName("tr");
    let csvContent = "";

    for (let i = 0; i < rows.length; i++) {
        const row = rows[i];
        const cells = row.getElementsByTagName("td");
        for (let j = 0; j < cells.length; j++) {
            csvContent += cells[j].innerText + ",";
        }
        csvContent += "\n";
    }

    // Create a Blob with the CSV content
    const blob = new Blob([csvContent], { type: "text/csv;charset=utf-8;" });

    // Create a link element, hide it, direct it towards the Blob, and then 'click' it programatically
    const link = document.createElement("a");
    const url = URL.createObjectURL(blob);
    link.setAttribute("href", url);
    link.setAttribute("download", "table_data.csv");
    link.style.visibility = "hidden";
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}

</script>

<script>
function clearFilters() {
    var table = $('#list').DataTable();

    // Clear each column's filter
    table.columns().every(function() {
        var column = this;
        var select = $(column.header()).find('select');

        // Clear the selectpicker
        select.val([]).selectpicker('refresh');

        // Clear the column search
        column.search('').draw();
    });
}
</script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <button onclick="exportToExcel()">Export to Excel</button>
    <button type="button" onclick="clearFilters()">Clear Filters</button>
       <div id="c1" runat="server">
        <table cellspacing="0" class="myClass" id="list" style="width: 600px; font-family: Calibri; font-size: 11px; border-collapse: collapse; border: 1px solid black; z-index: 102;">
           <thead>
            <tr>
                <th>Field 1</th>
                <th>Field 2</th>
                <th>Field 3</th>
              
            </tr>
        </thead>
            <tbody>
                
                <tr>
                <td>APPLE (INDIA)</td>
                <td>A</td>                 
                <td>10</td>                
                 </tr>
                 
                 
                    <tr>
                <td>APPLE</td>
                <td>B</td>                 
                <td>5</td>                
                 </tr>
                 
                  <tr>
                <td>APPLE</td>
                <td>AB</td>                 
                <td>40</td>                
                 </tr>
                 
                 
                  <tr>
                <td>APPLE</td>
                <td>AC</td>                 
                <td>20</td>                
                 </tr>
                 
                 
                 
                  <tr>
                <td>APPLE</td>
                <td>BD</td>                 
                <td>10</td>                
                 </tr>
                 
                 
                    
            </tbody>
            <tfoot>
                <tr>
                                
             <td style="background: sandybrown; border: 1px solid black;"></td> 
                             
             <td style="background: sandybrown; border: 1px solid black;"></td>
                         
             <td style="text-align:right;border-collapse: collapse; background: sandybrown; border: 1px solid black; font-size: larger; font-weight: bold;font-size:20px;"></td>
                         
                      
                          
               </tr>
            </tfoot>
        </table>
    </div>
    </div>
    </form>
</body>
</html>

It work fine

First load it shows field3 total 55 perfect and on click second page its showing field3 as 30 but i want 55+30 = 85 so on page moves it should carry forward the total

Thanking you

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,502 questions
0 comments
Accepted answer
  1. Lan Huang-MSFT 29,666 Reputation points Microsoft Vendor
    2024-07-02T02:06:29.93+00:00

    Hi @RAVI,

    I think you could implement something like the following, which is a bit simpler.

    footerCallback: function (row, data, start, end, display) {
     var api = this.api();
     // Calculate sum for the third column (index 2)
     var column3Data = api.column(2, { page: 'current' }).data().reduce(function (a, b) {
         return parseFloat(a) + parseFloat(b);
     }, 0);
     total = api.column(2).data().reduce(function (a, b) { return parseFloat(a) + parseFloat(b); }, 0);
     // Total over this page
     // Update footer
     $(api.column(2).footer()).html(column3Data.toFixed(0) + '(' + total.toFixed(0) + ')');
     // Assuming 2 decimal places
 },

    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
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.