数据表导出到excel<选择选项> [英] datatables export to excel <select option>
问题描述
在测试页中,我插入了一个选项列表,我只需要将所选值导出为excel,但是现在我的excel结果包括选择选项的所有列表。
In my test page I have insert a option list and I need to export to excel only the selected value, but now my excel result include all list of "Select option".
我的代码:
<!DOCTYPE html>
<html>
<head>
<title>Test Export Excel "SELECT OPTION" </title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<script src="https://code.jquery.com/jquery-1.12.3.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.1/js/buttons.html5.min.js"></script>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
<link href="https://cdn.datatables.net/buttons/1.2.1/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css">
<script>
$(document).ready(function() {
$('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf'
]
} );
} );
</script>
<div>
<table id="example" class="display" cellspacing="0" border="1" 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><select class="form-control">
<option value="Edinburgh" selected>Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>61</td>
<td>2011/04/25</td>
<td>$320,800</td>
</tr>
<tr>
<td>Garrett Winters</td>
<td>Accountant</td>
<td><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" selected>Tokyo </option>
</select>
</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><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" selected>Tokyo </option>
</select>
</td>
<td>66</td>
<td>2009/01/12</td>
<td>$86,000</td>
</tr>
<tr>
<td>Shad Decker</td>
<td>Regional Director</td>
<td><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" selected>Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>51</td>
<td>2008/11/13</td>
<td>$183,000</td>
</tr>
<tr>
<td>Michael Bruce</td>
<td>Javascript Developer</td>
<td><select class="form-control">
<option value="Edinburgh" selected>Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>29</td>
<td>2011/06/27</td>
<td>$183,000</td>
</tr>
<tr>
<td>Donna Snider</td>
<td>Customer Support</td>
<td><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" selected>Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>27</td>
<td>2011/01/25</td>
<td>$112,000</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
结果导出在excel中可以,但办公室列( C)包含选项列表(爱丁堡,新加坡,东京)中的所有值。
导出excel结果
the result exporting in excel is ok except for the "office" column ("C") containing all the values in option list (Edinburgh, Singapore, Tokyo). Export excel result
推荐答案
您需要定义 exportOptions
:
这是您的代码
You need define exportOptions
:
Here is the code for you
var buttonCommon = {exportOptions:{格式:{正文:function(data,column,row,node){if(column == 2){return $ {data).find ( option:selected)。text()} else返回数据}}}};
然后在定义按钮时,使用此一个:
按钮:['copy',$ .extend(true,{},buttonCommon,{extend: csv}),$。extend(true,{},buttonCommon, {扩展名: excel}),$。extend(true,{},buttonCommon,{扩展名: pdf})]
Then when define the buttons, use this one:
buttons: [ 'copy', $.extend(true, {}, buttonCommon, { extend: "csv" }),$.extend(true, {}, buttonCommon, { extend: "excel" }), $.extend(true, {}, buttonCommon, { extend: "pdf" })]
<!DOCTYPE html>
<html>
<head>
<title>Test Export Excel "SELECT OPTION" </title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<script src="https://code.jquery.com/jquery-1.12.3.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.1/js/buttons.html5.min.js"></script>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
<link href="https://cdn.datatables.net/buttons/1.2.1/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css">
<script>
var buttonCommon = {
exportOptions: {
format: {
body: function (data, column, row, node) {
// if it is select
if (column == 2) {
return $(data).find("option:selected").text()
} else return data
}
}
}
};
$(document).ready(function() {
$('#example').DataTable( {
dom: 'Bfrtip',
buttons: [
'copy', $.extend(true, {}, buttonCommon, {
extend: "csv"
}), $.extend(true, {}, buttonCommon, {
extend: "excel"
}), $.extend(true, {}, buttonCommon, {
extend: "pdf"
})
]
} );
} );
</script>
<div>
<table id="example" class="display" cellspacing="0" border="1" 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><select class="form-control">
<option value="Edinburgh" selected>Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>61</td>
<td>2011/04/25</td>
<td>$320,800</td>
</tr>
<tr>
<td>Garrett Winters</td>
<td>Accountant</td>
<td><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" selected>Tokyo </option>
</select>
</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><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" selected>Tokyo </option>
</select>
</td>
<td>66</td>
<td>2009/01/12</td>
<td>$86,000</td>
</tr>
<tr>
<td>Shad Decker</td>
<td>Regional Director</td>
<td><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" selected>Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>51</td>
<td>2008/11/13</td>
<td>$183,000</td>
</tr>
<tr>
<td>Michael Bruce</td>
<td>Javascript Developer</td>
<td><select class="form-control">
<option value="Edinburgh" selected>Edinburgh</option>
<option value="Singapore" >Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>29</td>
<td>2011/06/27</td>
<td>$183,000</td>
</tr>
<tr>
<td>Donna Snider</td>
<td>Customer Support</td>
<td><select class="form-control">
<option value="Edinburgh" >Edinburgh</option>
<option value="Singapore" selected>Singapore</option>
<option value="Tokyo" >Tokyo </option>
</select>
</td>
<td>27</td>
<td>2011/01/25</td>
<td>$112,000</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
这篇关于数据表导出到excel<选择选项>的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!