datatable excel export, how can we apply multiple styles to same cell

nikki111 · November 2017

datatable excel export, how can we apply multiple styles to same cell , currently it overwrites the old style.
$('row c[r*="G"]', sheet).attr('s', '52');

            $('row c[r*="2"]', sheet).attr('s', '50');
            $('row c[r*="3"]', sheet).attr('s', '50');

rf1234 · November 2017

the built in styles don't allow to apply multiple styles to one cell. it gets overwritten. You would need to develop your own proprietary style that has all the styling that you want in it. Here is an example with detailed comments:

//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 "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 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"/>';
            var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
            '<alignment horizontal="center"/></xf>';
            sSh.childNodes[0].childNodes[0].innerHTML += n1;
            sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2;
            
            var fourDecPlaces = lastXfIndex + 1;
            var greyBoldCentered = lastXfIndex + 2;
            
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            //numeric columns except for rate get thousand separators
            $('row c[r^="F"]', sheet).attr( 's', '64' );
//                $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
            $('row c[r^="G"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
            $('row c[r^="I"]', sheet).attr( 's', '64' );
            $('row c[r^="J"]', sheet).attr( 's', '64' );
            $('row c[r^="K"]', sheet).attr( 's', '64' );
            $('row c[r^="L"]', sheet).attr( 's', '64' );
            $('row c[r^="M"]', sheet).attr( 's', '64' );
            $('row c[r^="N"]', sheet).attr( 's', '64' );
//                $('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', '7' );  //grey background bold
            $('row:last c', sheet).attr( 's', '2' );  //bold
        }, ........

rf1234 · November 2017

Finally I figured out how to address an indiviudal cell ... This case is first about defining a proprietary style. I called it "twoDecPlacesBold". It is actually a combination of the two built in fonts 2 (bold) and 64 (two decimal places with separator I guess). Since you can't apply them sequentially to the cell I needed to define my own style. Based on what I found in file "buttons.html5.js".

I also have a question how can you address multiple columns (not all) as a column range in one statement? (I had to repeat myself because I only know how to address all or just one column ...).

Here is the updated example:

var sSh = xlsx.xl['styles.xml'];
            var lastXfIndex = $('cellXfs xf', sSh).length - 1;
            
            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"/>';
            var s2 = '<xf numFmtId="0" fontId="2" 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"/>'
            sSh.childNodes[0].childNodes[0].innerHTML += n1;
            sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3;
            
            var fourDecPlaces = lastXfIndex + 1;
            var greyBoldCentered = lastXfIndex + 2;
            var twoDecPlacesBold = lastXfIndex + 3;
            
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            //numeric columns except for rate get thousand separators
            $('row c[r^="F"]', sheet).attr( 's', '64' );
//                $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
            $('row c[r^="G"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
            $('row c[r^="I"]', sheet).attr( 's', '64' );
            $('row c[r^="J"]', sheet).attr( 's', '64' );
            $('row c[r^="K"]', sheet).attr( 's', '64' );
            $('row c[r^="L"]', sheet).attr( 's', '64' );
            $('row c[r^="M"]', sheet).attr( 's', '64' );
            $('row c[r^="N"]', sheet).attr( 's', '64' );
//                $('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', '7' );  //grey background bold
            $('row:last c', sheet).attr( 's', '2' );  //bold
//row with totals                  
            $('row:eq(-2) c[r^="B"]', sheet).attr( 's', '2' );  //bold
            $('row:eq(-2) c[r^="C"]', sheet).attr( 's', '2' );  //bold
            $('row:eq(-2) c[r^="D"]', sheet).attr( 's', '2' );  //bold
            $('row:eq(-2) c[r^="E"]', sheet).attr( 's', '2' );  //bold
            $('row:eq(-2) c[r^="I"]', sheet).attr( 's', twoDecPlacesBold );
            $('row:eq(-2) c[r^="J"]', sheet).attr( 's', twoDecPlacesBold );
            $('row:eq(-2) c[r^="K"]', sheet).attr( 's', twoDecPlacesBold );
            $('row:eq(-2) c[r^="M"]', sheet).attr( 's', twoDecPlacesBold );
            $('row:eq(-2) c[r^="N"]', sheet).attr( 's', twoDecPlacesBold );           
        },

