MadCat
Reputation: 113
datatables export to excel <select option>
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
Upvotes: 5
Views: 13038
Answers (2)
Zero
Reputation: 1152
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>Upvotes: 7
Akash Sathvara
Reputation: 81
Another Solution From my side. I solved this by creating a duplicate column (but with text instead of dropdown buttons) on the end of my table and hiding it using display none.
The hidden column gets updated with the selected dropdown data in the other column using jQuery. Then I hid the dropdown column on export using
exportOptions: { columns: columns: [ 0, 1, 3, 4, 5, 6] }
(column 2 had the dropdowns)
<!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: [
{
extend: 'excel',
text: 'Excel',
exportOptions: {
columns: [ 0, 1, 3, 4, 5, 6]
}
}
],
select: true
} );
} );
</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 style="display: none;">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 style="display: none;">Edinburgh</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 style="display: none;">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><select class="form-control">
<option value="Edinburgh">Edinburgh</option>
<option value="Singapore">Singapore</option>
<option value="Tokyo" selected>Tokyo</option>
</select>
</td>
<td style="display: none;">Tokyo</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 style="display: none;">Singapore</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 style="display: none;">Edinburgh</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 style="display: none;">Singapore</td>
<td>27</td>
<td>2011/01/25</td>
<td>$112,000</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>Upvotes: 5
Related Questions
- How can I set the default value for an HTML <select> element?
- jQuery Get Selected Option From Dropdown
- How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
- How do I make a placeholder for a 'select' box?
- How can I select an element with multiple classes in jQuery?
- How do I style a <select> dropdown with only CSS?
- Using Node.js require vs. ES6 import/export
- How can I select an element by name with jQuery?
- How do you remove all the options of a select box and then add one option and select it with jQuery?
- Get selected text from a drop-down list (select box) using jQuery