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

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

tarik.handaztarik.handaz Posts: 5Questions: 2Answers: 0
edited February 2017 in Free community support

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',                               
                            }
                        }
                    ],

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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.handaztarik.handaz Posts: 5Questions: 2Answers: 0

    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.handaztarik.handaz Posts: 5Questions: 2Answers: 0

    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',                               
                                }
                            }
                        ],
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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.handaztarik.handaz Posts: 5Questions: 2Answers: 0

    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

  • RodeRode Posts: 1Questions: 0Answers: 0
    edited August 2017

    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.

  • nadeemahmadnadeemahmad Posts: 4Questions: 0Answers: 0

    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?

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    $(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

  • nadeemahmadnadeemahmad Posts: 4Questions: 0Answers: 0

    Thank you Allan
    Hard coding did the job for us.

  • paabloypaabloy Posts: 2Questions: 0Answers: 0

    I can not paint the excel rows

  • paabloypaabloy Posts: 2Questions: 0Answers: 0
    edited March 2019

    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
                    } 
                }
            });
        },
        }],
    });
    
  • shaheerarain786shaheerarain786 Posts: 3Questions: 1Answers: 0


    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?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    See Allan's last post in this thread.

  • shaheerarain786shaheerarain786 Posts: 3Questions: 1Answers: 0

    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.

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    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.

This discussion has been closed.