Datatables ExcelHTMl5 export add Page-Header and Page-Footer

user_8476830 0
Viewed 544

How can I add page_header and page_footer sections in the exported excel file from datatables so that the header and footer will be in each page while printing the document?

Current Printing view of the exported excel without page_header, page_footer: Current Printing view of the exported excel without page_header, page_footer

Printing view I want with page_header, page_footer: enter image description here

Here is my sample code:

<html>
  <head>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.css"/>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.css"/>
  </head>
  <body>
    <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start<br />date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger<br />Nixon</td>
                <td>System<br />Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td>Garrett<br />Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
            </tr>
            <tr>
                <td>Ashton<br />Cox</td>
                <td>Junior<br />Technical<br />Author</td>
                <td>San<br />Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
            </tr>
            <tr>
                <td>Cedric<br />Kelly</td>
                <td>Senior<br />Javascript<br />Developer</td>
                <td>Edinburgh</td>
                <td>22</td>
                <td>2012/03/29</td>
                <td>$433,060</td>
            </tr>
            <tr>
                <td>Airi<br />Satou</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>33</td>
                <td>2008/11/28</td>
                <td>$162,700</td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start<br />date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
    </table>
    
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.6.5/js/buttons.html5.js"></script>
    <script>
      $(document).ready(function() {
          $('#example').DataTable({
            dom: "Bftrip",
            buttons: [
                {
                  extend: 'excelHtml5',
                                    messageTop : 'Employee List',
                  footer: true,
                  exportOptions: {
                    format: {
                      header: function ( data, row, column, node ) {
                        return data.replace(/<br\s*\/?>/ig, "\r\n");
                      },
                      body: function ( data, row, column, node ) {
                        return data.replace(/<br\s*\/?>/ig, "\r\n");
                      },
                      footer: function ( data, row, column, node ) {
                        return data.replace(/<br\s*\/?>/ig, "\r\n");
                      }
                    },
                  },
                  customize: function(xlsx) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    var sSh = xlsx.xl['styles.xml'];
                    var lastXfIndex = $('cellXfs xf', sSh).length - 1;
                    var newStyleNumber = lastXfIndex + 1;
                    var newStyleNumber2 = lastXfIndex + 2;
                    var currentDoc = "";
                    var currentStyle;
                    
                    var s1 = '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1"><alignment vertical="center" horizontal="center" wrapText="1"/></xf>';
                    var s2 = '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1"><alignment vertical="center" horizontal="center" wrapText="1"/></xf>';
                    sSh.childNodes[0].childNodes[5].innerHTML += s1+ s2;
                    
                    var rowCount = document.getElementById("example").rows.length + 1;
                    for(var i=0; i<3; i++) {
                      $('row:eq(' + i + ') c*', sheet).attr('s', newStyleNumber2);
                    }
                    for(var i=3; i<rowCount; i++) {
                      $('row:eq(' + i + ') c*', sheet).attr('s', newStyleNumber);
                    }
                    $('row:eq(' + i + ') c*', sheet).attr('s', newStyleNumber2);
                    
                    var col = $('col', sheet);
                    $(col[0]).attr('width', 15);
                    $(col[1]).attr('width', 15);
                    $(col[2]).attr('width', 10);
                    $(col[3]).attr('width', 5);
                    $(col[4]).attr('width', 12);
                    $(col[5]).attr('width', 12);
                  }
                }
            ]
          });
      });
    </script>
  </body>
</html>

jszip datatables datatable jquery javascript
1 Answers

With this code, you can create a header and footer page.

"buttons": [
                {
        extend: 'excelHtml5',
        text: 'Excel',
        customize: function( xlsx ) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    // Get reference to the worksheet and parse it to xml nodes
                        // Has to be done this way to avoid creation of namespace atributes.
                        var afSerializer = new XMLSerializer();
                        var xmlString = afSerializer.serializeToString(sheet);
                        var parser = new DOMParser();
                        var xmlDoc = parser.parseFromString(xmlString,'text/xml');
                        //Create header and add it to the worksheet
                        var headerFooter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','headerFooter');
                        sheet.getElementsByTagName('worksheet')[0].appendChild(headerFooter);
                        var nodeHeaderFooter = sheet.getElementsByTagName("headerFooter");
                        //Creation of the header
                        var oddHeader = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','oddHeader');
                        nodeHeaderFooter[0].appendChild(oddHeader);
                        var nodeOddHeader = sheet.getElementsByTagName("oddHeader");
                        //The header/footer column definitions
                        // If unwanted, you can skip a column
                        //&L = Left column
                        //&F = Filename
                        //&A = sheetname
                        //&C = Center column
                        //&D = Date
                        //&T = Time
                        //&R = Right Column
                        //&P = Pagenumber
                        //&N = Total number of pages
                        var txtHeader = "&L"+"&F - &A"+"&R"+"&D - &T";
                        var nodeHeader = xmlDoc.createTextNode(txtHeader);
                        nodeOddHeader[0].appendChild(nodeHeader);
                        //Creation of the footer
                        var oddFooter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','oddFooter');
                        nodeHeaderFooter[0].appendChild(oddFooter);
                        var nodeOddFooter = sheet.getElementsByTagName("oddFooter");
                        var txtFooter = "&R"+"Page &P of &N";
                        var nodeFooter = xmlDoc.createTextNode(txtFooter);
                        nodeOddFooter[0].appendChild(nodeFooter);
                        //Add header and footer to the worksheet
                        sheet.getElementsByTagName('worksheet')[0].appendChild(headerFooter);
                    }
                }
        ]

I found the solution at this link https://codepen.io/RedJokingInn/pen/yMqezX

user_22492430
Related