Datatables/Tabletools: Format Data as Text When Exporting to Excel

Datatables / TableTools: format data as text when exporting to Excel

TableTools does not create a real excel file, it creates a csv file instead. Those contain only raw data, no formatting. Although the leading zeros are there, Excel usually will not show them. You have several options here:

• change the formatting from within Excel
• open the csv file from Excel's open dialog, from which you should be able to mark columns as text (you might need to change the file type to txt)
• add quotes around the data
• create a real excel file via some external library

How to export dataTable as Excel format?

Use this approach

oTableTools: {
  sSwfPath: "copy_csv_xls_pdf.swf",
  aButtons: [
    { sExtends: "xls",
      mColumns: 'visible',
      sFileName: 'export.xls',
      sToolTip: 'Save current table as XLS'
    }
  ]
}

The important things is sExtends: "xls" and sFileName to ensure a proper filename attached to the downloaded file.

But as other people mention in comments, you should really consider using the buttons plugin, here is a demo using that -> https://jsfiddle.net/zm825k01/

Datatables: How to show a message while data is exported (Excel, PDF, etc)?

As I'm using server side processing to get the data of my datatable I enabled the "Processing" and "bprocessing" options and customize the spinner with language' parameters as follows:

$('#my_table').DataTable({
        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
        "columnDefs": [
            { "className": "dt-body-center", "targets": "_all" }
        ],
        "responsive": true,
        "processing": true, 
        "bProcessing": true,
        "autoWidth": false, 
        "serverSide": true,
        "ajax": "url",
        "aaSorting": [2, 'desc'],

        "pagingType": "full_numbers",
        "language": {
            "sProcessing": '<i class="fas fa-asterisk fa-spin fa-6x fa-fw"></i> 
             <br>PROCESSING <br> Please wait...',
            },
           },
        dom:
            "<'row'<'col-sm-4'B><'col-sm-4 text-center'l><'col-sm-4'f>>" +
            "<'row'<'col-md-12'tr>>" +
            "<'row'<'col-md-7'i><'col-sm-5'p>>",

            buttons:[
            {
                extend: 'excelHtml5',
                text:'<i class="fas fa-file-excel fa-lg"></i>',
                titleAttr: 'Exportar a Excel (XLSX)',
                className:'btn btn-success',
                title:'DAWPs Data',
                action: newexportaction,
            },
            {
                text:'<i class="fas fa-sync"></i>',
                titleAttr: 'Limpiar filtro',
                className:'btn btn-secondary',
                action: function (e, dt, node, config) {
                        dt.search('').draw();
                        dt.order([2,'desc']).draw();
                }
            },
        ],

    }); 

With these modifications now when I clicked on export to excel button the spinner is showed in the table body; according the information I found, this only works when you're using server side.

Rails 4 datatables Export to excel file is working but file is not readable

Posting answer here because I didn't find it documented anywhere. After spending hours it worked by changing js_compressor in production.rb. Looks like support for ES5/ES6 is also needed for datatables while precompiling assets. The excel is now able to display all types of characters including support for other language characters. Previously it was failing to open excel because of special characters & some content in japanese.

  # Compress JavaScripts
  #config.assets.js_compressor = :uglifier
  config.assets.js_compressor = Uglifier.new(harmony: true)  

How to export all rows from Datatables using Ajax?

According to DataTables documentation there is no way to export all rows when you are using server side:

Special note on server-side processing: When using DataTables in server-side processing mode (serverSide) the selector-modifier has very little effect on the rows selected since all processing (ordering, search etc) is performed at the server. Therefore, the only rows that exist on the client-side are those shown in the table at any one time, and the selector can only select those rows which are on the current page.

I worked this around by adding an 'ALL' parameter to the length menu and training end users to display all records before doing a PDF (or XLS) export:

var table = $('#example').DataTable({
    serverSide: true,
    ajax: "/your_ajax_url/",
    lengthMenu: [[25, 100, -1], [25, 100, "All"]],
    pageLength: 25,
    buttons: [
        {
            extend: 'excel',
            text: '<span class="fa fa-file-excel-o"></span> Excel Export',
            exportOptions: {
                modifier: {
                    search: 'applied',
                    order: 'applied'
                }
            }
        }
    ],
    // other options
});

---

Related Topics