Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

datatables export to excel <select option>

Tags:

javascript

html

excel

datatable

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

like image 441
MadCat Avatar asked Jan 05 '23 21:01

MadCat

2 Answers

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>
like image 132
Zero Avatar answered Jan 15 '23 15:01

Zero

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>
like image 38
Akash Sathvara Avatar answered Jan 15 '23 16:01

Akash Sathvara
Sign in to Comment

Related questions

Adding html tag inside javascript string
Dependency injection in Angular Components like directives
Using a function as dictionary
jQuery detect if browser support Zoom
Jquery only click once
jquery slanted/curly quotation marks confusion
Generated button fires form submit
const vs var and the window.name property [duplicate]
using javascript how to check string contains hindi font?
authentication with Firebase 3.0 in node.js
Array intersection (set-theoretic) with Array.prototype.reduce
How to use lodash intersectionWith to compare and find same attribute with same value among two arrays with difference structure?
Error when precompiling all handlebars templates files in one js file
Remove an ::after CSS style using jQuery [duplicate]
Loop over object's key/value using TypeScript / Angular2 [duplicate]
JSON.stringify() Unable to save Object ID Fabric JS
Regular expression to restrict 'n' digits before and after decimal point
how to show a confirmation dialog box in toastr
Array.forEach() and Array.slice() together doesn't work correctly [duplicate]
Date field inside Kendo Grid is null on controller

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!