Excel export with background color without affecting the formatting

FrancoisLemieux

Hello I have been struggling for hours searching over and over on how to export with conditional formatting, I've been looking a lot in the forums and apologize if its a dupplicate

My most efficient way is to use a class because we have a php loop that create the table withsome data stored in SQL with targets and with many different formats ( time, percent, integers etc..) so i can apply easily a class in the backend but i have 2 issues.

1 i want to keep the number format that was there i only want to change the background color

2 If someone alter the order, the indexing is being all screwed and excel format is being applied on the wrong cell.

var StatTable= $('#StatTable').DataTable({
dom: 'Blfrtip',
buttons: [   {
    extend: 'excelHtml5',
    title: 'Performance_Tracker',
    customize: function(xlsx) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var notmetlist = StatTable.cells('.NotMet').indexes().toArray();
    notmetlist.forEach(function(element){
    var c = element['column']+1;
    var r   = element['row']+3;
    $('row:nth-child(' + r + ') c:nth-child('+ c +')', sheet).attr( 's', '12' );
    });}}]});

is there a way to do this? I need to have a excel background color of red if a datatable cell has a class of not met.

thanks!

allan

Yup - Excel is a huge pain with this. In Open Spreadsheet a style is a combination of the background colour, border, font and formatting. Its not like CSS where you can just add another class to get appropriate styling.

So what you have to do is create a new style which is the combination that you want and then add use that new style.

This is where the built in styles are for Buttons. You could use that as a guide for what you want to do. You'll need to add a new one to that list in the customize array.

Its a pain yes. Buttons is not designed to be an abstraction layer for Excel though I'm afraid. You'd need to use another library such as SheetJS if you want complete control over the Excel output.

Allan

FrancoisLemieux

Hi Thank you for this complete answer, i think ill have to dig deeper into other plugins or adding this customized style, but ill need 1 for each type, i think i could manage this

1 more question, can we get the "line number" and not the row index of everyrowwith a class or reindex those rows from the current order?

i need to replace this line? ( or add before!)

var notmetlist = StatTable.cells('.NotMet').indexes().toArray();

because the reordering kills me and i can't remove this function to my users.

thanks!

allan

I'm afraid I don't understand. Could you illustrate with an example?

Allan

rf1234

Hi Francois,

I guess you have trouble finding your rows if the users keep reordering the rows and you don't know their position afterwards?!

If you have some kind of "line number" or some other attribute in the data you export that you can use to identify the right row then it shouldn't be too difficult.

This code is from my customize: function(xlsx)

//move text from column B to column A and empty columns B through F of rows with totals
var rows = $('row c', sheet);  //create array of all rows (0 - N)      
var copyPaste; 
var emptyCellCols = ['B', 'C', 'D', 'E', 'F'];
var twoDecPlacesBoldCols = ['G', 'H', 'I', 'J', 'L', 'M', 'O'];
var boldCols = ['A'];
for ( i=2; i < rows.length; i++ ) {             
    copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();
    if ( copyPaste == 'Summen per Laufzeitende' ||
         copyPaste == 'Totals'                     ) {
        $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste);                    
        for ( y=0; y < emptyCellCols.length; y++ ) {
            $('row:eq('+i+') c[r^='+emptyCellCols[y]+']', sheet).text('');
        }
        for ( y=0; y < twoDecPlacesBoldCols.length; y++ ) {
            $('row:eq('+i+') c[r^='+twoDecPlacesBoldCols[y]+']', sheet).attr( 's', twoDecPlacesBold );
        }
        for ( y=0; y < boldCols.length; y++ ) {
            $('row:eq('+i+') c[r^='+boldCols[y]+']', sheet).attr( 's', '2' );  //bold
        }
    }
}                 

As you can see I identify rows by their content in column B (e.g. "Summen per Laufzeitende" or "Totals"). You could do the same with your "line number".

