ExcelHtml5 Export customize - get background color cell

ExcelHtml5 Export customize - get background color cell

Tito BadaTito Bada Posts: 4Questions: 1Answers: 0

Good morning, in excel export customize for example I can get the value of the specific cell with the function:

$('is t', this).text();

Is it possible to get the value of the background colour (define with html tag in <td>) of a specific cell?

Thank you if you can help me

Tito

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    Hi @Tito Bada ,

    You can just use the css method:

    $('is t', this).css('background-color')
    

    Cheers,

    Colin

  • Tito BadaTito Bada Posts: 4Questions: 1Answers: 0

    Hi Colin, thanks for your help!

    I tried to use the feature you suggested, here is a quick summary, I hope to be concise:

    In the generation of the cells of the table I use (for example):
    <td style="background-color: #087c22"><?php echo $data?></td>

    The column of cells in the excel file on which I am going to generate the background color is column K (verified after a test export).

    To generate the excel file I use the following code:

                                    // Loop over the cells in column `K`
                                    $('row c[r^="K"]', sheet).each( function () {
    
                                        var color = $('is t', this).css('background-color');
    
                                        alert("Background color = " + color);
    
                                    });
    

    This is just a test to verify the content of the variable.
    Unfortunately the content of the variable "color" is always "undefined".

    I'm definitely doing something wrong. I'm sorry.
    Can you help me?
    Thanks, bye!

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @Tito Bada ,

    I think we may be talking cross purposes here - this is what I was referring. The console displays the RGB code for your <td style="background-color: #087c22">.

    Would you be able to modify that example to demonstrate your problem, please.

    Cheers,

    Colin

  • Tito BadaTito Bada Posts: 4Questions: 1Answers: 0

    Hi Colin,
    thanks for the example you gave me.

    In practice I am in the following situation: I would like to create a table where in a given column there are numerical values. Some of these values are equal to 0. Some cells with 0 are represented with the background color orange, others with the color green (for different needs).

    With html of course I solve with the "style tag" that I insert in the "td tag"

    can be
    td style="background-color: #ff8100"><?php echo $data?>

    or
    td style="background-color: #087c22"><?php echo $data?></td>

    When I export in excel format, I would like to be able to read the background color code I have set and replicate it in the excel file.

    In the example of the code I would like to do this:

    buttons: [
                   {
                      extend: 'excelHtml5',
                      customize: function(xlsx) {
                      var sheet = xlsx.xl.worksheets['sheet1.xml'];
    
                       // Loop over the cells in column `K`
                       $('row c[r^="K"]', sheet).each( function () {
    
                       var color = $('is t', this).css('background-color');
    
                        if(color == ) {
    
                                $(this).attr( 's', '10' );
    
                          } else if (color == ) {
    
                                $(this).attr( 's', '15' );
    
                          }
    
                         });
                       }
                    }
    ]
    

    in the example the value of the color variable is always equal to "undefined".
    I don't know what to control in the if(color == ........

    Maybe you can't read in the customized excel function the value of a style tag displayed in html, or I'm wrong about reading or generating the variable.

    I'll try to change your example to generate the problem, but if I'm doing something wrong let me know.
    Sorry for my incompetence.

    Cheers,

    Tito

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited January 2019 Answer ✓

    Hi @Tito Bada ,

    Here you go, a nice Monday afternoon task! This is using "Red" and "Green" CSS, but you can modify the if statement to be any colour code.

    Hope that does the trick,

    Cheers,

    Colin

  • Tito BadaTito Bada Posts: 4Questions: 1Answers: 0

    Hi @Colin,

    this Monday afternoon I did the task and... it worked!
    Really perfect! I understood how to act on the code.

    Thank you for the example.

    Thank you for solving the problem, and especially thank you for teaching.

    Thank you so much.

    Cheers,

    Tito

  • sunnyar29sunnyar29 Posts: 4Questions: 1Answers: 0

    Hi @Tito Bada ,

    Can you please share your answer which fixed the issue because in my case also the value of the color variable is always equal to "undefined".

    Thanks,
    Sunny

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,734

    Colin posted a working example.

    in my case also the value of the color variable is always equal to "undefined".

    Can you post a link to your page or a test case replicating the issue? Maybe you can update Colin's example with your code.

    Kevin

  • sunnyar29sunnyar29 Posts: 4Questions: 1Answers: 0

    @kthorngren : Actually in my scenario I have to loop over all the columns instead of a single column. I got it working now. Thanks for the guidance.

  • Eswar4unowEswar4unow Posts: 1Questions: 0Answers: 0

    Hi @colin @kthorngren

    In the working example , if I set the paging: true, the excel export with cell color is incomplete. How to make it work with paging ?

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    Oddly using $(...).css('background-color') will return an rgb colour on cells which have been drawn in the document, but will return a string (e.g. red or green) on cells which haven't yet been drawn.

    Adding a check for that fixes the export: http://live.datatables.net/horuxuxo/2/edit .

    Allan

  • WiliWili Posts: 7Questions: 1Answers: 0

    Hi @colin
    this work great, but as like @Eswar4unow question, How to color color all the data , without see the paging page ? the color in excel only follow the 1'st page.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    It's behaving as expected in Allan's test case. Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

    Cheers,

    Colin

  • Lau939393Lau939393 Posts: 1Questions: 0Answers: 0

    Hi, what is the code of color yellow?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    There isn't a built in style for yellow - see the full list here - if that was needed you would have to edit the XML doc

    Colin

Sign In or Register to comment.