Excel Export Button not setting cell background

CWentzloff

So I'm creating a table output for lab data results. I've been able to compare data cells with criteria cells and highlight them on the webpage. I've tried coding in the s attribute when I set the highlighting and when I use the customize option, but to no avail.
As a note, we're running this on wordpress 4.9.5, and I've changed the jquery version to 3.3.1. I have updated all enqueued scripts to the CDN files list in the example https://datatables.net/extensions/buttons/examples/html5/excelCellShading.

For the life of me, I cannot get the cell highlighting to show up. I have included the DataTables call in the document.ready function. If I'm missing something, please let me know.

jQuery('#testHeaders').DataTable({
    dom: 'Blfrtip',
    buttons: [{
        extend: 'excelHtml5',
        title: 'TAR Results',
        customize: function(xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];        
            jQuery('.exceedence').each( function () {
                jQuery(this).attr( 's', '20' );
                     //console.log(this);
                     //returns all the individual <td> elements, so it's finding them, just seemingly not applying s to the excel output
            });
        }
    }],
    paging: false
});

kthorngren

I think the problem is you are looping through HTML elements using this:
jQuery('.exceedence').each( function () {

Where you need to be looping through the worksheet (var sheet = xlsx.xl.worksheets['sheet1.xml'];) like the example:
$('row c[r^="C"]', sheet).each( function () {

The loop in the example will set the attributes of the worksheet.

Kevin

CWentzloff

Thanks for the quick response. I see exactly what you're saying.
Is there any way to pull in a data attribute or class of the corresponding cell from the html table? With your explanation I can affect the highlighting on an entire row or column, but the goal is to look at individual cells. Hoping not to have to do all the comparisons in this again since I've already done it in my php code.

allan

Is there any way to pull in a data attribute or class of the corresponding cell from the html table?

Currently no - I'm afraid not. What happens is that Buttons uses buttons.exportData() to get the array of data to export and then builds up the Excel sheet from that. So there is a seperation of concerns which means that effectively the link is broken.

You can use a data formatter to output the data at a point when you can access the node, but there is still a text only output from there. I guess you could use some kind of meta formatting - like put a | at the front to show it as red, or whatever and then strip that symbol when generating the XML, but that's going to be just as messy, or messier than writing the calculations again. Sorry!

Allan

CWentzloff

Totally understandable. By the way we love DataTables for our site. It's been a huge work horse. I was able to go through, when outputting the data, and determine if there was an exceedence, store the location of that to an array (I had to remove the sort ability or this really went bad), and then used that array to generate the highlighted cells in the script when the php output the script tag on the page. Again, not super clean, but we'll call it a mild inconvenience