How to Customize exported excel's cell background color in Datatables

Customize exported excel's cell background color in Datatables

In this post i will walk you through how we can customize exported excel's cell background color in datatables. For this we need make changes in the buttons.html5.js file. you may refer below links for more info. excelHtml5 and how can i change the background color

Follow below steps and make changes in buttons.html5.js file

1. Create your own fills.

 '<fills count="11">'+

'<fill>'+

'<patternFill patternType="none" />'+

'</fill>'+

'<fill>'+ // Excel appears to use this as a dotted background regardless of values but

'<patternFill patternType="none" />'+ // to be valid to the schema, use a patternFill

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="FFD9D9D9" />'+

'<bgColor indexed="64" />'+

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="FFD99795" />'+

'<bgColor indexed="64" />'+

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="ffc6efce" />'+

'<bgColor indexed="64" />'+

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="ffc6cfef" />'+

'<bgColor indexed="64" />'+

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="ffc6efcd" />'+

'<bgColor indexed="64" />'+   //6

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="ffffeb9c" />'+

'<bgColor indexed="64" />'+   //7

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="fffec7cd" />'+

'<bgColor indexed="64" />'+   //8

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="ffe3e0cd" />'+

'<bgColor indexed="64" />'+  //9

'</patternFill>'+

'</fill>'+

'<fill>'+

'<patternFill patternType="solid">'+

'<fgColor rgb="ff92d150" />'+

'<bgColor indexed="64" />'+  //10

'</patternFill>'+

'</fill>'+

'</fills>'+



2. You need to insert a new xf tag into the cellXfs array that uses both percentage formatting and your background colour. Then set your cell to use that new style (i.e. the xf tag) -


'<xf numFmtId="0" fontId="0" fillId="6" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+
    '<xf numFmtId="0" fontId="0" fillId="7" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+
    '<xf numFmtId="0" fontId="0" fillId="8" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+
    '<xf numFmtId="0" fontId="0" fillId="9" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+
    '<xf numFmtId="0" fontId="0" fillId="10" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+



3. Set your color code here -


customize: function( xlsx ) {
                                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                                $('row c[r^="G"]', sheet).each( function () {     // for column G  
                                $('row c', sheet).each( function () {        // will access each cell                 
                                    var toto=$(this);
                                    if ( $(this).text() === "A") {
                                        $(this).attr( 's', '40' );                                 
                                    }
                                    else if ( ($(this).text() === "B") {
                                        $(this).attr( 's', '45' );                                 
                                    }                                   
                                    else if ( $(this).text() === "C") {
                                        $(this).attr( 's', '35' );                                 
                                    }
                                });
                            },

4. Output -






Comments


  1. $(this).attr( 's', '40' );
    $(this).attr( 's', '45' );
    $(this).attr( 's', '35' );
    40,45,35 are pre-defined how to define the newly added colors

    ReplyDelete

Post a Comment

Popular posts from this blog

Populate dropdown using jquery ajax in SharePoint

Customizing SharePoint list and implementing Quick search using jQuery