In case you need a full example of a customize function including the creation of your own styles just let me know I can post the example here if you like.

FrancoisLemieux

Hi, this is a great idea to show a line number based on the index, doing so will give me an option to track it once transferred to Excel.

And yes, rf1234 if you can give me a clean example on how to make it red with a "percentage" value, it would be really appreciated! i've been unable to make it happen with my tries i don't know why

So Allan to explain a bit more my situation is that when i export to excel, the lines are being created by the current view order and not the index number (it is good because the user , so my row 3 in excel is not necessary my index 0, I thought i could use index + 3 but if the user modified the order it doesnt works and the format is being applied to the wrong cell. If i could force datatables to recreate the index number based on the visible row that would fix it all. so no mater how many times my columns were reordered, the first row would have an index of 0. second = 1 based on the visible data. OR if I could get the visible line number instead of the index it would help also.

thanks to both of you!

allan

You can control the export order using the order property of the export options (see the selector-modifier docs). You can select to have the export in the currently applied order, or the original data order.

Allan

FrancoisLemieux

ahh cool that is also a good idea. i think i have things to work with. many thanks!

rf1234

Hi François,

so this is my Excel export button definition including some styles:

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
    customize: function( xlsx ) {
// see built in styles here: https://datatables.net/reference/button/excelHtml5
// take a look at "buttons.html5.js", search for "xl/styles.xml"
//styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
//styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
//styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
//styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
//styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
//styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
//on the last line we have the 67 currently built in styles (0 - 66), see link above

        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;
        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"/>';
        var s2 = '<xf numFmtId="0" fontId="2" 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;
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;

        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);
    //set lenght of some columns: col number: length (excl. first column)
        var colsLength = ['01:12', '02:12', '03:16', '04:10', '05:12', '06:16',
                          '07:16', '08:16', '09:16', '10:16', '11:16', '12:16',
                          '13:16', '14:16'];
        for ( i=0; i < colsLength.length; i++ ) {
            $( cols [ parseInt( colsLength[i].substr(0,2) ) ] )
                    .attr('width', parseInt( colsLength[i].substr(3) ) );                
        }

//two decimal places columns          
        var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O'];            
        for ( i=0; i < twoDecPlacesCols.length; i++ ) {
            $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
        $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
//                $('row c', sheet).attr( 's', '25' ); //for all rows
        $('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

//move text from column B to column A and empty columns B through F of rows with totals
        var rows = $('row c', sheet);  //create array of all rows (0 - N)      
        var copyPaste; 
        var emptyCellCols = ['B', 'C', 'D', 'E', 'F'];
        var twoDecPlacesBoldCols = ['G', 'H', 'I', 'J', 'L', 'M', 'O'];
        var boldCols = ['A'];
        for ( i=2; i < rows.length; i++ ) {             
            copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();
            if ( copyPaste == 'Summen per Laufzeitende' ||
                 copyPaste == 'Totals'                     ) {
                $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste);                    
                for ( y=0; y < emptyCellCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+emptyCellCols[y]+']', sheet).text('');
                }
                for ( y=0; y < twoDecPlacesBoldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+twoDecPlacesBoldCols[y]+']', sheet).attr( 's', twoDecPlacesBold );
                }
                for ( y=0; y < boldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+boldCols[y]+']', sheet).attr( 's', '2' );  //bold
                }
            }
        }            
    },
    exportOptions: {
//            columns: ':visible',
        //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') {
                    if (data != null) {  
                        if ( column > 14 ) {
                            return '';  //get rid of the changed manually column
                        }
                        if (column === 3 || column === 4 || column > 5) {
                            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 (column == 4) {
                                if (data !== '') {
                                    data = data / 100;
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {
                        if ( column > 14 ) {
                            return '';  //get rid of the changed manually column
                        }
                    }
                }
                return data;
            }
        }
    }
};

I do a lot of reporting too. But I use PHP Spreadsheet for this; much easier.

Roland