Datatable does not export second row in footer

user_2689469 0
Viewed 1836

I have this datatable issue where I am not able to export second row of footer in excel. below is the snippet. when I export footer 1 is there in the sheet but footer 2 is not.

$(document).ready( function () {
    $('#table').DataTable({
        dom: 'Bfrtip',
        buttons: [
            { extend: 'excelHtml5', footer: true },
        ]
        
    });
} );
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="//cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
<link href="//cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet"/>
<table id="table">
    <thead>
        <tr>
            <th>Header</th>
            <th>Header</th>
            <th>Header</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>body</td>
            <td>body</td>
            <td>body</td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <th>Footer 1</th>
            <th>Footer 1</th>
            <th>Footer 1</th>
        </tr>
        <tr>
            <th>Footer 2</th>
            <th>Footer 2</th>
            <th>Footer 2</th>
        </tr>
    </tfoot>
</table>

datatable jquery
1 Answers

Unfortunately DataTables does not support multi-row table footer, but there is a customize function which allows to customize the XLSX file:

customize

Since: 1.2.0

This method can be used to modify the XLSX file that is created by Buttons. The first parameter that is passed in is an object that contains the XML files and the object structure matches the file system paths for those files in an XLSX file. Customisation of the XLSX file is a complex topic - please refer to the Customisation section in the excelHtml5 button documentation for full details.

As of Buttons 1.5.2 this function is passed three parameters:

  1. An object that contains the XML files in the ZIP file structure used by Excel
  2. The button configuration object
  3. A DataTables API instance for the table the button belongs to.

We can utilize the customize callback and combine it with reading the table footer and then adding the extra footer rows to the XML XLS using jQuery:

$(document).ready( function () {
  $('#table').DataTable({
    dom: 'Bfrtip',
    buttons: [
      {
        extend: 'excelHtml5',
        footer: true,
        customize: (xlsx, config, dataTable) => {
          let sheet = xlsx.xl.worksheets['sheet1.xml'];
          let footerIndex = $('sheetData row', sheet).length;
          let $footerRows = $('tr', dataTable.footer());

          // If there are more than one footer rows
          if ($footerRows.length > 1) {
            // First header row is already present, so we start from the second row (i = 1)
            for (let i = 1; i < $footerRows.length; i++) {
              // Get the current footer row
              let $footerRow = $footerRows[i];

              // Get footer row columns
              let $footerRowCols = $('th', $footerRow);

              // Increment the last row index
              footerIndex++;

              // Create the new header row XML using footerIndex and append it at sheetData
              $('sheetData', sheet).append(`
                <row r="${footerIndex}">
                  ${$footerRowCols.map((index, el) => `
                    <c t="inlineStr" r="${String.fromCharCode(65 + index)}${footerIndex}" s="2">
                      <is>
                        <t xml:space="preserve">${$(el).text()}</t>
                      </is>
                    </c>
                  `).get().join('')}
                </row>
              `);
            }
          }
        }
      }
    ]
  });
});
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="//cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.6.1/js/buttons.html5.min.js"></script>
<link href="//cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet"/>
 <table id="table">
    <thead>
        <tr>
            <th>Header</th>
            <th>Header</th>
            <th>Header</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>body</td>
            <td>body</td>
            <td>body</td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <th>Footer 1</th>
            <th>Footer 1</th>
            <th>Footer 1</th>
        </tr>
        <tr>
            <th>Footer 2</th>
            <th>Footer 2</th>
            <th>Footer 2</th>
        </tr>
        <tr>
            <th>Footer 3</th>
            <th>Footer 3</th>
            <th>Footer 3</th>
        </tr>
    </tfoot>
</table>

user_18896850
Related