convert number to string when using datatables export to excel

Question

I have some problems when I try to export my data as excel using the datatables button.

When my data type is a number and starts from 0, this value will disappear. and also they automatically provide (.) dot as a delimiter. can someone help me to fix this??

my data

when export as excel

I want my data like this. without (point) and number 0 is not lost when the number 0 is at the beginning

this my script

$('#submitdata').on('click',function(){
    var vbasedon = $("#selbased").val();
    var mulai = $("#start_date").val();
    var selesai = $("#end_date").val();
    var vtipe = '';

    if(vbasedon=='apartemen' || vbasedon=='rusunawa'){
        var vtipe=$(".tipedt").val();
    } else {
        vtipe='';
    }




    $.ajax({
        url: "<?= base_url('index.php/')?>",
        data: {vbasedon:vbasedon,mulai:mulai,selesai:selesai,vtipe:vtipe},
        cache: false,
        type: "POST",
        dataType:"JSON",
        success: function(msg){

            initEffortStandardTable(msg);
        }
    });




});

function initEffortStandardTable(dataObject) {

    var effortStandardTable = $('.laporan').DataTable();
    var columns = [];
    Object.keys(dataObject[0]).forEach(column=> {
        if (column === 'ctotalPerYear'
            || column === 'ftotalPerYear'
            || column === 'CPUETotalPerYear') return;
            columns.push({
                data: column,
                title : column.toString().toUpperCase().replace('_', '  '),
                footer: column
            });
    });
    effortStandardTable.destroy();
    $('#laporan').empty();
    var html = `<table class="table table-striped table-bordered table-hover laporan" ></table>`;
    $('#laporan').append(html);
    effortStandardTable = $('.laporan').DataTable({
        data: dataObject,
        columns : columns,
        "bDestroy": true,
        responsive: true,
        dom: '<"html5buttons"B>lTfgitp',
        buttons: [
        { extend: 'copy'},
        {extend: 'excel', title: 'Laporan'},
        {

            extend: 'pdfHtml5',
            orientation: 'landscape',
            pageSize: 'A4'
        }]
    });
}

what about exporting the values as excels "string-function" ="String" like '<td>="'+msg[i].nama+'"</td>'? — user4602302
– user4602302, Commented Aug 1, 2019 at 17:54
i'm sorry, czI forgot to delete that part. I just pass this initEffortStandardTable(msg); with "msg" is an array that I got from the database. — AdityaDees
– AdityaDees, Commented Aug 1, 2019 at 18:52

AdityaDees · Accepted Answer · 2019-08-03 13:04:08Z

2

I have found the answer to my question. I just need to manipulate data by adding unicode '\u200C'. so my code will be like this

columns.push({
data: column,
title : column.toString().toUpperCase().replace('_', '  '),
footer: column,
render: 
    data=>{data=> column == 'NIM' ? '\u200C'+data : data
});

and this is my results.

answered Aug 3, 2019 at 13:04

AdityaDees

1,06719 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dev_Anoop · Accepted Answer · 2020-10-02 10:55:03Z

1

A number that contains more than 15 digits in Excel, it changes any digits past the fifteenth place to zeros. You can cast the column values to string on export.

//Use customizeData
exportOptions: {
                   //--
               },
customizeData: function (data) {
              var ind = data.header.indexOf("ColumnName"); // This code is to find the column name's index which you want to cast.
              for (var i = 0; i < data.body.length; i++) {
                  data.body[i][ind] = '\u200C' + data.body[i][ind]; //will cast the number to string.
                }
             }

Error_Image below:

https://i.sstatic.net/A39hf.png

edited Oct 2, 2020 at 10:55

answered Oct 2, 2020 at 10:47

Dev_Anoop

212 bronze badges

Collectives™ on Stack Overflow

convert number to string when using datatables export to excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related