Adding Custom Colors for Cells in Excel Export

hippopotamus · March 2023

I had a question regarding adding custom colors for excel cells in exports. There are a few threads which talk about this but I haven't found any good examples of doing this. From what I have read from thread such as this https://datatables.net/forums/discussion/40551/how-can-i-change-the-background-color-for-a-cell-excel-export are that we need to modify the XML file to include your custom styling. One way is to manually go into it and hardcode it. But the other way I've read and the way I want to do it is to add to it during the export through the customize function. I've read about this several times in threads but haven't saw any actual examples about it. I was wondering if anybody had one they can show. Thank you for any help.

rf1234 · March 2023

I add custom cell colors in here. It is basically a change history view on my log. Whenever a cell value changes within the same contract it is colored. To make it easier to track log changes.

This is what is looks like. Column B shows whether a record is "new" or just "edited". Everything is ordered by column D and C ascending.

And this is the customize method. You'll figure it out. Good luck!

customize: function( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    var cols = $('col', sheet);
    var rows = $('row', sheet);
    var i; var y; var z;
    //the first two rows are heading, so we start with
    //the fourth row and compare it with the third etc.
    var newRow = '';
    var oldRow = '';
    var comments = '';
    for ( i=3; i < rows.length; i++ ) {   
        //loop through all columns within the row
        for ( y=0; y < cols.length; y++ ) { //last col is not relevant for comparison (creator)
            //shorten comments text to max 200 length
            for ( z=0; z < xlsCommentsCols.length; z++ ) {
                if ( xlsLetters[y] === xlsCommentsCols[z] ) {
                    comments = $('row:eq('+i+') c[r^='+xlsCommentsCols[z]+'] t', sheet).text();
                    if ( comments.length >= 200 ) {
                        comments = comments.substr(0,197) + '...';
                    }
                    $('row:eq('+i+') c[r^='+xlsCommentsCols[z]+'] t', sheet).text(comments);
                }
            }
            oldRow = $('row:eq('+(i-1)+') c[r^='+xlsLetters[y]+'] t', sheet).text();
            newRow = $('row:eq('+i+') c[r^='+xlsLetters[y]+'] t', sheet).text();
            if ( xlsLetters[y] == "A" ) { //first column contains name
                oldRow = oldRow.substr(5, 10);
                newRow = newRow.substr(5, 10);
            }
            if ( i === 3 ) { //change without New
                if ( $('row:eq('+(i-1)+') c[r^="B"] t', sheet).text() != "Neu" &&
                     $('row:eq('+(i-1)+') c[r^="B"] t', sheet).text() != "New"    ) {
                    if ( xlsLetters[y] == "B" ) {//we always highlight the action column if a change occurred
                        $('row:eq('+(i-1)+') c[r^='+xlsLetters[y]+']', sheet).attr( 's', '5' );  //normal text grey background
                    }
                } 
            }
            //we only hihglight if it is NOT a new variable because a new variable is not a change
            if ( $('row:eq('+i+') c[r^="B"] t', sheet).text() != "Neu" &&
                 $('row:eq('+i+') c[r^="B"] t', sheet).text() != "New"    ) {
                if ( $('row:eq('+i+')     c[r^="D"] t', sheet).text() !== 
                     $('row:eq('+(i-1)+') c[r^="D"] t', sheet).text()      ) { //changed serial
                    if ( xlsLetters[y] == "B" ) {//we always highlight the action column if a change occurred
                        $('row:eq('+(i)+') c[r^='+xlsLetters[y]+']', sheet).attr( 's', '5' );  //normal text grey background
                    }
                } else if ( xlsLetters[y] == "B" ) {//we always highlight the action column if a change occurred
                    $('row:eq('+i+') c[r^='+xlsLetters[y]+']', sheet).attr( 's', '11' );  //white text red background
                } else if ( oldRow !== newRow ) {                                        
                // we don't highlight the editing time columns and not columns if deleted
                    if ( xlsLetters[y] != "C" ) {
                        if ( $('row:eq('+i+') c[r^="B"] t', sheet).text() != "Gelöscht" &&
                             $('row:eq('+i+') c[r^="B"] t', sheet).text() != "Deleted"    ) {
                            $('row:eq('+i+') c[r^='+xlsLetters[y]+']', sheet).attr( 's', '20' );  //blue background
                        }
                    }
                }
            }
        }
    }            
},

Here is one of the global variables I defined that makes life easier. Of course you can calculate the Excel column letter. But I don't like math and this is so much easier

var xlsLetters = 
    [ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
      'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 
      'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 
      'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV',
      'AW', 'AX', 'AY', 'AZ', 
      'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 
      'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV',
      'BW', 'BX', 'BY', 'BZ' ];

This is the relevant part of the docs:
https://datatables.net/reference/button/excelHtml5

Search for "Built in Styles". Of course you can define your own styles and you'll find posts on that in this forum. But that isn't trivial.

hippopotamus · March 2023

Thanks for your example. I was hoping to use my own colors instead of the built in styles but as you say its not that trivial. But I'm a bit curious, would you happen to know how to proceed with doing that though?

