可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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

回答1:

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.

回答2:

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)

Export value with Linebreaks into single cell in E

问题:

回答1:

回答2:

收藏的人(0)

Export value with Linebreaks into single cell in E

问题:

回答1:

回答2:

收藏的人(0)

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