5

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

Improve this question
3

3 Answers 3

Reset to default
7

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>

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hello, I have a doubt, I tried Excel and it's working but it displaying the only those column record which is showing on screen.i have more the 10 column how can I export that?
it doesn't work. After changing city it doesn't change in pdf.
5

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>

Improve this answer

1 Comment

Hi, but if you change the option value the excel export is not refreshed, in this case you need to add a function to update the value of 3th column on change of 2nd.
0

my working example:

    buttons: [
    {
        extend: 'excel',
        exportOptions: {
            format: {
                body: function (data, row, column, node) {
                    // Se la cella contiene una select, prendi solo il valore selezionato
                    var $select = $(node).find('select');
                    if ($select.length) {
                        return $select.val();
                    }
                    return data;
                }
            }
        }
    },
    // ...altri buttons...
]
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.