Can we create lengthy column in separate raw when export to excel?

sam371986

I have 9 columns in my table list with 16000 raws. I am using server side datatable feature and I have implemented excel export feature of datatable also. My problem is from 9 columns 1 column is a comment (<th>comment</th>). May be it contains lengthy comments, so Can I add comment section in separate raw?

kthorngren

First with server side processing the export will only contain the data that is displayed. Please see the last FAQ in the service side processing section:
https://datatables.net/faqs/index#Server-side-processing

Modifying the Excel export is tricky. I'm not sure if or how you can move the comments section to a row in the export. One option for you may be to set the style of that column to wrap text. To wrap the text you can use style 55 from the style options built into the excelHtml5 button type. This example shows how to apply styles).

Someone else may have ideas of how to move the comment to the next row.

Kevin

sam371986

@kthorngren Thanks. I had tried several method but to move the column as next row but not working. So I had tried to wrap the text. Wrapping the text is working but I am not sure am I followed correct method. Please check my code and correct me Thanks.

function fetch_data(is_date_search, daterange)
    {
        booking_data = $('#booking_data_mschool').DataTable({
                "lengthMenu": [10, 50, 100, 250, 500],
                "order": [[ 1, "desc" ]],
                "processing": true,
                "serverSide": true,
                "ajax": {
                    "url": '/cabinowner/mschool/bookings',
                "dataType": "json",
                "type": "POST",
                "data":{ is_date_search:is_date_search, daterange:daterange}
            },
            "columns": [
        { "data": "hash" },
        { "data": "invoice_number" },
        { "data": "ind_notice" },
        { "data": "usrLastname" },
        { "data": "usrFirstname" },
        { "data": "usrEmail" },
        { "data": "check_in" },
        { "data": "reserve_to" },
        { "data": "beds" },
        { "data": "dormitory" },
        { "data": "sleeps" },
        { "data": "status" },
        { "data": "answered" }
    ],
        "columnDefs": [
        {
            "orderable": false,
            "targets": [0, 2, 3, 4, 5, 8, 9, 10, 11, 12]
        }
    ],
        "language": {
        "sEmptyTable":      "Keine Daten in der Tabelle vorhanden",
            "sInfo":            "_START_ bis _END_ von _TOTAL_ Einträgen",
            "sInfoEmpty":       "0 bis 0 von 0 Einträgen",
            "sInfoFiltered":    "(gefiltert von _MAX_ Einträgen)",
            "sInfoPostFix":     "",
            "sInfoThousands":   ".",
            "sLengthMenu":      "_MENU_ Einträge anzeigen",
            "sLoadingRecords":  "Wird geladen...",
            "sProcessing":      "Bitte warten...",
            "sSearch":          "Suchen",
            "sZeroRecords":     "Keine Einträge vorhanden.",
            "oPaginate": {
            "sFirst":       "Erste",
                "sPrevious":    "Zurück",
                "sNext":        "Nächste",
                "sLast":        "Letzte"
        },
        "oAria": {
            "sSortAscending":  ": aktivieren, um Spalte aufsteigend zu sortieren",
                "sSortDescending": ": aktivieren, um Spalte absteigend zu sortieren"
        }
    }
    });

        /* Visiblity disabled of "comment" column */
        booking_data.column(2).visible( false );

        /* Bottom buttons for datatables */
        var buttons = new $.fn.dataTable.Buttons(booking_data, {
            buttons: [
                {
                    extend: 'csv',
                    exportOptions: {
                        columns: [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]
                    }
                },
                {
                    extend: 'excel',
                    exportOptions: {
                        columns: [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]
                    },
                    customize: function( xlsx ) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('row c[r^="B"]', sheet).attr( 's', '55' );
                    }
                },
                {
                    extend: 'print',
                    exportOptions: {
                        columns: [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]
                    }
                },
            ]
        }).container().appendTo($('#buttons'));
    }

allan

so Can I add comment section in separate raw?

Not really. You perhaps could if you can workout how to add rows and manipulate the XML that Buttons outputs for Excel, but it isn't trivial as Kevin notes. There is no built in way to do what you are looking for I'm afraid - you'd need to write the code for it.

Allan

kthorngren

Wrapping the text is working but I am not sure am I followed correct method. Please check my code and correct me

The code looks ok to me. Is there something not working?

Kevin

sam371986

@kthorngren, Code is working. I just post the code because I am not sure am I followed correct method. Thanks.

sam371986

@allan Thanks.