nikki111 · November 2017

the styles are not getting applied, am i missing something?

rf1234 · November 2017

how would I know? Can't see your code ...

nikki111 · November 2017

var sSh = xlsx.xl['styles.xml'];
var lastXfIndex = $('cellXfs xf', sSh).length - 1;

            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"/>';
            var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">' +
            '<alignment horizontal="center"/></xf>';
            sSh.childNodes[0].childNodes[0].innerHTML += n1;
            sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2;

            var fourDecPlaces = lastXfIndex + 1;
            var greyBoldCentered = lastXfIndex + 2;

            $('row:eq(0) c', sheet).attr('s', greyBoldCentered);

rf1234 · November 2017

I don't see this line in your code.

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

You defined the style sheet but not the worksheet itself. That's probably the issue.

Take a look at this: https://datatables.net/reference/button/excelHtml5

"The customize method is passed a single parameter - an object with the following structure (note that xml is simply a place holder to represent an XML document - each XML document is of course different):"

{
    "_rels": {
        ".rels": xml
    },
    "xl": {
        "_rels": {
            "workbook.xml.rels": xml
        },
        "workbook.xml": xml,
        "styles.xml": xml,
        "worksheets": {
            "sheet1.xml": xml
        }
 
    },
    "[Content_Types].xml": xml
}

And this:
"As an example, let's modify the text shown in cell A1:"

customize: function ( xslx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
 
    $('c[r=A1] t', sheet).text( 'Custom text' );
}

nikki111 · November 2017

can you put full code with style greyBoldCentered for cell A1 in customize function

rf1234 · November 2017

no sorry I can't because this was taken from the docs!

rf1234 · November 2017

I've been playing around a little with the customize function for the Excel export file. Didn't find a solution to format column ranges - and picked the good old "for" loop for this. Maybe this is helpful for some of you?! @nikki111: this works also for "greyBoldCentered" ... And you can also see how to address individual columns in a given row. This code

$('row:eq(-2) c[r^="A"] t', sheet).text(copyPaste);

pastes something into column A of the second last row. And this code

$('row:eq(-2) c[r^="A"]', sheet).attr( 's', greyBoldCentered );

makes it grey, bold and centered.

This is the complete example:

//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 "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 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"/>';
        var s2 = '<xf numFmtId="0" fontId="2" 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"/>'
        sSh.childNodes[0].childNodes[0].innerHTML += n1;
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3;

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

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

