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

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

nikki111nikki111 Posts: 8Questions: 3Answers: 0

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');
«1

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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
            }, ........
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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 );           
            },
    
  • nikki111nikki111 Posts: 8Questions: 3Answers: 0
    edited November 2017

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

  • nikki111nikki111 Posts: 8Questions: 3Answers: 0

    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);
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited 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' );
    }
    
  • nikki111nikki111 Posts: 8Questions: 3Answers: 0

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited 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;
                }
            }
        }
    };
    
  • LePatayLePatay Posts: 20Questions: 3Answers: 1

    @rf1234 Thank you so much for your full example.

  • jacek_kjacek_k Posts: 6Questions: 1Answers: 0

    @rf1234 is that works with IE?

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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_kjacek_k Posts: 6Questions: 1Answers: 0

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.

  • KateFrancescaKateFrancesca Posts: 4Questions: 1Answers: 0

    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):

    <xf xmlns="http://www.w3.org/1999/xhtml" applynumberformat="1" xfid="0" applyborder="1" applyfill="1" applyfont="1" borderid="0" fillid="0" fontid="2" numfmtid="3" addedby="Kate" />

    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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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;
                }
            }
        }
    };
    
    
  • KateFrancescaKateFrancesca Posts: 4Questions: 1Answers: 0

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

  • BharathidhasanNBharathidhasanN Posts: 4Questions: 1Answers: 0

    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 ?

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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_hcdaryl_hc Posts: 2Questions: 0Answers: 0
    edited 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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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_hcdaryl_hc Posts: 2Questions: 0Answers: 0

    @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. :)

  • bjarrarbjarrar Posts: 3Questions: 1Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited 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
    
  • bjarrarbjarrar Posts: 3Questions: 1Answers: 0

    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.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited 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.

Sign In or Register to comment.