如何在我的数据表中添加列SUM功能?
问题描述：
我创建了一个数据表，该数据表的页脚具有多重搜索功能，我希望有一个功能可以将当前显示或搜索的所有薪水加起来.我不确定该怎么做.
I have created a datatable which has a multi search feature in the footer and I would like to have a feature which adds up all the salaries of what is currently displayed or searched. I am not sure how to do that.
这是我的表格的HTML代码
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="./search.js"></script>
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>Tiger Nixon</td>
<td>System Architect</td>
<td>Edinburgh</td>
<td>61</td>
<td>2011/04/25</td>
<td>$320,800</td>
</tr>
<tr>
<td>Garrett Winters</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>63</td>
<td>2011/07/25</td>
<td>$170,750</td>
</tr>
<tr>
<td>Ashton Cox</td>
<td>Junior Technical Author</td>
<td>San Francisco</td>
<td>66</td>
<td>2009/01/12</td>
<td>$86,000</td>
</tr>
<tr>
<td>Cedric Kelly</td>
<td>Senior Javascript Developer</td>
<td>Edinburgh</td>
<td>22</td>
<td>2012/03/29</td>
<td>$433,060</td>
</tr>
<tr>
<td>Airi Satou</td>
<td>Accountant</td>
<td>Tokyo</td>
<td>33</td>
<td>2008/11/28</td>
<td>$162,700</td>
</tr>
<tr>
<td>Brielle Williamson</td>
<td>Integration Specialist</td>
<td>New York</td>
<td>61</td>
<td>2012/12/02</td>
<td>$372,000</td>
</tr>
<tr>
<td>Herrod Chandler</td>
<td>Sales Assistant</td>
<td>San Francisco</td>
<td>59</td>
<td>2012/08/06</td>
<td>$137,500</td>
</tr>
<tr>
<td>Rhona Davidson</td>
<td>Integration Specialist</td>
<td>Tokyo</td>
<td>55</td>
<td>2010/10/14</td>
<td>$327,900</td>
</tr>
</tbody>
<tfoot>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
</table>
这是我的search.js代码
$(document).ready(function() {
// Setup - add a text input to each footer cell
$('#example tfoot th').each( function () {
var title = $(this).text();
$(this).html( '<input type="text" placeholder="Search '+title+'" />' );
} );
// DataTable
var table = $('#example').DataTable();
// Apply the search
table.columns().every( function () {
var that = this;
$( 'input', this.footer() ).on( 'keyup change', function () {
if ( that.search() !== this.value ) {
that
.search( this.value )
.draw();
}
} );
} );
} );
我正在尝试在此代码中添加求和功能，以便将薪水列的总和加起来，有人可以帮我吗?我不确定该怎么做
I am trying to add a sum feature to this code where it adds up the total of the salary column, can someone please help me? im not sure how to do this
答
That topic has been raised multiple times over here at SO, you may inquiry my earlier post in that regard.
这里的技巧是将选择器修饰符{search:'applied'}与方法 .column() (如果您需要汇总单个列)或 .rows() 列总数.
The trick here is to use selector-modifier {search:'applied'} together with methods .column() (if you need to summarize single column) or .rows() for multi-column totals.
另一个有用的方法.data()可用于将数据提取到数组中( 1 ， 2 ).
Another helpful method .data() may be used to extract the data into array (1, 2).
为了在每次重新绘制表格时刷新总数，您可以使用 drawCallback 选项，以指定用于重新计算可见行总数并将结果放入所需节点的回调函数.
In order to refresh your totals upon each table re-draw, you may employ drawCallback option, to specify callback function that re-calculates totals for visible rows and puts the result into desired node.
查看以下在线演示:
//format number as currency (not essential within current context)
const num2curr = num => '$'+num.toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
//datatables initialization
const dataTable = $('#example').DataTable({
dom: 't',
//append individual filter inputs
initComplete: function(){
this.api().columns().every(function(){
$(this.footer()).html(`<input colindex="${this.index()}" placeholder="${$(this.header()).text()}"></input>`)
})
},
//calculate total salary and put that into span#totalsalary
drawCallback: function(){
const totalSalary = this
.api()
.column(5,{search:'applied'})
.data()
.toArray()
//remove '$',',', keep decimal separator '.', summarize
.reduce((total,salary) => total+=Number(salary.replace(/[^0-9\.]/g,'')),0);
//insert result into the <span> text
$('#totalsalary').text(`Total salary for filtered rows is: ${num2curr(totalSalary)}`);
}
});
//individual filtering
$('#example').on('keyup', 'tfoot input', function(){
dataTable.column($(this).attr('colindex')).search($(this).val()).draw()
});
<!doctype html><html><head><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/rg-1.1.0/datatables.min.css" /><script type="application/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script><script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/rg-1.1.0/datatables.min.js"></script><script src="test.js"></script></head><body><table id="example" class="display" style="width:100%"><thead><tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Start date</th><th>Salary</th></tr></thead><tbody><tr><td>Tiger Nixon</td><td>System Architect</td><td>Edinburgh</td><td>61</td><td>2011/04/25</td><td>$320,800</td></tr><tr><td>Garrett Winters</td><td>Accountant</td><td>Tokyo</td><td>63</td><td>2011/07/25</td><td>$170,750</td></tr><tr><td>Ashton Cox</td><td>Junior Technical Author</td><td>San Francisco</td><td>66</td><td>2009/01/12</td><td>$86,000</td></tr><tr><td>Cedric Kelly</td><td>Senior Javascript Developer</td><td>Edinburgh</td><td>22</td><td>2012/03/29</td><td>$433,060</td></tr><tr><td>Airi Satou</td><td>Accountant</td><td>Tokyo</td><td>33</td><td>2008/11/28</td><td>$162,700</td></tr><tr><td>Brielle Williamson</td><td>Integration Specialist</td><td>New York</td><td>61</td><td>2012/12/02</td><td>$372,000</td></tr><tr><td>Herrod Chandler</td><td>Sales Assistant</td><td>San Francisco</td><td>59</td><td>2012/08/06</td><td>$137,500</td></tr><tr><td>Rhona Davidson</td><td>Integration Specialist</td><td>Tokyo</td><td>55</td><td>2010/10/14</td><td>$327,900</td></tr></tbody><tfoot><tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Start date</th><th>Salary</th></tr></tfoot></table><span id="totalsalary"></span></body></html>