File Export

File Export

arosnerarosner Posts: 35Questions: 8Answers: 1
edited April 13 in Free community support

I am developing an ASP.NET MVC application and am attempting to use the File Export functionality described in: https://datatables.net/extensions/buttons/examples/initialisation/export.html.

There are 4 issues:
1) I want to only see the csv, button, but I see all buttons available from the FIle Export link above despite setting:

                    "layout": {
                        "topStart": {
                            "buttons": ["csv"]
                        }
                    },

2) The csv export always has a blank row below the column names and another row with the column names at the end of the file. Perhaps this is related to the fact that I have the search boxes on top and a table footer?
3) Hidden fields are included in the CSV
4) The "link" field is included in the CSV

Thank you

Page source is:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title></title>
    <link href="/Content/bootstrap.css" rel="stylesheet"/>
    <link href="/Content/site.css" rel="stylesheet"/>
    <script src="/Scripts/modernizr-2.8.3.js"></script>    
    <link href="https://cdn.datatables.net/2.0.3/css/dataTables.dataTables.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/fixedheader/3.4.0/css/fixedHeader.dataTables.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/buttons/3.0.1/css/buttons.dataTables.css" rel="stylesheet" type="text/css" />
    <style type="text/css">
        thead input {
            width: 100%;
            padding: 3px;
            box-sizing: border-box;
        }
    </style>
</head>
<body>
    <table>
        <tr>
            <td style="vertical-align:top">
                <div class="container body-content">
<h2>Search</h2>
<br />
<nav>
    <div class="nav nav-tabs" id="nav-tab" role="tablist">
            <a class="nav-link active" id="nav-RecordsList-tab" data-bs-toggle="tab" href="#nav-RecordsList" role="tab" aria-controls="nav-RecordsList" aria-selected="true">List</a>
    </div>
</nav>
<div class="tab-content" id="nav-tabContent">
        <div class="tab-pane fade show active" id="nav-RecordsList" role="tabpanel" aria-labelledby="nav-RecordsList-tab">
<table id="tableData" class="display compact" style="width:100%">
    <thead>
        <tr>
            <th>PROJECT</th>
            <th>AGREED</th>
            <th>LAST UPDATED</th>
            <th>ACTION</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>PROJECT</th>
            <th>AGREED</th>
            <th>LAST UPDATED</th>
            <th>ACTION</th>
        </tr>
    </tfoot>
</table>

        </div>
</div>

                    <hr />
                </div>
            </td>
        </tr>
    </table>

    <script src="/Scripts/jquery-3.4.1.js"></script>
    <script src="/Scripts/bootstrap.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/2.9.2/umd/popper.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/fixedheader/3.4.0/js/dataTables.fixedHeader.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/2.0.3/js/dataTables.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/3.0.1/js/dataTables.buttons.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/3.0.1/js/buttons.dataTables.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/3.0.1/js/buttons.html5.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/buttons/3.0.1/js/buttons.print.min.js"></script>
    <script>
        $(document).ready(function () {
                bindDatatable();
        });

        function bindDatatable() {
            $('#tableData thead tr').clone(true).addClass('filters').appendTo('#tableData thead');
            datatable = $('#tableData')
                .DataTable({
                    "orderCellsTop": true,
                    "fixedHeader": true,
                    "dom": "<'row'<'col-sm-12 col-md-6'l><'col-sm-12 col-md-6'B>>" +
                        "<'row'<'col-sm-12'tr>>" +
                        "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
                    "processing": true, // for show progress bar
                    "serverSide": false, // for process server side
                    "filter": true, // this is to enable filter (search box)
                    "pageLength": 25,
                    "layout": {
                        "topStart": {
                            "buttons": ["csv"]
                        }
                    },
                    "ajax": {
                        "url": "#",
                        "type": "POST",
                        "datatype": "json"
                    },
                    "language": {
                        "emptyTable": "No record found.",
                        "processing":
                            '<i class="fa fa-spinner fa-spin fa-3x fa-fw" style="color:#2a2b2b;"></i><span class="sr-only">Loading...</span> '
                    },
                    initComplete: function () {
                        var api = this.api();
                        // For each column
                        api.columns().eq(0).each(function (colIdx) {
                            // Set the header cell to contain the input element
                            var cell = $('.filters th').eq($(api.column(colIdx).header()).index());
                            var title = $(cell).text();
                            $(cell).html('<input type="text" placeholder="' + title + '" />');
                            // On every keypress in this input
                            $('input', $('.filters th').eq($(api.column(colIdx).header()).index()))
                                .off('keyup change')
                                .on('keyup change', function (e) {
                                    e.stopPropagation();
                                    // Get the search value
                                    $(this).attr('title', $(this).val());
                                    var regexr = '({search})'; //$(this).parents('th').find('select').val();
                                    var cursorPosition = this.selectionStart;
                                    // Search the column for that value
                                    api
                                        .column(colIdx)
                                        .search((this.value != "") ? regexr.replace('{search}', '(((' + this.value + ')))') : "", this.value != "", this.value == "")
                                        .draw();
                                    $(this).focus()[0].setSelectionRange(cursorPosition, cursorPosition);
                                });
                        });
                    },
                    "columns": [
                        {
                            "data": "PROJECT",
                            "autoWidth": true,
                            "searchable": false,
                            "visible": false
                        },
                        {
                            "data": "AGREEDSTATUS",
                            "autoWidth": true,
                            "searchable": true,
                        },
                        {
                            "data": "LASTUPDATE",
                            "autoWidth": true,
                            "searchable": true,
                        },
                        {
                            "data": null,
                            "searchable": false,
                            render: function (data, type, row) {
                                return '<a href="/Home/Edit?project=' + row.PROJECT + '" target="_blank">Edit</a>';
                            }
                        },                    ]
                });
        }
    </script>
