how can i change the background color for a cell "excel export"

tarik.handaz

i can't find a way to change the background of a cell i used the customize option for the export but they only give u a specific style number from 0 to 66 this is the exemple i saw https://datatables.net/reference/button/excelHtml5 but i can't change a style by adding a specific background or create my own style this is the code i use it work fine i only need to add a background to a cell

"buttons": ['pageLength',
                        {
                            extend: 'excel',
                            text: 'Export excel',
                            footer:true,
                            customize: function( xlsx ) {
                                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                                $('row c[r^="G"]', sheet).each( function () {
                                    if ( $(this).text()>= 1) {
                                        $(this).attr( 's', '56' );                  
                                    }
                                });
                            },
                            exportOptions: {                                
                                 columns: ':visible',                               
                            }
                        }
                    ],

allan

Just to confirm, the colours in the default styles don't suit your needs?

There are two options:

1. Hijack the fill for one of the current styles - the fills are defined here. Use jQuery to modify the background color.
2. Inject your own fill and then your own style.

Custom styling in OpenSpreadsheet is not easy...!

Allan

tarik.handaz

this is the pic of my datatable i need to keep the colors after the export and also the percentage so the style number 56 fit my need first i was using buttons.html5.min.js now i use the one u give and i still can't find how to hijack the style lol sorry but if u can help i will be thankful

tarik.handaz

thks i managed to change the fill as u show me i still have to keep the percentage because when i apply other style with the colors i need i lose the percentage it show the numbers as standard for exemple 106% show 1.06 this is my code now

"buttons": ['pageLength',
                        {
                            extend: 'excel',
                            text: 'Export excel',
                            customize: function( xlsx ) {
                                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                                $('row c[r^="G"]', sheet).each( function () {
                               //   alert( $(this).text())
                                    var toto=$(this);
                                    if ( $(this).text()*100>= 103) {
                                        $(this).attr( 's', '40' );                                  
                                    }
                                    else if ( ($(this).text()*100>=100) && ($(this).text()*100< 103)) {
                                        $(this).attr( 's', '45' );                                  
                                    }
                                    else if ( ($(this).text()*100>=97) && ($(this).text()*100< 100)) {
                                        $(this).attr( 's', '30' );                                  
                                    }
                                    else if ( $(this).text()*100<97) {
                                        $(this).attr( 's', '35' );                                  
                                    }
                                });
                            },
                            exportOptions: {                                
                                 columns: ':visible',                               
                            }
                        }
                    ],

allan

That's correct. You'd 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).

As I say, its a real pain!

Allan

tarik.handaz

thanks for ur help i did it because of u, yeap i changed the the numFmtId of the xf tag to 9 for the styles i used now i get what i want i also managed to add new colors

Rode

This works very nicely, but what if I wanted to change the background color of the entire row if the column equaled something?

SOLVED:
I overcomplicated this at first, so I found an easy solution, by looping through the rows and checking if the column equals something then I loop all the columns in that row and color them. Might not be the best way, but it works.

nadeemahmad

I can't find the way to change the cell background color using the below code.

"buttons": ['pageLength',
{
extend: 'excel',
text: 'Export excel',
customize: function( xlsx ) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row c[r^="H"]', sheet).each( function () {
if ( $(this).text()== 'ON') {
$(this).attr( 's', '40' );
$(this).css( 'background-color', 'green' );
}
else if ( $(this).text()== 'OFF') {
$(this).attr( 's', '35' );
$(this).css( 'background-color', 'red' );
}
});
},
exportOptions: {
columns: ':visible',
}
}
],

By using the jQuery $.css() i'm not able to set my desired color on cell background or is there any other color codes for color(Red, Green), so that i can use that codes here ( $(this).attr( 's', '40' ); ).

Can anyone help me out?

allan

$(this).css( 'background-color', 'green' );

That isn't going to work since Excel doesn't use CSS for styling. It uses the Open Spreadsheet specification.

The built in colours and styles are documented in the excelHtml5 documentation. If they don't suit your needs, you need to modify the XML, either by hard coding it or using the customize callback to modify the XML.

Allan

nadeemahmad

Thank you Allan
Hard coding did the job for us.

paabloy

I can not paint the excel rows

paabloy

Thank you very much, I already solve it, paint the rows in the excel file

$('#tablaPrueba').DataTable( {
    order:[],
    dom: 'Bfrtip',
    buttons: [{
    extend: 'excel',
    customize: function(xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        // Loop over the cells
        $('row c', sheet).each(function() {
        //select the index of the row
        var numero=$(this).parent().index() ;
            var residuo = numero%2;
            if (numero==1){            
                $(this).attr('s','22');//22 - Bold, blue background
            }else if (numero>1){
                if(residuo ==0  ){//'is t', 
                $(this).attr('s','25');//25 - Normal text, fine black border
                }else{
                $(this).attr('s','32');//32 - Bold, gray background, fine black border
                } 
            }
        });
    },
    }],
});

shaheerarain786

Hey,
I have build a layout like this, i have a export excel button in my datatables, but when i export the excel it doesn't contain any colors like shown in above picture. Can anybody helps me to figure this out?

Here is my code?

tangerine

See Allan's last post in this thread.

shaheerarain786

Hi @tangerine,

I tried the last post in the thread but it couldn't resolve my problem, basically i want to compare the values of columns for example in my above picture.

If(theLastColumnValueIs == 50) {
color the whole row with specific color
}
I just want this type of example, i was stuck for 2 days with this.
I'll appreciate thanks.

tangerine

This thread is about colours for Excel exporting. If that is not your issue, you could try a forum search. Changing row colour has been asked many times.