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: 48,301Questions: 1Answers: 6,970 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: 48,301Questions: 1Answers: 6,970 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: 48,301Questions: 1Answers: 6,970 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.

Sign In or Register to comment.