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

Answers

  • rf1234rf1234 Posts: 761Questions: 39Answers: 117

    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: 761Questions: 39Answers: 117

    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: 761Questions: 39Answers: 117

    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: 761Questions: 39Answers: 117
    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: 761Questions: 39Answers: 117

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

  • rf1234rf1234 Posts: 761Questions: 39Answers: 117
    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: 13Questions: 2Answers: 1

    @rf1234 Thank you so much for your full example.

  • jacek_kjacek_k Posts: 5Questions: 1Answers: 0

    @rf1234 is that works with IE?

  • rf1234rf1234 Posts: 761Questions: 39Answers: 117

    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: 5Questions: 1Answers: 0

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

  • rf1234rf1234 Posts: 761Questions: 39Answers: 117

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

  • rf1234rf1234 Posts: 761Questions: 39Answers: 117

    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: 761Questions: 39Answers: 117

    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: 48,430Questions: 1Answers: 7,007 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: 761Questions: 39Answers: 117

    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: 48,430Questions: 1Answers: 7,007 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

Sign In or Register to comment.