DataTable table order by numeric, not by text

DataTable order default by text, i.e 1, 11, 2,3,4,5,6...
I need the order numeric, i.e. 1,2,3,4,5,6,11
Using                                       
                       "columns": [
                                         { "orderDataType": "dom-text", "type": "numeric" }
                                       ],
Example:
         table= $("#result").find('#'+tableId).DataTable( {
                                         destroy: true,
                                        paging: false,
                                        "columns": [

                                         { "orderDataType": "dom-text", "type": "numeric" }
                                       ],
                                    fixedHeader: true
                                } );
Reference:
https://datatables.net/reference/option/columns.orderDataType
Example1:

$('#example').dataTable( {

  "columns": [

    null,

    null,

    { "orderDataType": "dom-text" },

    { "orderDataType": "dom-text", "type": "numeric" },

    { "orderDataType": "dom-select" },

    { "orderDataType": "dom-checkbox" }

  ]

} );

Example2:



$('#example').dataTable( {

  "columnDefs": [

    { "orderDataType": "dom-text", "targets": [ 2, 3 ] },

    { "type": "numeric", "targets": 3 },

    { "orderDataType": "dom-select", "targets": 4 },

    { "orderDataType": "dom-checkbox", "targets": 5 }

  ]

} );


 

DataTable export excel messup when search when colums containing select, input and editable div

 I have a table containing select, input and editable div in columns.
I have scripts to make export to EXCEL correctly in  DataTable  when  not using search box.
When using search box, exporting to EXCEL producing wrong  value.
I can not find a solution. Temporarily when in search mode, I hid the EXCEL button
as following:
                $('input[type="search"]').keyup(function(){

                   var search = $(this).val();                       
                   if(search.trim()=='')
                     $(".dt-buttons").show();
                   else
                     $(".dt-buttons").hide();

                  });    

Datatable export to excel bug not completly updated according to sorting

When I  click the header column to sort in a table using DataTable,
the export excel is only half columns correct. This is due to that my columns have
input, select and editable div. If  all columns are text, the export after sorting is fine.

I can not find a solution. The temporarily solution is  always  to export the original
sorting, i.e not export the current view after sorting.
 code
 modifier: { order: 'index' },

Example:
                                 extend: 'excel',
                                  exportOptions: {
                                          columns: ':visible',
                                          modifier: { order: 'index' },
                                 },

Make DataTable Cell contenteditable

Example code  DataTable Cell  contenteditable
html:
<div style="padding:20px">
  <div class="row rowAddLine">
    <div style=" text-align: right;">
      <button type="button" id="btnAddRow" class="btn btn-info"><i class="fa fa-plus" style="margin-right:10px;"></i>Add</button>
    </div>
  </div>
  <div class="row">
    <div class="table-responsive">
      <table id="id_table" class="table table-striped table-bordered" cellspacing="0" width="100%">
        <thead>
          <tr>
            <th>Column1</th>
            <th>Column2</th>
            <th>Column3</th>
            <th class="noWidth">Column4</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td>freerf</td>
            <td>rfrfe</td>
            <td>erferfrfe</td>
            <td class="noWidth">
              <button class="action btn btn-default" type="button">
                <span class="glyphicon glyphicon-remove"></span>
              </button>
            </td>
          </tr>
        </tbody>
      </table>
    </div>
  </div>
</div>
 
JS

