Excel Export

rf1234

I think Excel is still important in so many ways. Over 50% of my users want to be able to easily export their data to Excel. And they want it in a way that they can work with the data.

If you are not living in America or the UK you know the issue: It is hardly ever possible to work with exported numeric data. Formatted European numeric data get mutilated by Excel in a terrible way. I used the features that Data Tables provides to get it done - somehow.

But then the next problem emerged: How to do it,
a) if the user wants only selected columns (selected with the colvis button), or
b) the user wants wysiwyg: only what is displayed on the screen (not including child-rows hidden by the "responsive" extension)
- and still get the export formatting done in order to prevent Excel from destroying part of the data.

This was hard to develop and my code for this is ugly, I think.

I would strongly encourage a better solution for this. If anyone is interested how I resolved this with my ugly solution I can share it.

allan

Presumably the ugly bit is in a customize callback to determine which columns need to get the formatting applied to?

This is where Buttons does its Excel special data type detection and formatting. At the moment there is no concept in there of comma decimal and thousands period. Is that basically the issue?

Allan

rf1234

No, it is more than that, Allan.

I assign classes to the column headers in my HTML to indicate whether or not they require special formatting. If the user has flexibility to choose columns she wants to export, this requires that I know their position in the exported columns array.

First I had to learn that
- all columns are passed into "exportOptions.columns",
- only the columns to be exported are passed into "exportOptions.format.body"
- in "customize" you have to deal with the columns to be exported only but you need to address them by Excel column letter, not by number.

In order to still know where my columns are that I need to format I had to use a couple of global variables.

Here they are:

//excel export columns with special formatting to make it dynamic
//column index of all columns: true or false
var xlsTwoDecPlacesColsNumbers = [];
var xlsFourDecPlacesColsNumbers = [];
var xlsRightAlignedColsNumbers = [];

var columnsVisibleMain = []; //column index of all columns: true or false

var exportColumns = []; //column index of the exported columns only: always true!
var exportColumnsTwoDecPlaces = []; //column index of the exported columns only: true if two Dec Places or false
var exportColumnsFourDecPlaces = []; //column index of the exported columns only: true if four Dec Places or false
var exportColumnsRightAligned = [];  //column index of the exported columns only: true if right aligned or false

Then I needed to capture the initial position of my columns that require special formatting relative to all columns of the data table:

ctrTable
    .on ('init', function () {     
        var cols = $( ctrTable.columns().header() );
        $.each(cols, function(key, value) {
            if ( $(this).hasClass('xlsTwoDecPlaces') ) {
                xlsTwoDecPlacesColsNumbers.push ( true );
            } else {
                xlsTwoDecPlacesColsNumbers.push ( false );
            }
            if ( $(this).hasClass('xlsFourDecPlaces') ) {
                xlsFourDecPlacesColsNumbers.push ( true );
            } else {
                xlsFourDecPlacesColsNumbers.push ( false );
            }
            if ( $(this).hasClass('xlsRightAligned') ) {
                xlsRightAlignedColsNumbers.push ( true );
            } else {
                xlsRightAlignedColsNumbers.push ( false );
            }
        })
    })

I also needed to capture which columns are shown in the main view of the table excluding the columns hidden by the responsive extension. I didn't find anything in the api to check the "responsive status" by column hence I took the array of the event handler.

ctrTable.
    .on( 'responsive-resize', function (e, dt, columns) {
        columnsVisibleMain = columns;
    })

To capture which columns are eventually exported and where my special columns are I build a function that is called from "exportOptions.columns"

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 = [];
    }
    if ( scope == "full" ) {
        if ( $(table.column(ix).header()).hasClass("noExport") ) {
            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;
        }
    }
    //only exportable columns and whether they have tow or four decimal places
    //or are right aligned
    if ( val ) {
        exportColumns.push( true );
        exportColumnsTwoDecPlaces.push( xlsTwoDecPlacesColsNumbers[ix] );
        exportColumnsFourDecPlaces.push( xlsFourDecPlacesColsNumbers[ix] );
        exportColumnsRightAligned.push( xlsRightAlignedColsNumbers[ix] );
    }
    return val;
}

rf1234

Based on all of this here are my three buttons:

