line break in excel datatable

Question

How can I define a line break in a datatable excel when it is exploited?

https://jsfiddle.net/pa9q14mb/6/

At the time of exporting the excel the box shows it

-TEXT TEXT TEXT -TEXT TEXT TEXT -TEXT TEXT TEXT -TEXT TEXT TEXT

But I would like it this way in the excel

   -TEXT TEXT TEXT 
   -TEXT TEXT TEXT 
   -TEXT TEXT TEXT 
   -TEXT TEXT TEXT

I would like to know if there is the possibility of doing that and I was looking at some examples with the exportOptions function, but without results. regards

Answer 1

/*
    JUST ADD THIS FOR CALL THE CUSTOM BUTTON INTO YOUR "$('#custom').DataTable"
    $('#custom').DataTable({
        dom: 'Blfrtip',
        buttons: [
            getCustomExcelButton([4])  //YOU CAN CHANGE THIS OR AD MORE COLUMNS AFFECTED LIKE: [4,7,9,11,...]
        ], 
        ...others params 
    });
*/

function getCustomExcelButton(columnsAfected = []){
    return {
        extend: 'excelHtml5',
        text: 'Excel',
        title: '',
        exportOptions: {
            stripHtml: false,
            columns: ':visible:not(:eq(5))', //THIS IS OPTIONAL
            format: {
                body: function ( data, row, column, node ) {
                    let countries = ['PANAMA', 'HONDURAS', 'COSTA RICA', 'NICARAGUA', 'GUATEMALA', 'EL SALVADOR'];
                    countries = countries.join("break_line");
                    /*This console.log(countries.replace( /break_line/g, '\n' )) have the output:
                        PANAMA
                        HONDURAS
                        COSTA RICA
                        NICARAGUA
                        GUATEMALA
                        EL SALVADOR
                    */
                    return (columnsAfected.includes(column)) ? countries.replace( /break_line/g, '\n' ) : data;
                }
            },
        },
        customize: ( xlsx ) => customBreakLinesCells(xlsx, columnsAfected)
    };
}

function customBreakLinesCells(xlsx, columnsAfected){
    let sheet = xlsx.xl.worksheets['sheet1.xml'];

    $('row', sheet).each(function(rowIndex) {
        let row = $(this);
        //CONDITION FOR OMIT THE HEADER ROW
        if(rowIndex > 0){
            $('c', row).each(function(colIndex) {
                let cell = $(this);
                let value = cell.text();
                //COLUMNS AFECTED
                if(columnsAfected.includes(colIndex)){
                    cell.attr('s', '55');
                    cell.attr('t', 'inlineStr');
                    cell.html('<is><t>' + value + '</t></is>');
                }
            });
        }
    });
}

Answer 2

Where you're already setting your export options, you need to do something like this:

return column === 5 ?
       data.replace(/<br>/g,String.fromCharCode(10)) :
       data;

<br> appears in your DataTables cell, so you need to convert that to a carriage return (ascii code 10), which is what Excel expects for a new line.