$(document).ready( function () {
    var myTable = $('#id_table').DataTable({"bSort" : false, "createdRow": function ( row, data, index ){
                    $("td:last", row).addClass("noWidth");}
                });
 
    function makeEmptyArray(nbElements){
            var arr = [];
            for(var i=0; i<nbElements-1; i++)
            {
                arr.push("");
            }
            arr[i] = '<button type="button" class="action btn btn-default"><span class="glyphicon glyphicon-remove"></span></button>';
            return arr;
        }


 var emptyArrayOfElement = makeEmptyArray(myTable.columns().nodes().length);


  // Add row after click on the button 'add row'
  $("#btnAddRow").click(function(){
    var row = myTable.row.add(emptyArrayOfElement).draw(false).node();
    myTable.page('last').draw('page');
    $('html, body').animate({
      scrollTop : $(row)[0].offsetTop
    }, 500);
  });
 
 
  // Making TD editable exept td with action button
  $('body').on('dblclick', 'td:not(:has(button))', function(){
    // The cell that has been clicked will be editable
    $(this).attr('contenteditable', 'true');
    var el = $(this);
    // We put the cursor at the beginning
    var range = document.createRange();
    var sel = window.getSelection();
    if(el[0].childNodes.length > 0)
    {
      range.setStart(el[0].childNodes[0],0);
      range.collapse(true);
      sel.removeAllRanges();
      sel.addRange(range);
    }
    // The cell have now the focus
    el.focus();

    $(this).blur(endEdition);
  });

  function endEdition()
  {
  
    // We get the cell
    var el = $(this);
   
    // We tell to datatable to refresh the cache with the DOM, like that the filter will find the new data added in the table.
    // BUT IF WE EDIT A ROW ADDED DYNAMICALLY, THIS INSTRUCTION IS A PROBLEM
    myTable.cell( el ).invalidate().draw();
   
    // When the user finished to edit a cell and click out of the cell, the cell can't be editable, unless the user double click on this cell another time
    el.attr('contenteditable', 'false');

    el.off('blur', endEdition); // To prevent another bind to this function
  }
 
  } );
  CSS
  .noWidth {width: 0px;}
  Reference
  https://codepen.io/robert91/pen/wWwmeE

DataTable FAQ - Update search when changing input, textarea and selected text (dropdown menu)

When changing input and selected text (dropdown menu) in datatable, datatable should immediately
update the search.
For column with selected text from dropdown with select tag:
a) get value for search
     $.fn.dataTableExt.ofnSearch['html-select'] = function(sData) {
        return $("<div/>").html(sData).find(':selected')
                            .text();
     };
b) update when select change
    $('#'+tableId).find("td select").on('change', function() {
          var $td = $(this).closest('td');
          var value = this.value;
          $td.find('option').each(function(i, o) {
            $(o).removeAttr('selected');
            if ($(o).val() == value){ $(o).attr('selected','selected');}
          })
          $td.find('select').val(value);
           table.cell($td).invalidate().draw();             
       });    For column with input and textarea  tag:   
a) get value for search   
     $.fn.dataTableExt.ofnSearch['html-input'] = function(value) {
        return $(value).val();
     };       
b) update when input change
    $('#'+tableId).find("td input").on('change', function() {
        var $td = $(this).parent();
             $td.find('input').attr('value', this.value);
           table.cell($td).invalidate().draw();
    });
c) update when textarea change
      $("#result").find('#'+tableId).find('textarea').on('change', function() {
        var $td = $(this).parent();
       $td.find('textarea').text(this.value);
       table.cell($td).invalidate().draw();          
    });    
Example JS code is as following (assume 9th column (column=8) with select dropdown
and third column (column=2) with input, 10thcolumn (column=9) for textarea), assume table id is defaultTable:   

     $.fn.dataTableExt.ofnSearch['html-select'] = function(sData) {
        return $("<div/>").html(sData).find(':selected')
                            .text();
     };
     $.fn.dataTableExt.ofnSearch['html-input'] = function(value) {
        return $(value).val();
     };       
    var tableId='defaultTable';
    table= $("#result").find('#'+tableId).DataTable( {
                                     destroy: true,
                                    paging: false,
                                    columnDefs: [
                                       { "type": "html-select", "targets": [8] },
                                        { "type": "html-input", "targets": [2,9] }
                                    ],
                                fixedHeader: true
                            } );   
                           
    $('#'+tableId).find("td input").on('change', function() {
        var $td = $(this).parent();
             $td.find('input').attr('value', this.value);
           table.cell($td).invalidate().draw();
    });
     $("#result").find('#'+tableId).find('textarea').on('change', function() {
        var $td = $(this).parent();
       $td.find('textarea').text(this.value);
       table.cell($td).invalidate().draw();          
    });    

    $('#'+tableId).find("td select").on('change', function() {
          var $td = $(this).closest('td');
          var value = this.value;
          $td.find('option').each(function(i, o) {
            $(o).removeAttr('selected');
            if ($(o).val() == value){ $(o).attr('selected','selected');}
          })
          $td.find('select').val(value);
           table.cell($td).invalidate().draw();             
       });            

