How do we format Date string and negative currency values when exporting to excel ?

maheshlamdade

Hi,
I have a table with columns containng dates(mm/dd/yyyy) and currency($). However, when exporting the table to excel, the date column seems to be formatted as General rather than Date in the output Excel. The same issue being with the currency field as well.

I have tried formatting the date, with the exportOptions method, one of the code i tried being :

exportOptions : {
  format: {
  body: function ( data, row, column) {
    if(column == 3){
       var dt = data;
       // match data with the date regex
       if (dt !== null && dt.match('^[0-9]{2}/[0-9]{2}/[0-9]{4}$'))
       {
           formattedDate = new Date(dt);
           return formattedDate.getDate() + '/' + (formattedDate.getMonth() + 1) + '/' +  formattedDate.getFullYear();
        }else{
            return dt;
        }
     }
  }
 }
}

Apart from this, i have tried more things by search for formatting the date string after outputting data to excel, but didnt succeed.

For formatting the currency, i followed the links :

https://datatables.net/forums/discussion/46207/export-to-excel-and-format-a-numeric-column-as-currency

https://stackoverflow.com/questions/47675528/datatables-export-to-excel-and-format-a-numeric-column-as-currency/48571827#48571827

and succeeded in having the formatted string from 2,000$ in datatable to a formatted currency as $2,000 in the exported excel. But, this isnt the case with the negative currency(-$6,000) . For negative currency, the exported excel shows the format as General rather than Currency. The logic works fine except this case. Am unable to figure out the solution, however have tried many things for getting it done.

It would be a great help if anyone could help me with formatting the date and negative currency while exporting excel file.

colin

Hi @maheshlamdade ,

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

maheshlamdade

hi @colin , thanks for the comment.

Heres a example link :

http://live.datatables.net/nexoqoku/1/

This has a table with the format matching with the my table data for both date and currency fields. Also, i have also put my trial code for formatting date field for reference. You can see that the negative currency isnt formatted, whereas the positive one works quite fine. And also, the date column isnt formatted as date when we export the excel.

colin

Hi @maheshlamdade ,

Thanks for the fiddle, that helped.

For the currency, it looks like you'll need to change the code. See the code here. You could add a \-? just after the dollar sign and see if that does the job.

For the dates, I don't think that can be changed, the type I believe isn't passed through.

Cheers,

Colin

maheshlamdade

Hi @colin ,
I have already tried out the \-? and it does do the job for us, except for the negative currency.

And thanks for the update regarding dates.

Regards,
Mahesh