科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>javascript - datatables export to excel <select option> - Stack Overflow

    javascript - datatables export to excel <select option> - Stack Overflow

    programmeradmin2浏览0评论

    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=".12.3.js"></script>
    <script src=".10.12/js/jquery.dataTables.min.js"></script>
    <script src=".2.1/js/dataTables.buttons.min.js"></script>
    <script src=".5.0/jszip.min.js"></script>
    <script src=".1.18/build/pdfmake.min.js"></script>
    <script src=".1.18/build/vfs_fonts.js"></script>
    <script src=".2.1/js/buttons.html5.min.js"></script>
    
    <link href=".10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
    <link href=".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>

    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./jquery-1.12.3.js"></script>
    <script src="https://cdn.datatables/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables/buttons/1.2.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare./ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    <script src="https://cdn.rawgit./bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
    <script src="https://cdn.rawgit./bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
    <script src="https://cdn.datatables/buttons/1.2.1/js/buttons.html5.min.js"></script>
    
    <link href="https://cdn.datatables/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
    <link href="https://cdn.datatables/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

    • javascript
    • excel
    • html
    • datatable
    Share Improve this question asked Jun 14, 2016 at 16:25 MadCatMadCat 1131 gold badge1 silver badge13 bronze badges 3
    • All of your export formats have the same issue. Seems like this is more a feature of the libraries you're using. – Tim Williams Commented Jun 14, 2016 at 16:51
    • Yes is a datatale library: datatables/extensions/buttons/examples/html5/simple.html – MadCat Commented Jun 14, 2016 at 22:24
    • see this -> stackoverflow./questions/34329997/… – davidkonrad Commented Jul 28, 2016 at 7:46
    Add a ment  | 

    2 Answers 2

    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./jquery-1.12.3.js"></script>
    <script src="https://cdn.datatables/1.10.12/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables/buttons/1.2.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare./ajax/libs/jszip/2.5.0/jszip.min.js"></script>
    <script src="https://cdn.rawgit./bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
    <script src="https://cdn.rawgit./bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
    <script src="https://cdn.datatables/buttons/1.2.1/js/buttons.html5.min.js"></script>
    
    <link href="https://cdn.datatables/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
    <link href="https://cdn.datatables/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>

    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./jquery-1.12.3.js"></script>
<script src="https://cdn.datatables/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables/buttons/1.2.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare./ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="https://cdn.rawgit./bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script src="https://cdn.rawgit./bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script src="https://cdn.datatables/buttons/1.2.1/js/buttons.html5.min.js"></script>

<link href="https://cdn.datatables/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
<link href="https://cdn.datatables/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>

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论