//two decimal places columns          
        var twoDecPlacesCols = ['F', 'I', 'J', 'K', 'L', 'M', 'N'];            
        for ( i=0; i < twoDecPlacesCols.length; i++ ) {
            $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
        $('row c[r^="G"]', 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', '7' );  //grey background bold
        $('row:last c', sheet).attr( 's', '2' );  //bold
//row with totals   
        var boldCols = ['A', 'C', 'D', 'E'];         
        for ( i=0; i < boldCols.length; i++ ) {
            $('row:eq(-2) c[r^='+boldCols[i]+']', sheet).attr( 's', '2' );  //bold
        }
//move text from column B to column A and empty columns B through E
        var copyPaste = $('row:eq(-2) c[r^="B"] t', sheet).text();
        $('row:eq(-2) c[r^="A"] t', sheet).text(copyPaste);
        var emptyCellCols = ['B', 'C', 'D', 'E'];
        for ( i=0; i < emptyCellCols.length; i++ ) {
            $('row:eq(-2) c[r^='+emptyCellCols[i]+']', sheet).text('');
        }

        var twoDecPlacesBoldCols = ['I', 'J', 'K', 'M', 'N'];   
        for ( i=0; i < twoDecPlacesBoldCols.length; i++ ) {
            $('row:eq(-2) c[r^='+twoDecPlacesBoldCols[i]+']', sheet).attr( 's', twoDecPlacesBold );
        }       
    },
    exportOptions: {
//            columns: ':visible',
        format: {
            body: function ( data, row, column, node ) {
                // Strip $ from salary column to make it numeric
                if (typeof data !== 'undefined') {
                    if (data != null) {  
                        if ( column > 13 ) {
                            return '';  //get rid of the changed manually column
                        }
                        if (column === 5 || column === 6 || column > 7) {
                            if (lang == 'de') { //this time we use the English formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods with spaces
                                arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                                //join the pieces together with a period if not empty
                                if (arr[0] > ''  || arr[1] > '') {
                                    data = arr[0] + '.' + arr[1];
                                } else {
                                    return '';
                                }
                            } else {
                                data = data.toString().replace( /[\,]/g, "" );
                            }
                            //result a number still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            data = data.toString().replace( /[^\d.-]/g, "" ); 
                            //percent must be adjusted to fraction to work ok
                            if (column == 6) {
                                if (data !== '') {
                                    data = data / 100;
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {
                        if ( column > 13 ) {
                            return '';  //get rid of the changed manually column
                        }
                    }
                }
                return data;
            }
        }
    }
};

LePatay · June 2018

@rf1234 Thank you so much for your full example.

jacek_k · July 2018

@rf1234 is that works with IE?

rf1234 · July 2018

if you mean "does this work with IE?" then yes. I didn't develop it for IE though. And I am not sure whether it will work with older versions of IE. But I tested with the final version. I avoid some Java Script commands to be compatible with the final version of IE. This is on my list of IE incompatible Java Script: find, findIndex, includes, isNaN with function.

jacek_k · July 2018

@rf1234 so i could apply multi styles in export do excel in IE?

rf1234 · July 2018

yes, but my code might not work in older versions of IE. I only support IE 11 - nothing older than this.

rf1234 · July 2018

And of course there are other ways to write Excel. I provide a pretty detailed reporting for my users. This is all done server side with PHP Spreadsheet. You click a button, the request is sent to the server, the Excel sheet is generated and a link is written into a table which appears in the data table with the request button. Wait time for the user even for complex reports is usually only a few seconds. That allows you to do a lot more than simply exporting data table data at the front end. You can do complex Excel with all kinds of charts with PHP Spreadsheet. You can also predefine Excel templates you fill at the back end including forms that you save as Pdfs using mpdf with PHP Spreadsheet for example.

KateFrancesca · August 2018

Hi, thanks for putting together this example, it's really well documented. I'm struggling to get it work and wondered if you could advise. The problem I've got is that lines 24 and 25, where you append the new styles to the innerHTML property, isn't working.

        sSh.childNodes[0].childNodes[0].innerHTML += n1;
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3;

In my case, I'm getting undefined for the innerHTML property. So instead I tried adding the new styles using jquery by creating a node from the xml string and then appending it like so:

                var styleBoldNumString = '<xf addedBy="Kate" numFmtId="3" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>' 
                var styleBoldNumNode = $(styleBoldNumString);
                var cellXfs = $('cellXfs', sSh)
                cellXfs.append(styleBoldNumNode);

This does add the new xf element to the array but still doesn't work. When I looked at the XML in the document after saving it, I found the attribute names had all been set to lower case which I'm guessing is the problem and an additional xmlns attribute added (although I'm not sure that matters):

Ignore the addedBy="Kate" bit, that was just so I could finder it easier in the XML. I tried it without this too of course. I'm at a bit of a loss of what to try next so if anyone can help I'd be really greatful!

Thanks you!
Kate

rf1234 · August 2018

Hi Kate,

...mmmhh ... don't see what's wrong. And I don't have the time to figure this out right now. Maybe it helps if I post the entire code of my Excel Cash Flow Export Button and a sample report created with it?? Hopefully you can figure it out on that basis?!

Good luck!
Roland

So here is the code for the button. You'll find the sample report attached.

//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 "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 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"/>';
        var s2 = '<xf numFmtId="0" fontId="2" 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;
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;

        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 c', 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
                }
            }           
        }            
    },
    exportOptions: {
//            columns: ':visible',
        modifier: { selected: null }, //make sure all records show up
        format: {
            body: function ( data, row, column, node ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {  
                        if ( column > 15 ) {
                            return '';  //get rid of the changed manually column
                        }
                        if (column === 3 || column === 4 || column > 5) {
                            if (lang == 'de') { //this time we use the English formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods with spaces
                                arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                                //join the pieces together with a period if not empty
                                if (arr[0] > ''  || arr[1] > '') {
                                    data = arr[0] + '.' + arr[1];
                                } else {
                                    return '';
                                }
                            } else {
                                data = data.toString().replace( /[\,]/g, "" );
                            }
                            //result a number still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            data = data.toString().replace( /[^\d.-]/g, "" ); 
                            //percent must be adjusted to fraction to work ok
                            if (column == 4) {
                                if (data !== '') {
                                    data = data / 100;
                                }
                            }
                            return data;                                
                        }
                    }
                }
                return data;
            },
            header: function ( data, column ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {
                        if ( column > 15 ) {
                            return '';  //get rid of the changed manually column
                        }
                    }
                }
                return data;
            }
        }
    }
};