DataTable FAQ - exporting to EXCEL for column with input and selected text (dropdown menu)

For column with selected text from dropdown with select tag:
    data = table
    .cell( {row: row, column: column} )
    .nodes()
    .to$()
    .find(':selected')
    .text();

For column with input  tag:   
                            data = table
                            .cell( {row: row, column: column} )
                            .nodes()
                            .to$()
                            .find('input')
                            .val();   
  

Example JS code is as following (assume 9th column (column==8) with select dropdown
and thrid column (column--2) with input):   
    var buttonCommon = {
        exportOptions: {
            format: {
                body: function(data,  row, column) {
                             if(column==8)
                            data = table
                            .cell( {row: row, column: column} )
                            .nodes()
                            .to$()
                            .find(':selected')
                            .text();
                             if(column==2)
                            data = table
                            .cell( {row: row, column: column} )
                            .nodes()
                            .to$()
                            .find('input')
                            .val();               
                            data = data.replace(/<br\s*\/?>/ig, "\r\n");//should be with wrapped text
                            return data;
                },
                header: function(data, column, row) {
                            data = data.replace(/<br\s*\/?>/ig, "\r\n");//should be with wrapped text
                            return data;
                }
              }
            }
    };
    $.extend(true, $.fn.dataTable.defaults, {
              "lengthChange": false,
              "pageLength": 100,
              "orderClasses": false,
              "stripeClasses": [],
               "destroy": true,
              dom: 'Bfrtip',
              buttons: [
                    $.extend(true, {}, buttonCommon, {
                      extend: 'excel',
                      exportOptions: {
                              columns: ':visible'
                     },

                      customize: function(xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            $('row c[r^="A"]', sheet).attr( 's', '50' ); //<-- left aligned text for A column                                       
                            var col = $('col', sheet);
                            col.each(function () {
                                     $(this).attr('width', 20);
                            });
          
              }
            })
          ]
      }
    );

DataTable FAQ - Set text align and cell width when exporting to EXCEL

1) To set A column text left aligned when exporting to EXCEL using DataTable
   $('row c[r^="A"]', sheet).attr( 's', '50' );

  2) To set All column width 20
    var col = $('col', sheet);
    col.each(function () {
             $(this).attr('width', 20);
    });
  
3) To set first column width 10
    $(col[0]).attr('width', 10);

4) Example JS code is as following:   
    var buttonCommon = {
        exportOptions: {
            format: {
                body: function(data,  row, column) {
               
                            data = data.replace(/<br\s*\/?>/ig, "\r\n");//should be with wrapped text
                            return data;
                },
                header: function(data, column, row) {
                            data = data.replace(/<br\s*\/?>/ig, "\r\n");//should be with wrapped text
                            return data;
                }
              }
            }
    };
    $.extend(true, $.fn.dataTable.defaults, {
              "lengthChange": false,
              "pageLength": 100,
              "orderClasses": false,
              "stripeClasses": [],
               "destroy": true,
              dom: 'Bfrtip',
              buttons: [
                    $.extend(true, {}, buttonCommon, {
                      extend: 'excel',
                      exportOptions: {
                              columns: ':visible'
                     },

                      customize: function(xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            $('row c[r^="A"]', sheet).attr( 's', '50' ); //<-- left aligned text for A column                                       
                            var col = $('col', sheet);
                            col.each(function () {
                                     $(this).attr('width', 20);
                            });
                            $(col[0]).attr('width', 10);
                            $(col[1]).attr('width', 40);
                            $(col[2]).attr('width', 50);   
          
              }
            })
          ]
      }
    );

DataTable FAQ - Keep line break when exporting to EXCEL, columns and rows visible

