line break when exporting to excel Datatable

Question

I have my project in this way :https://jsfiddle.net/Eufragio/u342qgoz/1/

When exporting excel I need an order or a more visible way to show my results

 $(document).ready( function () {
  var table = $('#example').DataTable({
    dom: 'Btirp',
    buttons: [{
    extend: 'csvHtml5',
    text: 'CSV',
    filename: 'csv_file',
    footer: true
    },
    {
    extend: 'excelHtml5',
    text: 'Excel',
    filename: 'excel_file',
    footer: true
    }],
      //Total General

    "footerCallback": function (row, data, start, end, display) {
        var api = this.api(),
          data;

        // Remove the formatting to get integer data for summation
        var intVal = function (i) {
            return typeof i === 'string' ?
              i.replace(/[\L,]/g, '') * 1 :
              typeof i === 'number' ?
                i : 0;
        };

        /*
   // Total over all pages // Total en todas las páginas
   total = api
     .column(5)
     .data()
     .reduce(function(a, b) {
       return intVal(a) + intVal(b);
     }, 0);

   // Total over this page // Total sobre esta pagina
   pageTotal = api
     .column(5, {
       page: 'current'
     })
     .data()
     .reduce(function(a, b) {
       return intVal(a) + intVal(b);
     }, 0);

   // Update footer //Pie de página de actualización
   $(api.column([5, 3]).footer()).html(
     // '' + pageTotal + ' ( L' + total + ' total)'
     //'' + total.toFixed(2)
     '' + total

   );
   */



        // Total over all pages // Total en todas las páginas
        total = api
          .column(3)
          .data()
          .reduce(function (a, b) {
              return intVal(a) + intVal(b);
          }, 0);

        // Total over this page // Total sobre esta pagina
        pageTotal = api
          .column(3, {
              page: 'current'
          })
          .data()
          .reduce(function (a, b) {
              return intVal(a) + intVal(b);
          }, 0);

        // Update footer //Pie de página de actualización
        $(api.column(3).footer()).html(
          // '' + pageTotal + ' ( L' + total + ' total)'
          //'' + total.toFixed(2)
          '' + total

        );
    },

    "columnDefs": [{
        "visible": false,
        "targets": 2
    }],
    "order": [
      [2, 'asc']
    ],
    "displayLength": 25,
    "drawCallback": function (settings) {
        var api = this.api();
        var rows = api.rows({
            page: 'all'
        }).nodes();
        var last = null;

        // Remove the formatting to get integer data for summation
        var intVal = function (i) {
            return typeof i === 'string' ?
              i.replace(/[\$,]/g, '') * 1 :
              typeof i === 'number' ?
                i : 0;
        };
        var groupTotal = {};
        api.column(2, {
            page: 'all'
        }).data().each(function (group, i) {
            group_assoc = group.replace(' ', "_");
            console.log(group_assoc);
            if (typeof groupTotal[group_assoc] != 'undefined') {
                groupTotal[group_assoc] = groupTotal[group_assoc] + intVal(api.column(5).data()[i]);

                /*
       $(api.column(2).footer()).html(

         '' + total[group_assoc]

       );
       */

            } else {
                groupTotal[group_assoc] = intVal(api.column(5).data()[i]);
            }
            if (last !== group) {
                $(rows).eq(i).before(
                  '<tr class="group"><td colspan="4">' + group + '</td><td class="' + group_assoc + '"></td></tr>'
                );

                last = group;
            }
        });

        var footerText = [];
        var footerTotal = [];
        for (var key in groupTotal) {
            $("." + key).html("L" + groupTotal[key].toFixed(2));
            footerText.push(key);  // Get array of group names
            footerTotal.push("L" + groupTotal[key].toFixed(2));  // Get array of group totals
        }
        // Update footer with group names, each on a newline
        $(api.column(4).footer()).html(
         footerText.join('<br>')
       );
        // Update footer with group totals, each on a newline
        $(api.column(5).footer()).html(
         footerTotal.join('<br>')
       );




    }
  });


    });

My problem is when exporting to excel, that the results bring me this way:

The result that I hope is this:

in which way you could show this result or what other way of doing it recommend

Daniel · Accepted Answer · 2019-11-15 09:10:58Z

5

Old topic but, mayby someone will be looking for a solution. I had the same problem on version 1.10.10 of DataTables which not supports the "\r\n" characters. After many hours of searching for a solution I found it, it was enough to upgrade DataTables version to the newest (currently 1.10.20).

Now everything is working properly with this code:

extend: 'excelHtml5',
filename: 'file_name',
text: 'Save as Excel',
exportOptions: {
    format: {
        body: function(data, column, row) {
            if (typeof data === 'string' || data instanceof String) {
                data = data.replace(/<br\s*\/?>/ig, "\r\n");
            }
            return data;
        }
    }
}

answered Nov 15, 2019 at 9:10

Daniel

1381 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

line break when exporting to excel Datatable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related