ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,394 questions
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 36%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELH%3C/text%3E%3C/svg%3E)
Hi @RAVI,
This is an ASP.NET forum. You need jQuery DataTable support.
https://datatables.net/forums/discussions
You have been using the built-in export to excel functionality of datatables.js. I don't know why you would change it. I would still recommend you to use the built-in export to excel functionality of datatables.js.
<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.3.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.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.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 thArray = [];
$('#list > thead > tr > th').each(function () {
thArray.push($(this).text())
})
var table = $('#list').DataTable({
dom: 'Bfrtip',
"buttons": [{
extend: 'excel',
footer: true,
title: "Test",
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: [
[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(2, { 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(2).footer()).html('Total: ' + 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');
}
});
});
});
</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 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 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>Name</th>
<th>Position</th>
<th>Office</th>
</tr>
</thead>
<tbody>
<tr>
<td>APPLE APPLE APPLE APPLE</td>
<td>A</td>
<td>10</td>
</tr>
<tr>
<td>APPLE APPLE APPLE APPLE</td>
<td>AB</td>
<td>5</td>
</tr>
<tr>
<td>APPLE APPLE APPLE APPLE</td>
<td>A</td>
<td>3</td>
</tr>
<tr>
<td>APPLE APPLE APPLE APPLE</td>
<td>AC</td>
<td>2</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>7</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>BG</td>
<td>10</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>AC</td>
<td>3</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>DC</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>WE</td>
<td>15</td>
</tr>
<tr>
<td>BANANA</td>
<td>DS</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>AB</td>
<td>35</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>A</td>
<td>10</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AB</td>
<td>5</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>A</td>
<td>3</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>2</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>7</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>BG</td>
<td>10</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>AC</td>
<td>3</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>DC</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>WE</td>
<td>15</td>
</tr>
<tr>
<td>BANANA</td>
<td>DS</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>AB</td>
<td>35</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>A</td>
<td>10</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AB</td>
<td>5</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>A</td>
<td>3</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>2</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>7</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>BG</td>
<td>10</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>AC</td>
<td>3</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>DC</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>WE</td>
<td>15</td>
</tr>
<tr>
<td>BANANA</td>
<td>DS</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>AB</td>
<td>35</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>A</td>
<td>10</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AB</td>
<td>5</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>A</td>
<td>3</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>2</td>
</tr>
<tr>
<td>APPLE APPLE</td>
<td>AC</td>
<td>7</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>BG</td>
<td>10</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>AC</td>
<td>3</td>
</tr>
<tr>
<td>MANGO MANGO</td>
<td>DC</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>WE</td>
<td>15</td>
</tr>
<tr>
<td>BANANA</td>
<td>DS</td>
<td>5</td>
</tr>
<tr>
<td>BANANA</td>
<td>AB</td>
<td>35</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>
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