Question regarding formatting of datatable columns before exporting to xls file

anujeet

Hi Team,

We have implemented data-tables in our application. For each list we have separate js file to initialize data-tables. Before exporting the data-tables we are doing some formatting for currency columns so that $ and euro sign should not export in exported xls. Below is our code which we used for a single list:

var buttons = new $.fn.dataTable.Buttons( table, {
buttons: [
{
extend: 'excel',
exportOptions: {
columns: ':visible',
charSet: "utf-8",
format: {
body: function ( data, row, column, node ) {

                                        // Strip $ from salary column to make it numeric
                                       if(column == 10 || column == 13 || column == 14 || column == 15 || column == 16 || column == 19) {

                                         var d = data.replace( /[€]/g, '' );

                                          d = d.replace( /[.]/g, '' );

                                         d = d.replace( /[,]/g, '.' );

                                         return d;
                                     } 
                                       return data; 
                                  }
                            }
        }           
    ]
} );

As you can see in above code I am removing "&euro" sign from currency columns before exporting to XLS. Below are my questions:
1) Right now I have fixed the index of columns like(10,13,14) etc, Is there any way so that I can apply a class to currency columns and in above code I can check if column has class then euro should be replace ?. I mean instead of doing column ==10, Shall I use something like column.class == 'className'.

2) Right now I have added above code in a js file which is being used for a single list. We have about 20 lists and 20 js files. Is there any way so that I can use above code as a common code for the all lists. So that I can add above code at one place and will be reflect at everywhere ?

Looking forward for your reply

Thanks

bindrid

so your data comes from the database with these symbols already attached?

anujeet

We have not saved the symbols in database. While showing on list we are appending the symbols and while exporting we are removing the symbols.

anujeet

Any solution to make the code generic?

Thanks

anujeet

Hi Team,

Any update on this?.

Thanks

kthorngren

Maybe you can try something like this:

                                        // Strip $ from salary column to make it numeric
                                       if( (node.className === '<assigned class>') ) {
 
                                         var d = data.replace( /[&euro;]/g, '' );
 
                                          d = d.replace( /[.]/g, '' );
 
                                         d = d.replace( /[,]/g, '.' );
 
                                         return d;
                                     }
                                       return data;
                                  }
                            }
        }          
    ]
} );

You would need to assign each cell with the &euro to that class.

Kevin