More flexibel Excel export

rf1234

This is to share my latest work on exporting data to Excel including formatting and dynamic column ordering and selection. While I already had column formatting (numbers, percentages, custom column width) this was static and didn't work with column reordering and user induced column visibility changes. In addition: When making changes to the underlying Data Tables I always forgot to adjust the hard-coded export formatting which led to errors etc..

My first change was to have a split button for the Excel export:
- Full export
- Export only selected columns (set with colVis - Button)
- Export only main view (based on responsive-extension results)

I replaced the hard-coded column numbers for formatting with HTML-classes and data-* attributes. This makes sure that nothing breaks when new columns are inserted or columns are moved around.

I use the following HTML classes to indicate that special formatting needs to be done for the Excel export:
- xlsTwoDecPlaces (numbers with two decimal places, e.g. $ amounts)
- xlsFourDecPlaces (numbers with four decimal places, e.g. percentages; these also require conversion from percentage to fraction: 50% needs to be passed to Excel as 0.5, in case you want a formatted field and not a "dumb" string)
- xlsRightAligned (fields that should be right aligned in Excel not the usual left alignment)
- noExport (columns that should never be exported because they are simply irrelevant, e.g. columns with document links)
- noExportFull (columns that should not be exported only when a full export is triggered)
- data-length attribute (Excel column width in case a special column width is required)

Since I use colReorder and stateSaving (both are optional and can be set by the user) I need to capture the "initial" columns based on the HTML BEFORE state loading and colReorder make any changes.

This is how I do this. The arrays are global variables to have them available later on.

table
    .on( 'preInit', function (e, settings) {
        //save the original formatting values BEFORE applying saved states and 
        //before initialization on "preInit"
        xlsTwoDecPlacesColsNumbers = [];
        xlsFourDecPlacesColsNumbers = [];
        xlsRightAlignedColsNumbers = [];
        xlsLengthSpecifiedColsNumbers = [];
        xlsLengthIfSpecifiedColsNumbers = []; 
        //all columns including class never etc. 
        table.columns().every( function() {
            var header = this.header();
            if ( $(header).hasClass('xlsTwoDecPlaces') ) {
                xlsTwoDecPlacesColsNumbers.push ( true );
            } else {
                xlsTwoDecPlacesColsNumbers.push ( false );
            }
            if ( $(header).hasClass('xlsFourDecPlaces') ) {
                xlsFourDecPlacesColsNumbers.push ( true );
            } else {
                xlsFourDecPlacesColsNumbers.push ( false );
            }
            if ( $(header).hasClass('xlsRightAligned') ) {
                xlsRightAlignedColsNumbers.push ( true );
            } else {
                xlsRightAlignedColsNumbers.push ( false );
            }
            var length = $(header).attr('data-length');
            if ( typeof length !== "undefined" && length !== false ) {
                xlsLengthSpecifiedColsNumbers.push ( true );
                xlsLengthIfSpecifiedColsNumbers.push ( length );

            } else {
                xlsLengthSpecifiedColsNumbers.push ( false );
                xlsLengthIfSpecifiedColsNumbers.push ( "0" );
            }
        });
    })

I also need to capture which columns are in the main view currently. I capture that in a global variable as well.

table
    .on( 'responsive-resize', function (e, dt, columns) {
        if ( ! $.isArray(columns) ) {
            return;
        }
        if ( columns.length <= 0 ) {
            return;
        }
        //this is the column selection for the excel main view export
        columnsVisibleMain = columns;
    });

The split button:

buttons: [
    {extend: "excelCashFlow", split: ["excelCashFlowVisible", "excelCashFlowNoChildRows"] },
    {extend: "colvis", columns: ':not(.never)' }   
],  

rf1234

The underlying buttons for the split button (deleted quite a bit of code, but kept the most relevant parts. and yes, I know this could be coded in a more concise way ... but I don't really care if it works ):

/custom button for cashflow excel generation
    $.fn.dataTable.ext.buttons.excelCashFlow = {
        extend: 'excel',
        title:    function () { return cfTitle; },
        filename: function () { return cfFilename; },
        messageBottom: function() {
            return tableFooter + cashFlowTable.rows( {search: "applied"} ).count();
        },
        customize: function( xlsx ) {
            var sSh = xlsx.xl['styles.xml'];
            var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
            var lastFontIndex = $('fonts font', sSh).length - 1; 
            var f1 = //bold and underlined font
            '<font>'+
                    '<sz val="11" />'+
                    '<name val="Calibri" />'+
                    '<b />'+'<u />'+
            '</font>'
            
            var i; var y; var z;
    //n1, n2 ... are number formats; s1, s2, ... are styles
            var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
            var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
            //define the style with the new font (number passed in as a variable)
            var s2 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
            '<alignment horizontal="center"/></xf>';         
    //s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
    //just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
            var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
            var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
            '<alignment horizontal="center" wrapText="1"/></xf>'
            sSh.childNodes[0].childNodes[0].innerHTML += n1;  //new number format
            sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
            sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4; //new styles
            
            var fourDecPlaces    = lastXfIndex + 1;
            var greyBoldCentered = lastXfIndex + 2;
            var twoDecPlacesBold = lastXfIndex + 3;
            var greyBoldWrapText = lastXfIndex + 4;
            
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
        //create array of all columns (0 - N)
            var cols = $('col', sheet);
            
            //convert the numbers of the exported columns to Excel column letters
            var xlsTwoDecPlacesCols = [];
            var xlsFourDecPlacesCols = [];
            var xlsLengthSpecifiedCols = [];
            var xlsLengthIfSpecifiedCols = [];
            var xlsExportColumnsCols = []; //all Export columns - we want them with letters
            
            for ( i=0; i < exportColumns.length; i++ ) {
                if ( exportColumnsTwoDecPlaces[i] ) {
                    xlsTwoDecPlacesCols.push( xlsLetters[ i ] );
                }
                if ( exportColumnsFourDecPlaces[i] ) {
                    xlsFourDecPlacesCols.push( xlsLetters[ i ] );
                }
                //no letters we stay numeric here
                if ( exportColumnsLengthSpecified[i] ) {
                    xlsLengthSpecifiedCols.push( i );
                }
                //we take the content which is the Excel column length
                if ( exportColumnsLengthIfSpecified[i] > 0 ) {
                    xlsLengthIfSpecifiedCols.push( exportColumnsLengthIfSpecified[i] );
                }
                xlsExportColumnsCols.push( xlsLetters[ i ] );
            };
            
            //two decimal places columns                      
            for ( i=0; i < xlsTwoDecPlacesCols.length; i++ ) {
                $('row c[r^='+xlsTwoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
            }
            //four decimal places columns                      
            for ( i=0; i < xlsFourDecPlacesCols.length; i++ ) {
                $('row c[r^='+xlsFourDecPlacesCols[i]+']', sheet).attr( 's', fourDecPlaces );
            }
            //set lenght of some columns: col number: length (excl. first column)
            for ( i=0; i < xlsLengthSpecifiedCols.length; i++ ) {
                $( cols [ xlsLengthSpecifiedCols[i] ] ).attr('width', parseInt( xlsLengthIfSpecifiedCols[i] ) );  
            }
            $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
            $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
            $('row:last c', sheet).attr( 's', '2' );  //bold
        },
        exportOptions: {
            columns: function(ix, data, node) {
                return xlsExportColumns(cashFlowTable, ix, "full");
            },
            //passing "undefined" also works but only with quotes
            modifier: { selected: null }, //make sure all records show up
            format: {
                body: function ( data, row, column, node ) {
                    if (typeof data === 'undefined') {
                        return;
                    }
                    if (data == null) {
                        return data;
                    }
                    if ( exportColumnsTwoDecPlaces[column] || exportColumnsFourDecPlaces[column] ) {
                        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].toString().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.toString().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.toString().replace( /[^\d.-]/g, "" ); 
                        //percent must be adjusted to fraction to work ok
                        if ( exportColumnsFourDecPlaces[column] ) {
                            if (data !== '') {
                                data = data / 100;
                            }
                        }   
                    }
                    return data;                
               }
            }
        }
    };
    
    $.fn.dataTable.ext.buttons.excelCashFlowVisible = 
        $.extend( true, {}, $.fn.dataTable.ext.buttons.excelCashFlow, {
            text: lang === 'de' ? 'Nur ausgewählte Spalten exportieren' : 'Export selected columns only',
            exportOptions: {
                columns: function(ix, data, node) {  
                    return xlsExportColumns(cashFlowTable, ix, "selected");
                }
            }
        });

    $.fn.dataTable.ext.buttons.excelCashFlowNoChildRows = 
        $.extend( true, {}, $.fn.dataTable.ext.buttons.excelCashFlow, {
            text: lang === 'de' ? 'Nur Hauptansicht exportieren' : 'Export main view only',
            exportOptions: {
                columns: function(ix, data, node) {   
                    return xlsExportColumns(cashFlowTable, ix, "main");
                }
            }
        });

rf1234

I do the conversion from numbers to Excel letters in a very simple way using the array below avoiding any kind of calculation. I am not very good at math ...

var xlsLetters = 
    [ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
      'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 
      'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 
      'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV',
      'AW', 'AX', 'AY', 'AZ', 
      'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 
      'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV',
      'BW', 'BX', 'BY', 'BZ' ];

The "exportOptions" method is executed prior to the "customize" method. Depending on the button one of these lines is being executed:

return xlsExportColumns(cashFlowTable, ix, "full");
return xlsExportColumns(cashFlowTable, ix, "selected");
return xlsExportColumns(cashFlowTable, ix, "main");

This function creates arrays (globals) that are used in "exportOptions" and "customize" as well. The challenge is to apply the right formatting options even for partly or reordered views. Hence it is important to know the original position of a column. A transposition needs to be done. Another challenge is that "exportOptions" is called for each table column, while "customize" is only called once. Confusing.

So this is the function:

function xlsExportColumns(table, ix, scope) {
// exportColumns = []; //column index of the exported columns only: always true!
// exportColumnsTwoDecPlaces = []; //column index of the exported columns only: true if two Dec Places or false
// exportColumnsFourDecPlaces = []; //column index of the exported columns only: true if four Dec Places or false
// exportColumnsRightAligned = [];  //column index of the exported columns only: true if right aligned or false
    var val;
    if ( ix == 0 ) {
        exportColumns = [];
        exportColumnsTwoDecPlaces = [];
        exportColumnsFourDecPlaces = [];
        exportColumnsRightAligned = [];
        exportColumnsLengthSpecified = []; 
        exportColumnsLengthIfSpecified = []; //column length or 0, if not specified (i.e. above value is false)
    }
    
    if ( $(table.column(ix).header()).hasClass("never") ) {
        val = false;
    } else if ( scope == "full" ) {
        if ( $(table.column(ix).header()).hasClass("noExport")      || 
             $(table.column(ix).header()).hasClass("noExportFull")      ) {
            val = false;
        } else {
            val = true;
        }
    } else if ( scope == "selected" ) {    
        if ( $(table.column(ix).header()).hasClass("noExport") ) {
            val = false;
        } else if  ( ! table.column(ix).visible() ) { //these are the "selected" columns
            val = false;
        } else {
            val = true;
        }
    } else if ( scope == "main" ) {
        if ( $(table.column(ix).header()).hasClass("noExport") ) {
            val = false;
        } else if  ( ! columnsVisibleMain[ix] ) { //only the columns of the main view
            val = false;
        } else {
            val = true;
        }
    }
    //if vat is turned off those cols don't get exported in any case!
    if ( val ) {
        if ( $(table.column(ix).header()).hasClass("VAT_hidden") ) {
            val = false;
        }
    }
    //only exportable columns and whether they have tow or four decimal places
    //or are right aligned or have the length specified
    if ( val ) {
        //use the original column indexes that were saved on "preInit"
        var t = "toOriginal";
        exportColumns.push( true );
        exportColumnsTwoDecPlaces.push( xlsTwoDecPlacesColsNumbers[table.colReorder.transpose(ix, t)] );
        exportColumnsFourDecPlaces.push( xlsFourDecPlacesColsNumbers[table.colReorder.transpose(ix, t)] );
        exportColumnsRightAligned.push( xlsRightAlignedColsNumbers[table.colReorder.transpose(ix, t)] );
        exportColumnsLengthSpecified.push( xlsLengthSpecifiedColsNumbers[table.colReorder.transpose(ix, t)] );
        exportColumnsLengthIfSpecified.push( xlsLengthIfSpecifiedColsNumbers[table.colReorder.transpose(ix, t)] );
    }
    return val;
}

This is what the split button looks like:

Hope that helps a little in case you have similar challenges! In case you export everything as a string this is not required of course! But my users want to be able to use the Excel for immediate calculation and don't want to reformat everything manually. I wish my bank was able to do that too!

Roland

allan

How good is that! Amazing - thank you for sharing this with us.

The Excel export, and export in general, is something I'm planning on working on intensively, probably in the second part of the year. It is way to hard to customise it at the moment.

I'm sure others will find this useful!

Allan