Export Datatable Number with Comma Decimal to Excel

Software Used:

Datatables 1.10.20

Datatable Display:

Problem:

When i export to excel then decimal format in QTY column is wrong like image below:

Question:

How to fix this … ?

Answer:

We can use this code below:

$('#your_tbody_id').DataTable({
  buttons: [
    {
	extend: 'excel',
	exportOptions: {
           columns: ':visible',
           format: {
                    body: function(data, row, column, node) {
			if(column===1)
			   return data.replace(',', '.');
			else return data
						
                    }
	    }
	 }
     } 
  ]
})

Explanation:

  • if(column===1) , this code check if column index is 1
  • data.replace(‘,’, ‘.’) this code replace comma with dot and Mirosoft Excel will change dot to comma

Thanks for reading

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *