Datatables excel export, adding colour to offsetting cell blocks

macca424

Hi, I am looking to add a block of colour to all cells (with data in them) from cell "R" to "AG" which is an offset of 16 cells. I want to assign that a colour (doesn't really matter what colour, I can play around with this later, and then the next 16 cells I would like to have a different colour, then the next 16 cells going back to the original colour, so just alternating between the 2 colours every 16 cells.

 $(document).ready(function() {
$('#example').DataTable({
    dom: 'Bfrtip',
    buttons: [{
        extend: 'excel',
        customize: function(xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];

            // Loop over the cells in column `R`
            $('row c[r^="R"]', sheet).each( function () {
                    $(this).attr( 's', '20' );
            });
        }
    }]
});
});

So obviously at the minute this only sets cell R as the colour, I haven't been able to find the code to look for a value between.

Thanks, Paul.

colin

Hi @macca424 ,

This thread from @rf1234 has an example of a complete excel modification - you could possibly borrow some of the the techniques in there to address your issue.

Hope that helps,

Cheers,

Colin

macca424

Hi Colin,

Thanks for the help, I think I'm almost there now, just need to find a way to dynamically change the cell values?

See below what I have now. This sets the first 16 rows to blue, and the next 16 to green. Is there any way then I can set the next 16 rows to go back to blue (without having to name a full new array starting at AW?

Thanks, Paul

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

    var twoDecPlacesCols = ['R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB','AC', 'AD','AE', 'AF','AG'];           
    for ( i=0; i < twoDecPlacesCols.length; i++ ) {
        $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '20' );
    }

     var twoDecPlacesCols = ['AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ','AR', 'AS','AT', 'AU','AV'];           
    for ( i=0; i < twoDecPlacesCols.length; i++ ) {
        $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '15' );
    }

colin

You could probably group them all up in that first loop - do all same colours together, then the next.

C

macca424

The number of cells in the sheet could change from download to download though, eg first download could have 7 x 16 cells, the next (after i add info to the site) could have 10 x 16, so naming the cells I don't think will work for me.

Is there an easy way to just take the starting array, and add a count of 16 cells to it? so the only named array I have will be the one with R,S,T,U,V etc on it. Any other instances will simply be a count of 16 if there is a header within it.

Thanks, Paul.

colin

You could probably count the number of columns and use that. As you can tell, this is new to me too, it's using the customisations of that library, so not core DT functionality.