Export value with Linebreaks into single cell in Excel with jQuery Datatables 2016

Question

I'm trying to retain linebreaks made with <br> in an HTML table when exporting it to excel with DataTables.
I followed their guide to replace certain things with Regex here: DataTables find and replace during export.

I was able to replace things no problem. But I fail to replace the <br> with newlines that make content in the same cell retain their linebreaks.

This is my JS:

$( document ).ready(function() {

var fixNewLine = {
        exportOptions: {
            format: {
                body: function ( data, column, row ) {
                    // Strip $ from salary column to make it numeric
                    return column === 5 ?
// THIS WORKS:          data.replace(/test/ig, "blablabla"):
                        data.replace( /<br\s*\/?>/ig, '"'+"\r\n"+'"' ) :
                        data;
                }
            }
        }
    };


    $('#table2excel').DataTable({
        dom: 'Bfrtip',
        buttons:[
            $.extend( true, {}, fixNewLine, {
                extend: 'copyHtml5'
            } ),
            $.extend( true, {}, fixNewLine, {
                extend: 'excelHtml5'
            } ),
            $.extend( true, {}, fixNewLine, {
                extend: 'pdfHtml5'
            } )
        ]

    });
});

The problem lies in this line:

data.replace( /<br\s*\/?>/ig, '"'+"\r\n"+'"' ) :

It gets saved in excel with only a pair of " " instead of the actual line break. Note that this also doesn't work:

data.replace( /<br\s*\/?>/ig, "\r\n"):

Any advice?

There is a similar thread here: Export value with Linebreaks into single cell in Excel. jQuery Datatables But it's outdated as it's a year old and there have been updates to DataTables and "TableTools" has been replaced by "Buttons".

mesqueeb · Accepted Answer · 2016-03-03T06:29:06.347

13

The correct answer is:

data.replace( /<br\s*\/?>/ig, "\n" ) :

However, you need to press the "wrap text" button when opening the excel. If someone knows a way to have it wrapped automatically, please let me know.

edited Mar 03 '16 at 06:29

answered Mar 03 '16 at 06:21

mesqueeb

5,277
5
44
77

score 1 · Answer 2 · answered Mar 03 '16 at 02:08

1

The replace function is built in to javascript. Maybe you'd like to remove the quotations?

data.replace( /<br\s*\/?>/ig, "\r\n")

Works for me in a javascript interpreter.

Its possible that the caller of your formatting function removes newlines and replaces them with spaces

answered Mar 03 '16 at 02:08

Chris

846
6
16

Thanks. I tried, but still no linebreak. What do you mean with the "caller of my formatting"? – mesqueeb Mar 03 '16 at 04:12
Apparently it works with only "\n". However, I need to press the "show linebreaks" button inside excel! I still need to find a way to fix this. – mesqueeb Mar 03 '16 at 06:20
this seemed to be the correct answer - only with the little change of yours that replaced the "\r\n" by "\n". Why didn't you asked @Chris to correct this and marked it as correct? – leole May 23 '17 at 15:10

score 1 · Answer 3 · answered Aug 28 '20 at 04:12

This works for me with auto wrap text in Windows Excel 2016

data.replace( /<br\s*\/?>/ig, "\r")  // \r, not \n

And customize buttons

$('#myTable').DataTable( {
    buttons: [
        {
            extend: 'excelHtml5',
            customize: function( xlsx ) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                // set cell style: Wrapped text
                $('row c', sheet).attr( 's', '55' );
            }
        }
    ]
});

More info about button customization: https://datatables.net/reference/button/excelHtml5

Export value with Linebreaks into single cell in Excel with jQuery Datatables 2016

3 Answers3