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

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

sam371986sam371986 Posts: 4Questions: 1Answers: 0

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?

Answers

  • kthorngrenkthorngren Posts: 1,668Questions: 18Answers: 352

    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

  • sam371986sam371986 Posts: 4Questions: 1Answers: 0

    @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'));
        }
    
  • allanallan Posts: 43,557Questions: 1Answers: 5,703 Site admin

    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

  • kthorngrenkthorngren Posts: 1,668Questions: 18Answers: 352

    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

  • sam371986sam371986 Posts: 4Questions: 1Answers: 0

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

  • sam371986sam371986 Posts: 4Questions: 1Answers: 0

    @allan Thanks.

Sign In or Register to comment.