KateFrancesca · August 2018

Hi Roland,
Thank you so much for replying so quickly! Good news (well, sort of!), after some trial and error I stumbled across the issue. It turns out it works fine in Chrome but not in IE11 which I was using. Therefore the issue seems to be an IE specific bug. Thanks again for documenting this as I can now add all the extra styles I need (as long as everyone that uses my app has Chrome!!).
Kate

allan · August 2018

Try using the nightly version of Buttons if you aren't already. There is a bug in Buttons 1.5.2 (current release) causing issues with IE.

Allan

rf1234 · August 2018

Hi Allan, I use Buttons 1.5.1. That doesn't have the bug you mentioned, right?

BharathidhasanN · October 2018

HI Allan, I am using Buttons 1.5.1, still I am getting the issue in IE. Kinldy reply if the bug is there in 1.5.1 version too ?

allan · October 2018

1.5.4 is the current release and shouldn't have any issues with IE. If it does, please link to a test case showing the issue.

Allan

daryl_hc · February 2019

hello,I am a developer from China. It is a little difficult for me to understand discussions above,because my English is really poor.
I am trying to get both Bold And Centred text styles. And now I got some trouble , can anyone could help me?

while I copy some code from above . and it likes:

The chrome says there is an error and it is :

and the error happened at this line :
sSh.childNodes[0].childNodes[5].innerHTML += centralAndBold;

it seems that innerHtml can not write.
can anyone give me some suggestions? Thank you a lot.

colin · February 2019

Hi @daryl_hc ,

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

daryl_hc · February 2019

@colin Thanks for your reply. I had resolved my question.
While combining two styles,I didn't remove the end tag '/>', as a result the chrome reports that error. Thanks for your reply again.

bjarrar · March 2019

Hello all,

I was wondering if anyone was ever able to combine any of the first 5 styles together. I am trying to combine bold and underlined but it seems counter intuitive since both 'xf' elements seem to just have different font ids.

Underlined = '<xf numFmtId="0" fontId="4" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">';

Bold = '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">';

Any help or information on this would be appreciated.

Thank you

kthorngren · March 2019

As rf1234 mentions in the second post:

the built in styles don't allow to apply multiple styles to one cell. it gets overwritten. You would need to develop your own proprietary style that has all the styling that you want in it.

The problem is not due to Datatables but due to the OpenXML format that Excel uses for its files. Its not a simple task to create your own styles. Looks like rf1234 has an example.

Here is another thread with an example from F12Magic that might help.
https://datatables.net/forums/discussion/comment/102413/#Comment_102413

Kevin

rf1234 · March 2019

Yep, it is not a trivial task and I read a lot of posts before I figured out my solution that still works fine.
If you read the comments in my code examples above you should be able to figure it out. In particular take a look at "buttons.html5.js", search for "xl/styles.xml". All you need to do is to figure out what parts of the existing styles you would need to combine into a new style and then add your proprietary styles based on this. You don't want to change "buttons.html5.js" directly because you would have trouble upgrading Data Tables and Editor if you did. Hence you add the styles dynamically:

