Export value with Linebreaks into single cell in Excel. jQuery Datatables

Question

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 enter image description here

have opened a bounty for this. Curious myself :) – davidkonrad — davidkonrad
– davidkonrad, Commented May 1, 2015 at 4: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
– Dave Alperovich, Commented May 2, 2015 at 5:22

Axel Richter · Accepted Answer · 2015-05-06 04:45:06Z

3

+100

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.

edited May 6, 2015 at 4:45

answered May 3, 2015 at 7:33

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

davidkonrad Over a year ago

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.

Axel Richter Over a year ago

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.

potatopeelings · Accepted Answer · 2015-05-01 03:24:49Z

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)

edited May 1, 2015 at 3:24

answered May 1, 2015 at 1:21

potatopeelings

41.2k8 gold badges97 silver badges121 bronze badges

4 Comments

S McCrohan Over a year ago

Could use some explanation.

davidkonrad Over a year ago

This is specifically about jQuery dataTables and export to Excel

potatopeelings Over a year ago

Yep, the one at datatables.net/extensions/tabletools right? That's what I checked on, albeit with string data.

davidkonrad Over a year ago

hey @psycho, does this answer enable you to maintain the linebreaks inside cells?

Collectives™ on Stack Overflow

Export value with Linebreaks into single cell in Excel. jQuery Datatables

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related