Multiple style during export excel

datatable_user

I need to export excel where specific row or column contain multiple style. In my example column Language is suppose to be center and bold. My problem is column Language is center but didn't bold.

Test case is here : https://jsfiddle.net/6f1m2nbo/4/

Weirdly row 1 (title) is not bold anymore. Please assist and thanks in advance.

rf1234

This can be very, very cumbersome. The problem is that you cannot apply multiple styles to a cell at a time. If you have styling like bold, grey background, centered just for one cell you need to define a new style and apply that.

Here is something from my own coding that may or may not help you. Sorry, but I can't help you with more ...

In this code I
- define a bold and underlined font
- define a new number format (percent with 4 decimal places)
- define 4 new styles (four decimal places; grey background, bold and centered; two decimal places and bold; grey background, wrapped text)

Most of the other stuff you may probably ignore, I guess. I made my own little column transformation variable to convert column numbers into Excel column letters, too.

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' ];

customize: function( xlsx ) {
// see built in styles here: https://datatables.net/reference/button/excelHtml5
// take a look at https://cdn.datatables.net/buttons/2.2.2/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; var z;
//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);

    //convert the numbers of the exported columns to Excel column letters
    var xlsTwoDecPlacesCols = [];
    var xlsFourDecPlacesCols = [];
    var xlsLengthSpecifiedCols = [];
    var xlsLengthIfSpecifiedCols = [];
    var xlsExportColumnsCols = []; //all Export columns - we want them with letters

    for ( i=0; i < exportColumns.length; i++ ) {
        if ( exportColumnsTwoDecPlaces[i] ) {
            xlsTwoDecPlacesCols.push( xlsLetters[ i ] );
        }
        if ( exportColumnsFourDecPlaces[i] ) {
            xlsFourDecPlacesCols.push( xlsLetters[ i ] );
        }
        //no letters we stay numeric here
        if ( exportColumnsLengthSpecified[i] ) {
            xlsLengthSpecifiedCols.push( i );
        }
        //we take the content which is the Excel column length
        if ( exportColumnsLengthIfSpecified[i] > 0 ) {
            xlsLengthIfSpecifiedCols.push( exportColumnsLengthIfSpecified[i] );
        }
        xlsExportColumnsCols.push( xlsLetters[ i ] );
    };

    //two decimal places columns                      
    for ( i=0; i < xlsTwoDecPlacesCols.length; i++ ) {
        $('row c[r^='+xlsTwoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
    }
    //four decimal places columns                      
    for ( i=0; i < xlsFourDecPlacesCols.length; i++ ) {
        $('row c[r^='+xlsFourDecPlacesCols[i]+']', sheet).attr( 's', fourDecPlaces );
    }
    //set lenght of some columns: col number: length (excl. first column)
    for ( i=0; i < xlsLengthSpecifiedCols.length; i++ ) {
        $( cols [ xlsLengthSpecifiedCols[i] ] ).attr('width', parseInt( xlsLengthIfSpecifiedCols[i] ) );  
    }

//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) ) );                
//            }

    $('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 = [];
    var twoDecPlacesBoldCols = xlsTwoDecPlacesCols;
    var boldCols = ['A'];
    for ( i=2; i < rows.length; i++ ) {  
        for (z=0; z < xlsExportColumnsCols.length; z++) {
            copyPaste = $('row:eq('+i+') c[r^='+xlsExportColumnsCols[z]+'] t', sheet).text();
//                    copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();            
            //this will not be formatted if column "payment date" is not exported and is not in position B; this is ok!
            if ( copyPaste == 'Summen per Laufzeitende' ||
                 copyPaste == 'Totals'                     ) {
                emptyCellCols.push(xlsExportColumnsCols[z]);
                for ( y=0; y < emptyCellCols.length; y++ ) {
                    $('row:eq('+i+') c[r^='+emptyCellCols[y]+'] t', 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
                }
                $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste); 
                break;
            }
        }
    }            
},