CODEWITHSUNDEEP
javascripthtmlajaxdatatablesexport-to-excel
Elif Ece Sanli
Elif Ece Sanli

Reputation: 103

Datatables excel export stripHtml doesn't work

I have added a datatables export button to my datatable. In this table, some columns include html tags like

<button data-toggle="dropdown" class="btn btn-primary dropdown-toggle btn-xs">BUTTON NAME</button>

When I first added the export button, it worked fine and the rows with HTML tags exported without their tags like: BUTTON NAME. But then I had to include a replace function to my button code as following:

buttons: [
  {
    extend: "excelHtml5",

    exportOptions: {
      format: {
        body: function (data, row, column, node) {
          return column === 18 ? data.replace(",", ".") : data;
        },
      },
    },

    tag: "img",

    className: "btn-excel",

    title: "file_title",

    titleAttr: "Excel",

    attr: {
      src: "../images/menu_img/excel.ico",
    },

    action: newexportaction,
  },
];

When I add this exportOptions attribute, the rows with HTML tags started to appear with their tags in the exported excel files. The column I am trying to replace doesn't have any html tag, they are all in some other column but this change affects them somehow.

I tried to add 'stripHtml: false' but it didn't make a difference. I also tried to write the replace function with switch case statements but it didn't work as well.

Upvotes: 2

Views: 1195

Answers (1)

andrewJames
andrewJames

Reputation: 21910

When you use the format function for your Excel export button, DataTables populates its data parameter with the raw data from each DataTable cell. So, that raw data is the full HTML content for your BUTTON NAME button.

More precisely, you get each cell's innerHtml. See also here.

That is why some other options such as stripHtml: true no longer have any effect.

You can perform the equivalent stripHtml step (in your specific case) by using the node parameter instead of the data parameter - and accessing the node's innerText using JavaScript:

body: function (data, row, column, node) {
  if (column === 5) {
    return data.replace(",", ".");
  } else if ( column === 3) {
    return node.innerText;
  } else {
    return data;
  }
}

My output in Excel is:

enter image description here

Here is my full test example, so you can see the source data:

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <link href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" rel="stylesheet"/>
  <link href="https://cdn.datatables.net/buttons/2.3.6/css/buttons.dataTables.css" rel="stylesheet"/>
 
  <script src="https://code.jquery.com/jquery-3.6.0.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>
  <script src="https://cdn.datatables.net/buttons/2.3.6/js/dataTables.buttons.js"></script>
  <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.html5.js"></script>

  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office in Country</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td><button data-toggle="dropdown" class="btn btn-primary dropdown-toggle btn-xs">BUTTON NAME</button></td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td><button data-toggle="dropdown" class="btn btn-primary dropdown-toggle btn-xs">BUTTON NAME</button></td>
                <td>2011/07/25</td>
                <td>$170,750</td>
            </tr>
        </tbody>
    </table>

</div>

<script>

$(document).ready(function() {

  $('#example').DataTable( {
    dom: 'Bfrtip',
    buttons: [
      {
        extend: 'excelHtml5',
        exportOptions: {
          stripHtml: true,
          format: {
            body: function (data, row, column, node) {
              if (column === 5) {
                return data.replace(",", ".");
              } else if ( column === 3) {
                return node.innerText;
              } else {
                return data;
              }
            }
          }
        }
      }
    ]

  } );

} );

</script>

</body>
</html>

Upvotes: 1

Related Questions