rf1234 · March 2023

ok, here is another "customize" method from a different button. I create my own styles here. I don't have your use case (proprietary fills) but that should work the same way. You would need to fiddle with this:
//styleSheet.childNodes[0].childNodes[2] ==> fills <fills count="6"> </fills>

This code in https://cdn.datatables.net/buttons/2.3.5/js/buttons.html5.js
would need to be appended by you programmatically at run time to make it work. I don't recommend "hacking" the file itself because then you would need to redo this at every data table version change. One key insight is: You cannot apply multiple styles to one cell. If you need a combination of several things e.g. bold, underlined, purple background, you would need to create it as one style combining all of those features. That makes it cumbersome. I don't do this any more. So this "customize" method of mine is very old.

'<fills count="6">'+
                '<fill>'+
                    '<patternFill patternType="none" />'+
                '</fill>'+
                '<fill>'+ // Excel appears to use this as a dotted background regardless of values but
                    '<patternFill patternType="none" />'+ // to be valid to the schema, use a patternFill
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="FFD9D9D9" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="FFD99795" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffc6efce" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
                '<fill>'+
                    '<patternFill patternType="solid">'+
                        '<fgColor rgb="ffc6cfef" />'+
                        '<bgColor indexed="64" />'+
                    '</patternFill>'+
                '</fill>'+
            '</fills>'+

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
    customize: function( xlsx ) {
// see built in styles here: https://datatables.net/reference/button/excelHtml5
// take a look at https://cdn.datatables.net/buttons/2.3.5/js/buttons.html5.js , search for "xl/styles.xml"
//styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
//styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
//styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
//styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
//styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
//styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
//on the last line we have the 67 currently built in styles (0 - 66), see link above

        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
        var lastFontIndex = $('fonts font', sSh).length - 1; 
        var f1 = //bold and underlined font
        '<font>'+
                '<sz val="11" />'+
                '<name val="Calibri" />'+
                '<b />'+'<u />'+
        '</font>'

        var i; var y;
//n1, n2 ... are number formats; s1, s2, ... are styles
        var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
        var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
        //define the style with the new font (number passed in as a variable)
        var s2 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center"/></xf>';         
//s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
//just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
        var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
        var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center" wrapText="1"/></xf>'
        sSh.childNodes[0].childNodes[0].innerHTML += n1;  //new number format
        sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4; //new styles

        var fourDecPlaces    = lastXfIndex + 1;
        var greyBoldCentered = lastXfIndex + 2;
        var twoDecPlacesBold = lastXfIndex + 3;
        var greyBoldWrapText = lastXfIndex + 4;

        var sheet = xlsx.xl.worksheets['sheet1.xml'];
    //create array of all columns (0 - N)
        var cols = $('col', sheet);
    //set lenght of some columns: col number: length (excl. first column)
        var colsLength = ['01:12', '02:12', '03:16', '04:10', '05:12', '06:16',
                          '07:16', '08:16', '09:16', '10:16', '11:16', '12:16',
                          '13:16', '14:16', '15:16'];
        for ( i=0; i < colsLength.length; i++ ) {
            $( cols [ parseInt( colsLength[i].substr(0,2) ) ] )
                    .attr('width', parseInt( colsLength[i].substr(3) ) );                
        }

//two decimal places columns          
        var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'];            
        for ( i=0; i < twoDecPlacesCols.length; i++ ) {
            $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
        $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
//                $('row c', sheet).attr( 's', '25' ); //for all rows
        $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
        $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
        $('row:last c', sheet).attr( 's', '2' );  //bold

//move text from column B to column A and empty columns B through F of rows with totals
        var rows = $('row', sheet);  //create array of all rows (0 - N)      
        var copyPaste; var dateRefRate; var newDate;
        var emptyCellCols = ['B', 'C', 'D', 'E', 'F'];
        var twoDecPlacesBoldCols = ['G', 'H', 'I', 'J', 'L', 'M', 'O', 'P'];
        var boldCols = ['A'];
        for ( i=2; i < rows.length; i++ ) {                  
            copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();
            if ( copyPaste == 'Summen per Laufzeitende' ||
                 copyPaste == 'Totals'                     ) {
                $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste);                    
                for ( y=0; y < emptyCellCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+emptyCellCols[y]+']', sheet).text('');
                }
                for ( y=0; y < twoDecPlacesBoldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+twoDecPlacesBoldCols[y]+']', sheet).attr( 's', twoDecPlacesBold );
                }
                for ( y=0; y < boldCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+boldCols[y]+']', sheet).attr( 's', '2' );  //bold
                }
            }           
        }            
    },

hippopotamus · March 2023

This was exactly what I was looking for. Thank you so much for the help.

Adding Custom Colors for Cells in Excel Export

Adding Custom Colors for Cells in Excel Export

Answers

Howdy, Stranger!

Categories

DataTables

Adding Custom Colors for Cells in Excel Export

Adding Custom Colors for Cells in Excel Export

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables