Line break in Excel export

Line break in Excel export

tomboschtombosch Posts: 2Questions: 1Answers: 0
edited July 2016 in Free community support

Hello,

I am searching for a solution to create line breaks in an Excel export. Can someone complete my code to replace the '<br>' in my HTML to a line break in Excel? After hours of searching and trying, my hair is start to fall of ;-)

(This code adds '&lt;br&gt;' in Excel instead the line break.)

Thank u very much in advance!

My code:

$( document ).ready(function() {

var fixNewLine = {
        exportOptions: {
            format: {
                body: function ( data, column, row ) {
                    return column === 5 ?
                        data.replace( /<br\s*\/?>/gi, '"'+"\r\n"+'"' ) :
                        data;
                }
            }
        }
    };


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

    });
});

Answers

  • cicjrcicjr Posts: 7Questions: 2Answers: 0

    me too!

  • F12MagicF12Magic Posts: 109Questions: 0Answers: 28

    If I remember it correctly, excel only needs a newline ( \n ). At least when used with Windows. For a Mac it's \r\n . So try and change the replacement code with this:

    return column === 5 ?
        data.replace( /<br\s*\/?>/gmi, '\n' ) :
        data;
    
  • obworleyobworley Posts: 7Questions: 2Answers: 0

    Did you ever get an answer to this? I've been Googling for 5 hours and just can't get an answer. Either it strips all the HTML or if I replace br with \n, I get all of the code with the < replaced with < and still don't get the new line. I really love this tool, and with TableTools, it used to work, but it just quit exporting to Excel and I'm having to go back to the drawing board. And stripNewlines: false doesn't seem to do anything at all.

This discussion has been closed.