$.fn.dataTable.ext.buttons.excelCtrMgmt = {
    extend: "excel",
    title:    function () { return lang === 'de' ? 'Vertragsübersicht' : 'Contract Overview' },
    filename: function () { return lang === 'de' ? 'Vertragsübersicht' : 'Contract Overview' },
    messageBottom: function() {
        return tableFooter + ctrTable.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;
//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>';         
        var s3 = '<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; //new styles

        var fourDecPlaces    = lastXfIndex + 1;
        var greyBoldCentered = lastXfIndex + 2;
        var greyBoldWrapText = lastXfIndex + 3;

        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 xlsRightAlignedCols = [];

        for ( i=0; i < exportColumns.length; i++ ) {
            if ( exportColumnsTwoDecPlaces[i] ) {
                xlsTwoDecPlacesCols.push( xlsLetters[ i ] );
            }
            if ( exportColumnsFourDecPlaces[i] ) {
                xlsFourDecPlacesCols.push( xlsLetters[ i ] );
            }
            if ( exportColumnsRightAligned[i] ) {
                xlsRightAlignedCols.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 );
        }
//aktenzeichen und vertragsnummer as right aligned text
        for ( i=0; i < xlsRightAlignedCols.length; i++ ) {
            $('row c[r^='+xlsRightAlignedCols[i]+']', sheet).attr( 's', '52' );
        }

        $('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(ctrTable, ix, "full");
        },
        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;                     
                }
                //replace ampersands and other special chars
                data = data.replace(/&gt;/g, '>')
                           .replace(/&lt;/g, '<')
                           .replace(/&amp;/g, '&')
                           .replace(/&quot;/g, '"')
                           .replace(/&#163;/g, '£')
                           .replace(/&#39;/g, '\'')
                           .replace(/&#10;/g, '\n');
                //replace html tags with one space
                data = data.replace(/<[^>]*>/g, ' ');
                //replace multiple spaces and tabs etc with one space
                return data.replace(/\s\s+/g, ' ');
            },
            header: function ( data, column ) {
                //replace html tags with one space
                return data.replace(/<[^>]*>/g, ' ');
            }
        }
    }
}

$.fn.dataTable.ext.buttons.excelCtrMgmtVisible = 
    $.extend( true, {}, $.fn.dataTable.ext.buttons.excelCtrMgmt, {
        text: lang === 'de' ? 'Nur ausgewählte Spalten exportieren' : 'Export selected columns only',
        exportOptions: {
            columns: function(ix, data, node) {  
                return xlsExportColumns(ctrTable, ix, "selected");
            }
        }
    });
    
$.fn.dataTable.ext.buttons.excelCtrMgmtNoChildRows = 
    $.extend( true, {}, $.fn.dataTable.ext.buttons.excelCtrMgmt, {
        text: lang === 'de' ? 'Nur Hauptansicht exportieren' : 'Export main view only',
        name: "excelCtrMgmtNoChildRows",
        exportOptions: {
            columns: function(ix, data, node) {   
                return xlsExportColumns(ctrTable, ix, "main");
            }
        }
    });

To make the Excel column letter matching as simple as possible I used another variable. I know there other ways to achieve this but I found this one the simplest and most intuitive one:

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' ];

rf1234

What makes this so difficult is that you can't use the same column number throughout the process. Just knowing that column 10 needs special formatting doesn't work if the user can select the columns to be exported.
Column 10 is column 10 in the first step, but it can be column 5 and column letter E in the following steps. Or, it might not be part of the exported columns at all.

allan

Oof! That's a lot of code to do what you need, although I do see why. If you want to pass through export configuration options, there just is no way to do that at the moment. Perhaps if we passed in an array of the column indexes (or an Api instance of the columns) that are used in the export into customize that would help?

I didn't find anything in the api to check the "responsive status" by column hence I took the array of the event handler.

column().responsiveHidden()

Allan

rf1234

Perhaps if we passed in an array of the column indexes (or an Api instance of the columns) that are used in the export into customize that would help?

Not much actually. What would really help if you could address a column by its id or class regardless of its current position after filtering the columns for export. And also by numeric position based on all table columns. That would require Data Tables to do the repositioning based on user selection internally.

That would mean that column 10 for the developer is always column 10 - even if it is column 5 in column letter E after filtering.

I use the code on "init" plus this code to do the trick, but that could be built in, I guess:
(From the function above)

if ( ix == 0 ) {
    exportColumns = [];
    exportColumnsTwoDecPlaces = [];
    exportColumnsFourDecPlaces = [];
    exportColumnsRightAligned = [];
}
....
//only exportable columns and whether they have tow or four decimal places
//or are right aligned
if ( val ) {
    exportColumns.push( true );
    exportColumnsTwoDecPlaces.push( xlsTwoDecPlacesColsNumbers[ix] );
    exportColumnsFourDecPlaces.push( xlsFourDecPlacesColsNumbers[ix] );
    exportColumnsRightAligned.push( xlsRightAlignedColsNumbers[ix] );
}

column().responsiveHidden()

Thanks Allan, I had overlooked that one!

Roland

allan

Hi Roland,

Yup, I see what you mean. The customize callback was meant to be for final tweaks to the document that is produced - i.e. everything is where it will be in the output (so no sparse arrays).

I need to have a bit of a think about this!

Allan

rf1234

Many people still want everything in Excel especially to share and fiddle around with large tables. And I am trying to avoid the overhead of doing this all on the server using PHPSpreadsheet etc.. I think any effort is well invested in this. I'd be willing to pay a license fee for an "Excel premium package", Allan.