This part of the code is crucial. I define a new number format here "n1" which I use in my new style "s1". In addition to "s1" I define 3 more styles "s2", "s3" and "s4" and later call them "fourDecPlaces", "greyBoldCentered", "twoDecPlacesBold" and "greyBoldWrapText".
I think the variable names make it clear enough what these new styles are about.

var sSh = xlsx.xl['styles.xml'];
var lastXfIndex = $('cellXfs xf', sSh).length - 1;
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"/>';
var s2 = '<xf numFmtId="0" fontId="2" 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;
sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;

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

This is an example where I apply 3 of my proprietary styles and one built-in style ("2" = bold):

$('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

bjarrar · March 2019

Hello @rf1234 ,

First of all thank you. I have used the answers you have posted on this forum religiously and have even bookmarked this page as I always keep coming back.

I was able to successfully create so many combined styles using the sample code you have created and they work perfectly. However, my issue here is not with the combination of styles per se. It's the combination of specific styles together, namely Bold and Underlined or any of the first 5 elements in that xl/styles.xml list. As I go through the buttons.html5.js and look for those elements I see the following:

'<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+ '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+ '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+ '<xf numFmtId="0" fontId="3" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+ '<xf numFmtId="0" fontId="4" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+

As you can see and as I have mentioned in my first comment:

Bold: '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">';

Underlined: '<xf numFmtId="0" fontId="4" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">';

The combination of which would result in something like:

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

                    var sSh = xlsx.xl['styles.xml'];
                    var lastXfIndex = $('cellXfs xf', sSh).length - 1;

                    var s1 = '<xf numFmtId="0" fontId="4" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                        '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                        '<alignment horizontal="center"/></xf>';

                    sSh.childNodes[0].childNodes[5].innerHTML += s1;
                    var boldCenteredUnderlined = lastXfIndex + 1;

                    $('row c[r^="B"]', sheet).attr('s', '63');
                    $('row c[r^="D"]', sheet).attr('s', '63');
                    $('row c[r^="E"]', sheet).attr('s', '63');
                    $('row c[r^="C"]', sheet).attr('s', '63');
                    $('row:eq(1) c', sheet).attr( 's', '50');
                    $('row:eq(2) c', sheet).attr( 's', boldCenteredUnderlined);

This throws an error as it should in chrome because of the two xf tags but this is where my problem arises. Since the only difference between bold and underlined is the fontId="4" vs. fontId="2" in their respective xf tags, what would be the most appropriate way to combine these two styles? In all other combinations it is easy as it is either a fontId change added to an alignment tag or a color or wrapText.

I hope this clears up where my issue is stemming from. I appreciate your time in helping me with this as you have become the de facto SME when it comes to combining styles in the excelHtml5 export realm.

rf1234 · March 2019

Looks like you can only have one font in a style. Just as I created my own number format to become part of a style you would have to create your own font to become part of a style.

You would need to do something like:

var sSh = xlsx.xl['styles.xml'];
var lastFontIndex = $('fonts <font>', sSh).length - 1;

var f1 = 
'<font>'+
        '<sz val="11" />'+
        '<name val="Calibri" />'+
        '<b />'+'<u />'+
'</font>'

//add the new font to the other fonts
sSh.childNodes[0].childNodes[1].innerHTML += f1;

//define the style with the new font (number passed in as a variable)
var sBoldUnderlined = '<xf numFmtId="0" fontId="' 
+ (lastFontIndex+1) + '" fillId="0" borderId="0" applyFont="1"
applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">';

This way you should have created a new font that is bold and underlined which has the fontId="5". Tried to make it more flexible above in case @allan adds more fonts.

Don't know whether this works " '<b />'+'<u />' ". But I would give it a try.

datatable excel export, how can we apply multiple styles to same cell

datatable excel export, how can we apply multiple styles to same cell

Answers

Howdy, Stranger!

Categories

DataTables

datatable excel export, how can we apply multiple styles to same cell

datatable excel export, how can we apply multiple styles to same cell

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables