0

i am trying to export data from a datatable to an excel file. But everytime i use the excel button it changes the format of my numbers. For example a 0,023 is copied to excel as a 23. Is there a way to use the excel export button so that excel isnt changing the format? The other things is that i use the datatable on different data from a DB. So the column isnt always the same which has to be formated. Even the User can select which column should be exported. So the Format sould be used.

So possible Datatable could look like:

A B C
abc 1 1,2
A B C
abc 1,2 dcf

Thx in advance.

<script type="text/javascript" language="javascript">
    $(document).ready(function() {  
        
        var table = $('#tbl').DataTable( {
   dom:  "lfBptrip",
   select: {
            style: 'multi'
        }
          ,
        columnDefs: [
            {
                targets: 0,
                className: 'noVis'
            }
        ],        
        language: {
            searchBuilder: {
                button: 'Filter',
            }
        },
        buttons:[
            'searchBuilder',    
            {
              extend: 'colvis',
              columns: ':gt(0)',
              collectionLayout: 'fixed columns',
              text: 'Spaltenauswahl',
              postfixButtons: [{
                    extend: 'colvisRestore',
                    text: 'Show All',
                  }, {
                    extend: 'colvisGroup',
                    text: 'Hide All',
                    hide: ':visible'
                  },
                  
                  ]
           },
            {
            extend: 'copy',
            text: 'Zwischenablage',
                exportOptions: {
                  columns: ':visible'
                }
        }, 
        {
            extend: 'excelHtml5',
            text: 'Excel',
            exportOptions: {
                  columns: ':visible'
                }
        },
        {
            extend: 'pdf',
            pageSize: 'LEGAL',
            orientation: 'landscape',
            text: 'PDF',
                exportOptions: {
                  columns: ':visible'
                }
        }       
        ],       
        colReorder: true,       
        scrollX: true,
    });
}); 
</script>
kast1180
  • 15
  • 5
  • 1
    It's not an exact duplicate of your question, but this is very close, I think: [JQuery Datatable Excel export number format](https://stackoverflow.com/q/67331618/12567365). Specifically, you can use the `exportOptions.format.body` function to transform the format of the source data (and, separately, use `customize` to add an Excel custom number format, if you need one). – andrewJames Oct 04 '22 at 17:05
  • Thx for the link. It is working. The only problem is that i dont know in which column the format will be used. in my case it total depends on which data i use in the datatable. – kast1180 Oct 05 '22 at 12:17
  • 1
    You would need to [edit] your question and add this to the question. You can show us some sample table data, also. It might be possible to inspect the data to determine which columns need this format to be applied. – andrewJames Oct 05 '22 at 12:32

0 Answers0