Menu
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
Tried using:
return sValue.replace(/<br\s*\/?>/ig, "\x0B");
produces the following
405
Let's have a complete example.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/tabletools/2.2.4/css/dataTables.tableTools.css">
<script type="text/javascript" language="javascript" src="https://code.jquery.com/jquery-latest.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/tabletools/2.2.4/js/dataTables.tableTools.min.js"></script>
<script type="text/javascript" language="javascript" class="init">
$(document).ready(function() {
$('#papercliptable').DataTable( {
dom: 'T<"clear">lfrtip',
tableTools: {
"sSwfPath": "/copy_csv_xls_pdf.swf",
"aButtons": [{
"sExtends": "xls",
"sFileName": "test.csv",
"fnCellRender": function (sValue, iColumn, nTr, iDataIndex) {
console.log("sValue = " + sValue);
console.log("iColumn = " + iColumn);
re = /<br\s*\/?>/i;
if (re.test(sValue)) {
return '"' + sValue.replace(/<br\s*\/?>/ig, "\n") + '"';
} else {
return sValue;
}
}
}]
}
});
});
</script>
</head>
<body>
<table id="papercliptable">
<thead>
<tr>
<th>A</th>
<th>B</th>
<th>C</th>
<th>D</th>
</tr>
</thead>
<tbody>
<tr>
<td>12345</td>
<td>Value1</td>
<td>Value2</td>
<td>This<br/>is<br/>a<br/>test.</td>
</tr>
</tbody>
</table>
</body>
</html>
Note, you have to have the copy_csv_xls_pdf.swf within your own domain. You can download it from: https://cdn.datatables.net/tabletools/2.2.4/
This works for me and produces:
CSV:
A B C D
12345 Value1 Value2 "This
is
a
test."
Note, the spaces between the columns are horizontal tabs "\t" 0x09.
Excel:
Note, this is the result in Excel if the *.csv is opened via File - Open. The Text Import Wizard can't handle line breaks within cells in correct manner.
52
If the content contains new line characters you need to delimit it within double quotes. So use
return '"' + sValue.replace(/<br\s*\/?>/ig, "\r\n") + '"';
Of course you need to do this only if the content contains \r\n (otherwise numbers get formatted as text)
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