</body>
</html >

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

Answers

  • kthorngrenkthorngren Posts: 21,322Questions: 26Answers: 4,949

    I want to only see the csv, button, but I see all buttons available from the FIle Export link above despite setting:

    One problem is you are loading a mix of Datatables 1.13.7 and 2.0.3 CSS and JS files. Remove the 1.13.7.

    You have both dom and layout configured. The layout option is the new way to layout the Datatables elements and dom is the legacy way. I'm not sure what happens when both are configured but I suggest removing dom.

    Possibly wither of these is causing all the buttons to show.

    The csv export always has a blank row below the column names and another row with the column names at the end of the file. Perhaps this is related to the fact that I have the search boxes on top and a table footer?

    See this thread for an example of removing header rows you don't want exported.

    Hidden fields are included in the CSV

    The "link" field is included in the CSV

    See this example for selecting the columns to export.

    Kevin

  • arosnerarosner Posts: 35Questions: 8Answers: 1
    edited April 14

    Thank you for the response, Kevin.

    1) I removed the dom option per your request.

    2) I also removed the css and js references to version 1.3.7.

    3) Per the supplied example, I modified the following DataTables Javascript:

                    "customizeData": function (data) {
                        data.headerStructure.pop();
                    },
                    "layout": {
                        "topStart": {
                            "buttons": [
                                {
                                    "extend": "csvHtml5",
                                    "exportOptions": {
                                        "columns": [3, 4, 5, 6, 7, 8]
                                    }
                                },
                                'colvis'
                            ]
                        }
                    },
    

    The prior issue that still exists is:

    The csv export always has a blank row below the column names (second row) and another row with the column names at the end of the file.

    A new issue is that I now have separate search box above and to the right of the Dataable now.

    Thank you again.

  • kthorngrenkthorngren Posts: 21,322Questions: 26Answers: 4,949

    The csv export always has a blank row below the column names (second row) and another row with the column names at the end of the file.

    Did you try the suggestion in the thread I provided to remove the second header row?

    A new issue is that I now have separate search box above and to the right of the Dataable

    The layout docs explain how to change the default setting to remove elements. You will need to add topEnd: null to remove the search input.

    Kevin

  • arosnerarosner Posts: 35Questions: 8Answers: 1

    Hi Kevin,

    The topEnd: null successfully removed the Search box. Thank you!

    I read the thread you sent regarding "The csv export always has a blank row below the column names (second row) and another row with the column names at the end of the file."

    However, the issue remains.

    Perhaps the customizeData property is in the wrong place? I now have the Layout secion as follows:

                    "layout": {
                        "topEnd": null,
                        "topStart": {
                            "buttons": [
                                {
                                    "extend": "csvHtml5",
                                    "exportOptions": {
                                        "columns": [3, 4, 5, 6, 7, 8]
                                    },
                                    "customizeData": function (data) {
                                                        data.headerStructure.pop();
                                                    },
                                },
                                'colvis'
                            ]
                        }
                    },
    

    Thank you,

    Al

  • kthorngrenkthorngren Posts: 21,322Questions: 26Answers: 4,949
    edited April 14

    As Allan mentioned in the thread it goes into the exportOptions option. For example:
    https://live.datatables.net/voxayava/1/edit

    Kevin

  • arosnerarosner Posts: 35Questions: 8Answers: 1

    The blank row beneath the first row of column names is gone now. The only issue is the column names in the last row (probably the footer).

                    "layout": {
                        "topEnd": null, //remove generic search box
                        "topStart": {
                            "buttons": [
                                {
                                    "extend": "csvHtml5",
                                    "exportOptions": {
                                        "columns": [3, 4, 5, 6, 7, 8],
                                        "customizeData": function (data) {
                                                data.headerStructure.pop();
                                            },
                                    },
                                },
                                'colvis'
                            ]
                        }
                    },
    

    Thank you,

    Al

  • arosnerarosner Posts: 35Questions: 8Answers: 1

    Kevin,

    Never mind. I found https://datatables.net/extensions/buttons/examples/html5/footer.html. The issue can be closed.

    Thank you again.

    Al

  • kthorngrenkthorngren Posts: 21,322Questions: 26Answers: 4,949

    If you don't want the footer then see this example for how to remove it.

    Kevin

Sign In or Register to comment.