8

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 &nbsp;(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 enter image description here

Community
  • 1
  • 1
psycho
  • 1,539
  • 4
  • 20
  • 36
  • Have deleted my answer, sry it didnt worked out. – davidkonrad Apr 15 '15 at 20:38
  • have opened a bounty for this. Curious myself :) – davidkonrad – davidkonrad May 01 '15 at 04:08
  • There seems to be some confusion. seems from the code that soft breaks are being created with "\r\n" which is actually ***eol***. "\n" is actually a line break in excel format. But there are no tabs in Excel. Tab is an event. Replace tabs with " ". – Dave Alperovich May 02 '15 at 05:22

2 Answers2

3

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:

enter image description here

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.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Gratz. Have seen your profile. Your ekspertise in Excel and the given example convince me that this is the generally correct answer that will help people in the future. – davidkonrad May 05 '15 at 18:02
  • 1
    Sorry, but I believe there is no "generally correct answer" in this case. Since the tableTools produces CSV rather than XLS(X), their results will ever be dependent of locale and system and kind of import in Excel. – Axel Richter May 06 '15 at 04:50
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)

potatopeelings
  • 40,709
  • 7
  • 95
  • 119