how to handle blank values in export excel ?

prasadhari009

prasadhari009

When I export data table to excel ,the blank values in datatable comes in excel as &nbsp so I Actually I dont want these ..

Please help me........

Thanks
Hari

kthorngren

One option might be to use Orthogonal data and convert the &nbsp string to a space. See this example of using Orthogonal data with the export buttons.

Kevin

prasadhari009

this is my code but still   tags are coming in excel

 exportOptions: {       
                                orthogonal: 'export',
                                format: {                                    
                                    body: function (data, row, column, node, type) {    
                                        return type === 'export' ? data.replace(/[$,]/g, '') : data;                                                                 
                                    },
                                    modifier: {
                                        // DataTables core
                                        order: 'index',  // 'current', 'applied', 'index',  'original'
                                        page: 'all',      // 'all',     'current'
                                        search: 'none'     // 'none',    'applied', 'removed'
                                    },
                                    columns: ':visible'
                                },

                                action: function (e, dt, node, config) {

                                    config.filename = "Unit Delinquent Report";
                                    $.fn.DataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
                                }
                            }
                        }),   

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

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

prasadhari009

this is my code

exportOptions: {
orthogonal: 'export',
format: {
body: function (data, row, column, node, type) {
return type === 'export' ? data.replace(/[$,]/g, '') : data;
},
modifier: {
// DataTables core
order: 'index', // 'current', 'applied', 'index', 'original'
page: 'all', // 'all', 'current'
search: 'none' // 'none', 'applied', 'removed'
},
columns: ':visible'
},

                           action: function (e, dt, node, config) {

                               config.filename = "Unit Delinquent Report";
                               $.fn.DataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);
                           }
                       }
                   }),  

colin

To progress this, please post a test case as requested, that will allow us to debug the code,

Colin

kthorngren

The regex ( data.replace(/[$,]/g, '' ) you are using is not correct to replace  . You will want something like data.replace(/ /gi,' ') instead.

Kevin