How to saved a excel files include multi title

How to saved a excel files include multi title

HsuTingHuaHsuTingHua Posts: 2Questions: 1Answers: 0

Hi everyone,

I need to save DataTable content to an Excel file, but I am having a trouble with the title row.
If the thead tag includes multiple tr tags and the th tag uses properties like rowspan and colspan,
the Excel file loses the title row. Is there a solution to this issue? Thank you.

        var table = $('#example').DataTable({
            data: tArray,
            "order": [[ 3, "asc" ],[ 1, "asc" ]],
            dom: 'Bfrtip',
            buttons: [
               {
                   extend: 'excelHtml5',
                   text: 'TestExcel',
                   className: 'BtnExcel',
                   filename:'Excel001',
                   exportOptions: {
                       modifier: {
                           page: 'current'
                       },
                       columns: "thead th:not(.noExport)",
                       rows: function (indx, rowData, domElement) {
                           return $(domElement).css("display") != "none";
                       }

                   },
                   customize: function (xlsx) {
                       var sheet = xlsx.xl.worksheets['sheet1.xml'];
 
                       $('row c[r^="C"]', sheet).attr('s', '2');
                   }

               }
            ]
        });
<!DOCTYPE html>
<html>
  <head>
    <script src="https://code.jquery.com/jquery-1.11.3.min.js"></script>

    <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
    <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>

    <meta charset=utf-8 />
    <title>DataTables - JS Bin</title>
  </head>
  <body>
    <div class="container">
      <table id="example" class="display nowrap" width="100%">
        <thead>
          <tr>
            <th rowspan="2">Name</th>
            <th rowspan="2">Position</th>
            <th rowspan="1" colspan="2" style="text-align:center">The data can't be </br>saved to the Excel file</th>
            <th rowspan="1" colspan="2" style="text-align:center">The data can't be </br>saved to the Excel file</th>
          </tr>
         <tr>
            <th>Office</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>61</td>
            <td>2011/04/25</td>
            <td>$3,120</td>
          </tr>
          <tr>
            <td>Garrett Winters</td>
            <td>Director</td>
            <td>Edinburgh</td>
            <td>63</td>
            <td>2011/07/25</td>
            <td>$5,300</td>
          </tr>
        </tbody>
      </table>
    </div>
  </body>
</html>

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    At the moment, no there isn't I'm sorry to say. This is a limitation in the export - it doesn't support either colspan/rowspan, or multi-row headers. I do plan to address this, but most likely it will be done as part of the DataTables 2 work - which currently doesn't have a release date other than "when it is ready".

    Allan

  • HsuTingHuaHsuTingHua Posts: 2Questions: 1Answers: 0

    Hi Allan
    Although there is no way to solve this problem, I still thank you for answering my question.

    Hsu Ting Hua

Sign In or Register to comment.