How to set Excel export's cells background colors by table cell class?

relish27

Hello,
I found this great example here for changing background colors on Excel exports: https://codepen.io/RedJokingInn/pen/MbNMry

This and other examples I'm finding allow for changing the bg color based on the cell value. In my regular table display in browser, I am setting a class to my cell. I would like to be able to set the backgrounds appropriately in the export, but I can't just test the value of the cell. The class represents a certain "significance" rating that cannot be calculated by any formula; it's just a value that is given to me.

It doesn't seem like it's possible to get the class value, because the code is just looping through the xml document, which doesn't have the table cell classes in it.

One thought I had is that I could "hide" some text in the cell and then use the "search all cells" option to change the background color if it exists in the cell. However, I wouldn't want that text to be visible in the export. Is it possible to replace / remove text in a column before exporting? I could hide it using CSS on the regular display.

Any other ideas?

Thank you!

colin

Hi @relish27 ,

This here checks by class - a few of the earlier rows have class demo added to the Name column. This example checks for that class.

Hope that does the trick,

Cheers,

Colin

relish27

Hi, @colin

Thank you so much! That looks like it should do the trick, but somehow it is not working on my end. I don't get any errors, but I also just don't get any color. My code is below. Am I missing something obvious?

One thing I had wondered earlier is why I couldn't get console.log to display anything. But I see that you do have it in the customize area -- is there something I need to do in order to allow it to show?

        var output_table = $(this).DataTable({
            "paging": false,
            "searching": false,
            "bInfo": false,
            "order": output_order,
                dom: 'Bfrtip',
                responsive: false,
                "scrollX": true,
                buttons: [
                    {
                        extend: 'excelHtml5',
                        dom: 'Bfrtip',
                        text: 'Download XLS',
                        title: output_filename,
                        messageTop: output_intro + '\n' + output_legend_header,   
                        messageBottom: output_legend_footer,                    
                        customize: function( xlsx ) {

                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            var row = 0;
                     
                            $('row', sheet).each(function(x) {
                              if (x > 1) row++;
                              
                                console.log(output_table.row(':eq('+row+')').data())
                                if ($(output_table.cell(':eq('+row+')', 0).node()).hasClass('sig-w')) {
                                  console.log('YES - sig-w');
                                  $('row:nth-child('+(x+1)+') c', sheet).attr('s', '10');
                                }
                                else if ($(output_table.cell(':eq('+row+')', 0).node()).hasClass('sig-b')) {
                                    console.log('YES - sig-b');
                                  $('row:nth-child('+x+') c', sheet).attr('s', '20');
                                }
                          });
                
                        }
                    },
                ]       
            }); 

colin

Hi @relish27 ,

Aha, I didn't realise I'd left in the console logging! To view it, go to Developer Tools (F12) and open the console tab. You'll see my debug when the Excel document is being created (so only after you click the button).

The only thing I can think of is that the class sig-w isn't on the first cell of the row. You can verify that by right-clicking on the cell and choosing Inspect. My one, for example, shows this:

<td class="demo sorting_1">Ashton Cox</td>

If still no joy, would you be able to link to your page so we can take a look?

Cheers,

Colin

colin

I just had a thought what the problem could be - I'll reply back once I've scratched my head and mulled it over.

colin

As you're looping over the table, output_table is being changed - I know it's being declared within the loop, but it would be safer (or clearer at least) to use the table that's passed into the customize function - see here.

Hope that does the trick!

Cheers,

Colin

relish27

Re: console log, thank you -- I do use the console log a lot, but in this case I am not seeing anything. However, now that I've got more being spit out, I can see that it is there briefly, but gets deleted after the XLS is generated. BUT, just realized, that's in Firefox but not in Chrome. In Chrome, I do see it. Weird. But good to see what's coming out there now!

Re: being clearer about the table -- I think I'm good there. Here's the larger gist that shows what's around what I sent before. I have a page that dynamically generates a number of tables that use DataTables.net. I use jQuery to loop through all instances of the them. https://gist.github.com/relish27/39099987149c445d16f19f088aeae407

What you said, though, about the class not being in the first column tipped me off to a few things, though:

1) I do need to check all cells. And I can do that now by doing an additional loop across the columns within your row loop (see below).

2) I see your code is applying the style to the whole row, right? I don't understand the syntax well enough to get this to work for the cell vs. the row. I found this in an example somewhere for applying just to a cell:

$('row c[r^="D6"]', sheet).attr('s', '32');

How would I adjust what I have below to work?

I also have three header rows in this case, so I had to adjust accordingly. But right now the correct three rows have a red background color!! So I just need to get it to be the correct cell vs. the whole row.

                        customize: function( xlsx ) {

                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            var row = 0;
                     
                            $('row', sheet).each(function(x) {
                                if (x > 3) {

                                    console.log("");
                                    console.log("---- row x: " + x);
                                  
                                    for(var i=0; i<num_columns; i++) {

                                        console.log("---- column i: " + i);

                                        console.log(output_table.row(':eq('+row+')').data());

                                        if ($(output_table.cell(':eq('+row+')', i).node()).hasClass('sig-w')) {
                                          console.log('YES - sig-w - row ' + row + ', column ' + i);
                                          $('row:nth-child('+(x)+') c', sheet).attr('s', '10');                                       

                                        }
                                        else if ($(output_table.cell(':eq('+row+')', i).node()).hasClass('sig-b')) {
                                            console.log('YES - sig-b - row ' + row + ', column ' + i);
                                          $('row:nth-child('+(x)+') c', sheet).attr('s', '20');
                                        }

                                    }

                                    row++;
                                }

                          });
                
                        }

relish27

I got it!! Thanks to being able to use console.log finally, I spit out the whole sheet so I could see the XML structure. That helped me to figure out how to get to the cell. In my case, it is:

$('row:nth-child('+(x)+') c', sheet).eq(i).attr('s', '10');

So that the whole customize chunk is like so:

                        customize: function( xlsx ) {

                            var sheet = xlsx.xl.worksheets['sheet1.xml'];

                            console.log(sheet);
                            var row = 0;
                     
                            $('row', sheet).each(function(x) {
                                if (x > 3) {

                                    console.log("");
                                    console.log("---- row x: " + x);
                                  
                                    for(var i=0; i<num_columns; i++) {

                                        console.log("---- column i: " + i);

                                        console.log(output_table.row(':eq('+row+')').data());

                                        if ($(output_table.cell(':eq('+row+')', i).node()).hasClass('sig-w')) {
                                          console.log('YES - sig-w - row ' + row + ', column ' + i);
                                          $('row:nth-child('+(x)+') c', sheet).eq(i).attr('s', '10');
                                        
                                        } else if ($(output_table.cell(':eq('+row+')', i).node()).hasClass('sig-b')) {
                                            console.log('YES - sig-b - row ' + row + ', column ' + i);
                                          $('row:nth-child('+(x)+') c', sheet).eq(i).attr('s', '20');
                                        }

                                    }

                                    row++;
                                }

                          });
                
                        }

colin

Hi @relish27 ,

Excellent, glad all good!

Cheers,

Colin

relish27

Yes, woo hoo! Thank you for your help.