0

I'm really stuck in exporting a nested table to Excel as a CSV file. How can I export nested table data to Excel using DataTables? I have given my code below.

I want output like below

enter image description here

$(document).ready(function() {
  var myData = [{
    employee: "Bob Smith",
    project: "Project",
    role: "Leader",
    itemno: "ABCDF",
    amount: "$50.00",
    jobtitle: "Software Engineer"

  }, {
    employee: "Tom Peterson",
    project: "Project1",
    role: "Assistant",
    itemno: "ABCDEFG",
    amount: "$40.00",
    jobtitle: "Business Analyst"
  }];

  var myColumns = [{
      title: "Employee",
      data: "employee"
    },
    {
      title: "<table class='table table-bordered table-sm'><thead><tr><td>Item Details</td><td>Amount</td></tr></thead></table>",
      data: null,
      render: function(data, type, row, meta) {
        var subtableHtml = "<table class='table table-bordered table-sm'>";

        subtableHtml += "<tbody>"
        subtableHtml += "<tr>";
        subtableHtml += "<td>" + data.project + "</br>" + data.role + "</br>" + data.itemno + "</td>";
        subtableHtml += "<td>" + data.amount + "</td>";
        subtableHtml += "</tr>";

        subtableHtml += "</tbody></table>";
        return subtableHtml;
      }
    },
    {
      title: "Designation",
      data: "jobtitle"
    },
  ];

  $('#dataTable').dataTable({
    searching: false,
    paging: false,
    data: myData,
    columns: myColumns,
    dom: "<'row'<'col-sm-12 col-md-4 col-lg-4 col-xs-12'l><'col-sm-12 col-md-4 col-lg-4 col-xs-12 text-center'B><'col-sm-12 col-md-4 col-lg-4 col-xs-12'f>>" +
      "<'row'<'col-sm-12 col-md-12 col-lg-12 col-xs-12'tr>>" +
      "<'row'<'col-sm-12 col-md-12 col-lg-5 col-xs-12'i><'col-sm-12 col-md-12 col-lg-7 col-xs-12 text-center'p>>",
    buttons: [{
      extend: 'excel',
      text: '<a href="#" class="aExcel"><img src="https://upload.wikimedia.org/wikipedia/commons/thumb/8/86/Microsoft_Excel_2013_logo.svg/1043px-Microsoft_Excel_2013_logo.svg.png" style="width:25px;height:25px" class="imgExcel" alt="Excel"/></a>',
      tag: 'span',
      title: '',
      init: function(api, node, config) {
        $(node).removeClass('btn btn-default btn-secondary buttons-excel buttons-html5')
      },
      exportOptions: {
        columns: [0, 1, 2]
      },
    }]
  });
});
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/buttons/1.6.4/css/buttons.dataTables.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css" rel="stylesheet" />

<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>

<script src="https://cdn.datatables.net/buttons/1.6.4/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.flash.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.6.4/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.print.min.js"></script>

<div class="container-fluid">
  <div class="row">
    <div class="col-md-8">
      <h3>Nested Table Export to Excel :</h3>
      <table id="dataTable" class="table table-sm table-bordered" style="width:100%"></table>

    </div>
  </div>
</div>
PK-1825
  • 1,431
  • 19
  • 39
  • Currently it looks like the code will export all the data in each sub-table as a single string - something like `AlphaLeaderBetaAssistant` (that is what I get when I run your code). Can you [edit] your question to show how you need that data to be structured? What does success look like? – andrewJames Oct 21 '20 at 14:38
  • @andrewjames Thank you, I have updated image above I want output like that, but as you said all sub table td data storing as string in main table td – PK-1825 Oct 22 '20 at 07:13
  • Thank you for the clarification. Because the layout of the data in Excel is very different from the layout in the HTML table, you would have to customize how data is written to Excel, by creating the required new cells and rows. This can be done - but even with a much simpler scenario than yours, I believe it is [a lot of work](https://stackoverflow.com/questions/61664874/how-to-add-additional-data-from-an-array-to-export-to-excel-in-jquery-data-table/61704469#61704469), because it involves manipulating the underlying XML file which stores Excel data. – andrewJames Oct 22 '20 at 13:12
  • On the other hand, if you are OK with simplifying the way the HTML data is shown in the DataTable (by re-arranging your `myData` before processing it), then it will become much more straightforward to write the data to Excel in the way you want. If that is an option, I can provide more details to show how that could work. – andrewJames Oct 22 '20 at 13:12
  • you are right, I have re-Arranged "myData" please check above code. I have also added screen shot of of expected result. – PK-1825 Oct 23 '20 at 06:45
  • Unfortunately, you still have nested HTML table cells - and that means you are still facing a more complex structure problem when writing your data to Excel. My approach would involve compromising on the layout of the HTML data (no nested `` cells), in order to make the Excel data easier to handle. It looks like that is not what you want, though. So my approach would not work for you. – andrewJames Oct 23 '20 at 17:57
  • Yeah, actually I wanted like above, I would have done this by myself but while exporting to csv I need to rearrange XML, but I dont know enough about that. Its ok I will try, anyhow thank you for your quick reply @andrewjames. – PK-1825 Oct 24 '20 at 23:10

0 Answers0