将Linebreaks导出到Excel中的单元格中。 jQuery数据库 [英] Export value with Linebreaks into single cell in Excel. jQuery Datatables
问题描述
我使用jQuery DataTables成功地从HTML应用程序导出HTML表格到excel。然而，一个特定的列具有包含换行符和制表符的值。我已经设法通过用< br> 和& nbsp替换新行（\\\
）和选项卡（\t）在HTML表上正确显示数据;（x5）。
问题是当导出到excel时，我需要将行删除，但将所有值保留在一个单元格中。
这是我的jquery代码：
$ '#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，\\ \\\\;
}，
sNewLine：\r\\\
}，{
sExtends：print，
sMessage：Metrics
}]
}
}）;
信用：
这适用于我，并生成：
CSV：
ABCD
12345 Value1 Value2此
为
a
测试。
请注意，列之间的空格是水平选项卡\t 0x09 。
Excel：
注意如果 *。csv 是通过文件打开打开的，则这是Excel中的结果。文本导入向导无法以正确的方式处理单元格内的换行符。
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
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.
这篇关于将Linebreaks导出到Excel中的单元格中。 jQuery数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
