Datatables.net - Export to Excel is showing error while customizing

chandjogani

I am using SPFx, typescript and datatables.net for my webpart and I want to export to excel and do some customization on data like align to left. Exporting is working fine but while I add code for customization its showing error. here is my code:

 window["JSZip"] = JSZip;
      $('#trackerDataTable').DataTable({
        dom: 'Bfrtip',
        buttons: [
          {
            extend: 'excel',
            text: '<i></i>&nbsp;Export to Excel &nbsp;',
            filename: 'Timecards',
            extension: '.xlsx',
            exportOptions: {
              columns: ':visible'
            },
            customize: function (xlsx) {
              debugger;
              var sheet = xlsx.xl.worksheets['sheet1.xml'];
              $('row c[r^="C"]', sheet).attr('s', '50');
            }
          },
        ]
});

Here in the above code xlsx.xl is showing below error :

enter image description here

It would be great if you can help me here to make it work. Thank you in advance.

leejohny653

Damm even I am having a similar kind of issue, I have searched all over the internet and even have posted on number of threads on different forum, no solution seems to work. I am really frustrated, can anyone of you here help me resolve this issue, I am very much tired now.

chandjogani

Hi @leejohny653 I found the solution now. I am using separate function for it. I am using typescript here.

 window["JSZip"] = JSZip;
      $('#trackerDataTable').DataTable({
        dom: 'Bfrtip',
        buttons: [
          {
            extend: 'excel',
            className: 'btn-primary',
            text: '  Export to Excel  ',
            filename: 'Timecards',
            extension: '.xlsx',
            exportOptions: {
              columns: ':visible'
            },
            customize: function (xlsx) {
              debugger;
              ExportTimecardsWebPart.exportExcel(xlsx); // create separate function
            }
            // customize: function(xlsx) {
            //   debugger;
            //   var sheet = xlsx.xl.worksheets['sheet1.xml'];
            //   $('row c[r^="C"]', sheet).attr('s', '50');
            // }
          },
        ],
});

 public static exportExcel(xlsx: any): void {
    debugger;
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    $('row c[r^="C"]', sheet).attr('s', '50');
  }

leejohny653

Thank you so much.. mobdro