Datatables: Hide values within a cell when exporting to CSV, EXCEL

Question

I have a table in jquery datatables. The cell shows the first name of the department. When you mouseover the name the department location pops up as a tooltip. For example:

<TABLE aria-describedby="DataTables_Table_2_info" role="grid" id="DataTables_Table_2" class="table table-tools table-dynamic  table-[object Object] dataTable" style="">
            <thead>
            <TR role="row">
                <TH aria-label="Head 0: activate to sort column ascending" style="width: 0px;" colspan="1" rowspan="1" aria-controls="DataTables_Table_2" tabindex="0" class="sorting">DEPARTMENT
                </TH>
                <TH aria-label="Head 0: activate to sort column ascending" style="width: 0px;" colspan="1" rowspan="1" aria-controls="DataTables_Table_2" tabindex="0" class="sorting">DATE
                </TH>
            </tr>
            </thead>
            <tbody>
                <tr class="odd" role="row" >
                    <td class="sorting_1">
                        <font color="#000000" size="2.5em"><a href="#" class="tooltips"><u>Accounts Department<span class="tooltiptext">3rd Floor West Building</span></u></a></font>
                    </td>
                    <td class="sorting_1" align=center > 
                        <font color="green"> 0.00%</font>
                    </td>
                </tr>   
            </tbody>
        </table>

However when you export with Excel the location exports as well. so the end result is something like:

Accounts Department3rd floor West Building

However the tooltiptext code is in the same <td>. Is there a way to exclude or to hide this part from exporting? For example to surround the code with something like <exclude> ?

After rummaging through the jquery.dataTables.min.js file this is the code that is executed when the save to excel is clicked:

fnClick:function(e,t,s)
  {
   this.fnSetText(s,this.fnGetTableData(t))
  }
 }),
   xls:e.extend({},
   TableTools.buttonBase,
     {
     sAction:"flash_save",
     sCharSet:"utf16le",
     bBomInc:!0,
     sButtonClass:"DTTT_button_xls",
     sButtonText:"<i class='fa fa-file-excel-o'></i>"

Much appreciated.

Answer 1

1. Add another column to your datatable that contains the cell data that you want displayed in your export, aka the data without the tooltip markup.
2. Hide the column you just added from being displayed in the browser
3. Select the columns you want exported to excel being sure to exclude the column that contains the tooltip markup and include the UI hidden column that contains just the data without the tooltip markup.

HTML:

<table id="exampleTable">
    <thead>
        <tr role="row">
            <th aria-hidden="true">DEPARTMENT</th> <!-- Used in data export -->
            <th aria-label="Head 0: activate to sort column ascending" style="width: 0px;" colspan="1" rowspan="1" aria-controls="DataTables_Table_2" tabindex="0" class="sorting">DEPARTMENT
            </th> <!-- used in browser UI -->
            <th aria-label="Head 0: activate to sort column ascending" style="width: 0px;" colspan="1" rowspan="1" aria-controls="DataTables_Table_2" tabindex="0" class="sorting">DATE
            </th>
        </tr>
    <thead>
    <tbody>
        <tr>
            <td>Accounts Department</td>
            <td><font color="#000000" size="2.5em"><a href="#" class="tooltips"><u>Accounts Department<span class="tooltiptext">3rd Floor West Building</span></u></a></font></td>
            <td>03/20/2018</td>
        </tr>
    </tbody>
</table>

JS:

$('#reportTable').DataTable({
    columnDefs: [
        // hide the first column from browser display (starts at 0)
        {'targets':[0], 'visible':false, 'searchable':false}
    ],
    buttons: [
        // configure the export to excel button (export to csv is similar)
        {
            extend: 'excelHtml5',
            title: "Example Excel Export",
            exportOptions: {
                columns: [0,2] // include the UI hidden column 0 and date column 2 (excludes the UI visible department column 1)
            }
        }            
    ]
});