ExcelHtml5 Export customize - get background color cell

Tito Bada · December 2018

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

colin · January 2019

Hi @Tito Bada ,

You can just use the css method:

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

Cheers,

Colin

Tito Bada · January 2019

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!

colin · January 2019

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 Bada · January 2019

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

colin · January 2019

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 Bada · January 2019

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

sunnyar29 · April 2019

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

kthorngren · April 2019

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

sunnyar29 · April 2019

@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.

Eswar4unow · October 2019

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 ?

allan · October 2019

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

Wili · March 2021

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.

colin · March 2021

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

Lau939393 · January 2022

Hi, what is the code of color yellow?

colin · January 2022

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

ExcelHtml5 Export customize - get background color cell

ExcelHtml5 Export customize - get background color cell

Answers

Howdy, Stranger!

Categories

DataTables

ExcelHtml5 Export customize - get background color cell

ExcelHtml5 Export customize - get background color cell

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables