小贝子编程

数据表导出到 Excel <选择选项>



在我的测试页面中，我插入了一个选项列表，我需要导出到excel，但现在我的excel结果包括所有"选择选项"的列表。

我的代码：

<!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>

除了包含选项列表中所有值的" Office"列（" C"）（爱丁堡，新加坡，东京）中的" Office"列（" C"）之外，Excel中的结果还可以。导出Excel结果

您需要定义exportOptions：这是您的代码

var buttonCommon = { exportOptions: { format: { body: function(data, column, row, node) {if (column == 2) { return $(data).find("option:selected").text() } else return data } } } };

然后，当定义按钮时，请使用此按钮： 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>

我侧面的另一个解决方案。我通过在表的末端创建一个重复的列（但使用文本而不是下拉按钮）解决了这一点，然后使用bishing nons掩盖它。

使用jQuery在另一列中使用选定的下拉列数据进行更新。然后，我使用

在导出时隐藏下拉列

导出：{列：列：[0，1，3，4，5，6]}

（第2列的下拉列表）

<!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>

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium