jQuery DataTables format output for export, exclude buttons

0
votes

For the jquery data table, I have a table displayed in one of the columns in the Datatable and wanted to enable the user to toggle it on/off. When exporting to excel/pdf/copy it has all the data, but it also includes the button during export.

I'm want to format the data to exclude the toggle buttons, so it won't show when exporting to the PDF/Excel. I've looked at this link to exclude the '$' signs for Salary. Is there a way I can make the buttons disappear also?

<!DOCTYPE html>
<html>

<head>
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script>

    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.7.1/css/buttons.dataTables.min.css">



    <script>
        $(document).ready(function () {

            var buttonCommon = {
                exportOptions: {
                    format: {
                        body: function (data, row, column, node) {
                            // Strip $ from salary column to make it numeric
                            return column === 5 ?
                                data.replace(/[$,]/g, '') :
                                data;
                        }
                    }
                }
            };

            $('#togg-tb1').click(function () {
                if ($("#table1").css("display") == "none") {
                    $("#table1").css("display", "table-cell");
                } else {
                    $("#table1").css("display", "none");
                }
            });

            $('#togg-tb2').click(function () {
                if ($("#table2").css("display") == "none") {
                    $("#table2").css("display", "table-cell");
                } else {
                    $("#table2").css("display", "none");
                }
            });

            $('#togg-tb3').click(function () {
                if ($("#table3").css("display") == "none") {
                    $("#table3").css("display", "table-cell");
                } else {
                    $("#table3").css("display", "none");
                }
            });

            $('#example').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    'copy', 'excel', 'pdf'

                ]
            });
        });



    </script>
</head>

<body>
    <table id="example" class="display nowrap" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
                <th>Toggling</th>

            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
                <td>
                    <button type="button" id="togg-tb1">Toggle</button>

                    <table id="table1">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
                <td>
                    <button type="button" id="togg-tb2">Toggle</button>
                    <table id="table2">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>

            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>Junior Technical Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
                <td>
                    <button type="button" id="togg-tb3">Toggle</button>

                    <table id="table3">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>
            </tr>

        </tbody>
    </table>

</body>

</html>
3
javascripthtmljquerydatatablesexport

3 Answers

0
votes

You can use the format.body option of the DataTables buttons.exportData() function. This gives you access to the node of each cell in the specific column you want to change:

exportOptions: {
  format: {
    body: function ( innerHtml, rowIdx, colIdx, node ) {
      if (colIdx === 6) {
        return node.textContent.replace('Toggle', '').replace(/  +/g, ' ');
      } else {
        return innerHtml;
      }
    }
  }
}

The key section is this part:

node.textContent.replace('Toggle', '').replace(/  +/g, ' ')

This takes each <td> node in the relevant column, and extracts the text content from that node (i.e. it strips out all the HTML tags).

Then it removes the text Toggle (which was displayed in the toggle button).

Then it replaces multiple consecutive white spaces with a single white space. This last step may not be exactly what you want, so you can change it to format the data in whatever way you need, before it is sent to Excel.

Here is the above code in its wider context:

<!DOCTYPE html>
<html>

<head>

    <meta charset="UTF-8">

    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script>

    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.7.1/css/buttons.dataTables.min.css">



</head>

<body>
    <table id="example" class="display nowrap" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
                <th>Toggling</th>

            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
                <td>
                    <button type="button" id="togg-tb1">Toggle</button>

                    <table id="table1">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
                <td>
                    <button type="button" id="togg-tb2">Toggle</button>
                    <table id="table2">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>

            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>Junior Technical Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
                <td>
                    <button type="button" id="togg-tb3">Toggle</button>

                    <table id="table3">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>
            </tr>

        </tbody>
    </table>

    <script>
        $(document).ready(function () {

            $('#togg-tb1').click(function () {
                if ($("#table1").css("display") == "none") {
                    $("#table1").css("display", "table-cell");
                } else {
                    $("#table1").css("display", "none");
                }
            });

            $('#togg-tb2').click(function () {
                if ($("#table2").css("display") == "none") {
                    $("#table2").css("display", "table-cell");
                } else {
                    $("#table2").css("display", "none");
                }
            });

            $('#togg-tb3').click(function () {
                if ($("#table3").css("display") == "none") {
                    $("#table3").css("display", "table-cell");
                } else {
                    $("#table3").css("display", "none");
                }
            });

            $('#example').DataTable({
                dom: 'Bfrtip',
                buttons: [
                  {
                    extend: 'excelHtml5',
                    title: '', // no title row in excel sheet
                    text: 'Excel', // label for the export button
                    exportOptions: {
                      format: {
                        body: function ( innerHtml, rowIdx, colIdx, node ) {
                          if (colIdx === 6) {
                            return node.textContent.replace('Toggle', '').replace(/  +/g, ' ');
                          } else {
                            return innerHtml;
                          }
                        }
                      }
                    }
                  }
                ]
            });
        });

    </script>