To keep line break <br/>, <br /> or <br> when exporting to EXCEL,
we can use data.replace(/<br\s*\/?>/ig, "\r\n") to replace
<br/>, <br /> or <br> to \r\n in EXCEL.
But the cell text should be wrapped.
    $('row c', sheet).each( function () {
                $(this).attr( 's', '55' );
    });   
Only export visible rows and columns to EXCEL
                                  extend: 'excel',
                                  exportOptions: {
                                          rows: ':visible',
                                          columns: ':visible'
                                 },
Example JS code is as following:   
    var buttonCommon = {
        exportOptions: {
            format: {
                body: function(data,  row, column) {
               
                            data = data.replace(/<br\s*\/?>/ig, "\r\n");//should be with wrapped text
                            return data;
                },
                header: function(data, column, row) {
                            data = data.replace(/<br\s*\/?>/ig, "\r\n");//should be with wrapped text
                            return data;
                }
              }
            }
    };
    $.extend(true, $.fn.dataTable.defaults, {
              "lengthChange": false,
              "pageLength": 100,
              "orderClasses": false,
              "stripeClasses": [],
               "destroy": true,
              dom: 'Bfrtip',
              buttons: [
                    $.extend(true, {}, buttonCommon, {
                      extend: 'excel',
                      exportOptions: {
                             rows: ':visible',    
                          columns: ':visible'
                     },

                      customize: function(xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                      //All cell wrapped text
                            $('row c', sheet).each( function () {
                                        $(this).attr( 's', '55' );
                            });       
          
              }
            })
          ]
      }
    );

DataTable FAQ - hide showing entries in summary

 In DataTable, you may see the summary line
"Showing 1 to 2 of 2 entries".
You may need to hide this line.

If your table id is 'defaultTable', then
this line id is defaultTable_info.

Jquery code to hide showing entries in DataTable

var tableId = 'defaultTable';
$('#'+tableId+"_info").hide();   

Powerful js library datatable to export table in pdf, excel, sorting, freezing column and table header fixed (stikcy)

Datable js library can be downloaded from
https://datatables.net/download/release

DataTables

DataTables is the core software of the DataTables project, and involves two primary files, the DataTables Javascript and CSS.

js:
https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js
css:
https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css

FixedHeader

The FixedHeader plug-in will freeze in place the header, footer and left and/or right most columns in a DataTable, ensuring that title information will remain always visible.
js:
https://cdn.datatables.net/fixedheader/3.1.2/js/dataTables.fixedHeader.min.js
css
https://cdn.datatables.net/fixedheader/3.1.2/css/fixedHeader.dataTables.min.css 

Example:

$('#myTable').DataTable( {

    fixedHeader: true
} );

FixedColumns

FixedColumns "freezes" in place the left most columns in a scrolling DataTable, to provide a guide to the end user (for example an index column).
js:
https://cdn.datatables.net/fixedcolumns/3.2.2/js/dataTables.fixedColumns.min.js 
css
https://cdn.datatables.net/fixedcolumns/3.2.2/css/fixedColumns.dataTables.min.css

Export pdf and excel

Example

$(document).ready(function() {

    $('#example').DataTable( {

        dom: 'Bfrtip',

        buttons: [

            'copy', 'csv', 'excel', 'pdf', 'print'

        ]

    } );

} );

In addition to the above code, the following Javascript library files are loaded for use in this example:

• //code.jquery.com/jquery-1.12.4.js
• https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js
• https://cdn.datatables.net/buttons/1.2.4/js/dataTables.buttons.min.js
• //cdn.datatables.net/buttons/1.2.4/js/buttons.flash.min.js
• //cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js
• //cdn.rawgit.com/bpampuch/pdfmake/0.1.24/build/pdfmake.min.js
• //cdn.rawgit.com/bpampuch/pdfmake/0.1.24/build/vfs_fonts.js
• //cdn.datatables.net/buttons/1.2.4/js/buttons.html5.min.js
• //cdn.datatables.net/buttons/1.2.4/js/buttons.print.min.js

 Reference:
https://datatables.net/extensions/buttons/examples/initialisation/export.html 

Sorting

https://datatables.net/examples/basic_init/table_sorting.html