how to remove spaces between text in columns in excel export.

deepak2309

how to remove spaces between text in columns in excel export. please find the attachments.

$(document).ready(function() { var buttonCommon = { exportOptions: { format: { body: function ( data, row, column, node ) { // Strip $ from salary column to make it numeric return column === 5 ? data.replace( /[$,]/g, ' ' ) : data; } } } }; $('#mytable').DataTable( { dom: 'Bfrtip', "bDestroy": true, buttons: [ $.extend( true, {}, buttonCommon, { extend: 'excelHtml5' } ) ]**** } ); } );

rf1234

Here is an example for a custom button that also uses exportOptions to format what is being exported. You can also use exportOptions in the table definition itself ... and hence get rid of your spaces or whatever you would like to do before exporting the data.

In this example I had to get rid of local number formatting and a couple of columns. I also had to change a percentage into a fraction etc. The customize piece makes sure that everything gets formatted according to the standards of the respective user's Excel installation.

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1',
    customize: function( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            //numeric columns except for rate get thousand separators
            $('row c[r^="F"]', sheet).attr( 's', '64' );
//                $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
//                $('row c[r^="G"]', sheet).attr( 's', '56' );  //% no dec. place
            $('row c[r^="I"]', sheet).attr( 's', '64' );
            $('row c[r^="J"]', sheet).attr( 's', '64' );
            $('row c[r^="K"]', sheet).attr( 's', '64' );
            $('row c[r^="L"]', sheet).attr( 's', '64' );
            //first row has grey backround
            $('row:first c', sheet).attr( 's', '7' );
        },
    exportOptions: {
        format: {
            body: function ( data, row, column, node ) {
                // Strip $ from salary column to make it numeric
                if (typeof data !== 'undefined') {
                    if (data !== null) {  
                        if ( column > 11 ) {
                            return '';  //get rid of the changed manually column
                        }
                        if (column === 5 || column === 6 || column > 7) {
                            if (lang == 'de') { //this time we use the English formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods with spaces
                                arr[0] = arr[0].replace( /[\.]/g, "" );
                                //join the pieces together with a period if not empty
                                if (arr[0] > ''  || arr[1] > '') {
                                    data = arr[0] + '.' + arr[1];
                                } else {
                                    return '';
                                }
                            } else {
                                data = data.replace( /[\,]/g, "" );
                            }
                            //result a number still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            data = data.replace( /[^\d.-]/g, "" ); 
                            //rate must be adjusted to fraction to be able to calculate
                            if (column == 6) {
                                if (data !== '') {
                                    data = data / 100;
//                                        data = data.toString();
//                                        data = data + '%';
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                if (typeof data !== 'undefined') {
                    if (data !== null) {
                        if ( column > 11 ) {
                            return '';  //get rid of the changed manually column
                        }
                    }
                }
                return data;
            }
        }
    }
};

https://datatables.net/extensions/buttons/examples/print/columns

allan

how to remove spaces between text in columns

What spaces? Can you link to a test case showing the issue if @rf1234's reply doesn't help to resolve it.

Allan