</body>

</html>
0
votes

You can specify the columns and format property for every button to acheive this & further customization. The column property can have the index of the columns to be part of the output.

<!DOCTYPE html>
<html>

<head>
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script>

    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.7.1/css/buttons.dataTables.min.css">



    <script>
    const format= {
                body: function ( data, row, column, node ) {
                    // Strip $ from salary column to make it numeric
                    return column === 5 ?
                        data.replace( /[$,]/g, '' ) :
                        data;
                }
            }
        $(document).ready(function () {

            var buttonCommon = {
                exportOptions: {
                    format: {
                        body: function (data, row, column, node) {
                            // Strip $ from salary column to make it numeric
                            return column === 5 ?
                                data.replace(/[$,]/g, '') :
                                data;
                        }
                    }
                }
            };

            $('#togg-tb1').click(function () {
                if ($("#table1").css("display") == "none") {
                    $("#table1").css("display", "table-cell");
                } else {
                    $("#table1").css("display", "none");
                }
            });

            $('#togg-tb2').click(function () {
                if ($("#table2").css("display") == "none") {
                    $("#table2").css("display", "table-cell");
                } else {
                    $("#table2").css("display", "none");
                }
            });

            $('#togg-tb3').click(function () {
                if ($("#table3").css("display") == "none") {
                    $("#table3").css("display", "table-cell");
                } else {
                    $("#table3").css("display", "none");
                }
            });

            $('#example').DataTable({
                dom: 'Bfrtip',
                buttons:  [
            {
                extend: 'copyHtml5',
                exportOptions: {
                    columns: [ 0,1,2,3,4,5 ]
                    ,format
                }
            },
            {
                extend: 'excelHtml5',
                exportOptions: {
                    columns: [0,1,2,3,4,5],
                    format
                }
            },
            {
                extend: 'pdfHtml5',
                exportOptions: {
                    columns: [0,1,2,3,4,5],
                    format
                }
            }
        ]
            });
        });



    </script>
</head>

<body>
    <table id="example" class="display nowrap" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
                <th>Toggling</th>

            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
                <td>
                    <button type="button" id="togg-tb1">Toggle</button>

                    <table id="table1">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
                <td>
                    <button type="button" id="togg-tb2">Toggle</button>
                    <table id="table2">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>

            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>Junior Technical Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
                <td>
                    <button type="button" id="togg-tb3">Toggle</button>

                    <table id="table3">
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>
                        <tr>
                            <td>Yo Hello</td>
                        </tr>

                    </table>

                </td>
            </tr>

        </tbody>
    </table>

</body>

</html>
0
votes

You could do something similar with the responsive extension:

https://datatables.net/extensions/responsive/classes

$(document).ready(function() {
  var buttonCommon = {
    exportOptions: {
      format: {
        body: function(data, row, column, node) {
          // Strip $ from salary column to make it numeric
          return column === 5 ?
            data.replace(/[$,]/g, '') :
            data;
        }
      }
    }
  };
  $('#example').DataTable({
    dom: 'Bfrtip',
    buttons: ['copy', 'excelHtml5', 'pdf'],
  });
});
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.7.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.7.1/js/buttons.print.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.7.1/css/buttons.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.dataTables.css" />

<body>
  <table id="example" class="display responsive nowrap" style="width:100%">
    <thead>
      <tr>
        <th class="all">Name</th>
        <th class="all">Position</th>
        <th class="all">Office</th>
        <th class="all">Age</th>
        <th class="all">Start date</th>
        <th class="all">Salary</th>
        <th class="none">Toggling</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>Tiger Nixon</td>
        <td>System Architect</td>
        <td>Edinburgh</td>
        <td>61</td>
        <td>2011/04/25</td>
        <td>$320,800</td>
        <td>
          <table id="table1">
            <tr>
              <td>Yo Hello</td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td>Garrett Winters</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>63</td>
        <td>2011/07/25</td>
        <td>$170,750</td>
        <td>
          <table id="table2">
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td>Ashton Cox</td>
        <td>Junior Technical Author</td>
        <td>San Francisco</td>
        <td>66</td>
        <td>2009/01/12</td>
        <td>$86,000</td>
        <td>
          <table id="table3">
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
            <tr>
              <td>Yo Hello</td>
            </tr>
          </table>
        </td>
      </tr>
    </tbody>
  </table>
</body>