Excel cell formatting - cannot figure out how to make it work

lordterrin

I've read through the guide here: https://datatables.net/extensions/buttons/examples/html5/excelCellShading.html, and also looked at some forum posts where people use the customize function, but for me formatting is not working.

I've downloaded the sample file you have at the above link, but there is no formatting in it for me when I open the saved file. I've also attempted to do this on my own without any luck. The code executes, but the downloaded file contains no additional formatting. Is there a particular version of Excel up to which this code works? I'm using Excel 2013.

F12Magic

It'll definitely work with excel 2013.
In the example you mention, the styling is applied by the code:

$(this).attr( 's', '20' );

This means that if the value of the current cell in column F is greater than 500000, it gets the "s"tyle with number 20. Lookup the style number you want at the documentation of the excelHtml5
Of course you'll need to adjust

$('row c[r^="F"]', sheet).each( function () {

with a jquery selector that fits your needs.

lordterrin

Interesting - Yours were some of the posts I had looked at

What's interesting though is that when I download Alan's example in the link above, there is no formatting. Is there formatting for you when you download the file?

F12Magic

Hmmm, indeed the example isn't working with me either in FF52.0.2
The problem could be the recently added auto detection of cells with currencies in them.

@Allen could you check your example? In my example below I also have problems with currency columns.

And if you want to see more styling examples, check my codepen.

lordterrin

Hey @F12Magic ,

Based on this code:

$('row c[r^="E"]', sheet).each( function () {
                                if(parseFloat(EuToUsCurrencyFormat($('is t', this).text())) > 1500){
                                    $(this).attr( 's', '17' );
                                }
                            });

How would you iterate over each cell in a ROW, instead of a column? I'm looking to iterate over the header row and use a different s attribute depending on a few variables...

F12Magic

Look in the JS pane of my codepen. There are comment lines above each code line explaining the selector. To iterate and style the cells you can use the same principle as you mentioned above. Something like below would do the job.

//Iterate over each cell of the first row
$('row:first c', sheet)..each( function () {
    var cValue = $('is t', this).text(); //The cell content
    //Conditional test
    if(cValue > 1000){
        //Apply the style
        $(this).attr( 's', '17' );
    }
});

lordterrin

Yeah I grabbed this from your codepen, I just wasn't sure of the syntax for iterating over rows. This is perfect. Thank you!

In the event this helps anyone else, here's the ultimate code I used to check whether or not the following headers were in my output. If they were, I wanted rows with those specific names to be a different color.

extra_columns = ['name','favorite_color','type', 'cost', 'option', 'status'];


customize : function ( xlsx ){
          var sheet = xlsx.xl.worksheets['sheet1.xml'];

          //Iterate over each cell of the first row
          $('row:first c', sheet).each( function () {
              var cValue = $('is t', this).text();                                         
              
              if(extra_columns.indexOf(cValue) > -1){                                              
                  $(this).attr( 's', '36' );
              } 
          });                                                                           
},
                     

jsmx@nne.com

@F12Magic wrote a nice example of how to loop through each cell in the first row row:first:

// Iterate over each cell of the first row
$('row:first c', sheet)..each( function () {
        // Apply the style
        $(this).attr( 's', '17' );
});

I need to loop through each cell in the 3rd row and set the background color.

How would you specify the row selector for row n?

F12Magic

Following code is untested, but if css selectors work it should be OK.

// Iterate over each cell of the third row
$('row:nth-child(3n) c', sheet).each( function () {
        // Apply the style
        $(this).attr( 's', '17' );
});

On the other hand, I checked my codepen examples and found this working for sure:

$('row c[r*="3"]', sheet).each( function () {
        // Apply the style
        $(this).attr( 's', '17' );
});