Thousands Separator in Datatable wrong display in excel

Software Used:

Datatables 1.10.20

Datatable Display:

Problem:

When it exported to Microsoft Excel then JUMLAH column displayed with wrong format like image below:

Question:

How to fix it … ?

Answer:

We can make this code below:

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

Explanation:

  • if(column===2) , this code check if column index is 2
  • data.replace(/./g, ‘,’) this code is replacing dot with comma and Microsoft Excel will change comma to dot

Thanks for reading

You may also like...

Leave a Reply

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