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".
My code:
<!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>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
You need define exportOptions:
Here is the code for you
var buttonCommon = { exportOptions: { format: { body: function(data, column, row, node) {if (column == 2) { return $(data).find("option:selected").text() } else return data } } } };
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, row, column, 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>