Export value with Linebreaks into single cell in Excel

simian1986

I am successfully exporting HTML tables from a web application to excel using jQuery DataTables. However one particular column has values containg line breaks and tabs. I have managed to display the data correctly on the HTML table by replacing new lines (\n) and tabs (\t) with <br> and  (x5) respectively.

The issue is when exporting to excel i need to have the line breaks back in but keep all the value in one cell.

here is my jquery code:

$('#papercliptable').dataTable({
"sDom": 'T<"clear">lfrtip',
"tableTools": {
    "aButtons": [{
        "sExtends": "xls",
        "sButtonText": "Excel",
        "fnCellRender": function (sValue, iColumn, nTr, iDataIndex) {
            console.log("sValue = " + sValue);
            console.log("iColumn = " + iColumn);
            return sValue.replace(/<br\s*\/?>/ig, "\r\n");
        },
        "sNewLine": "\r\n"
    }, {
        "sExtends": "print",
        "sMessage": "Metrics"
    }]
 }
});

Credit: post

It does not seem to work for me. All value goes to single cell but not with new line characters.

Any help would be greatly appreciated. Thanks