combine formating of export excel

combine formating of export excel

MoluMolu Posts: 16Questions: 3Answers: 0

f1 = '<font><sz val="12" /><name val="Calibri" /><color rgb="00000" /><b /></font>';
s1 = '<fill><patternFill patternType="solid"><fgColor rgb="213e82" /><bgColor indexed="64" /></patternFill></fill>';
s2 = '<xf numFmtId="168" fontId="1" fillId="6" borderId="1" applyFont="1" applyFill="1" xfId="0" applyAlignment="1"><alignment horizontal="center"/></xf>';
styles.childNodes[0].childNodes[1].innerHTML += f1;
styles.childNodes[0].childNodes[2].innerHTML += s1 + f1;
styles.childNodes[0].childNodes[5].innerHTML += s2;
$('row:first c', sheet).attr( 's', '67' );

code is not working, please help.

Answers

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

    In that code you are not using the fonts, fills and styles you are defining above. So what exactly doesn't work?

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited September 2022

    There is something wrong in this line:

    styles.childNodes[0].childNodes[2].innerHTML += s1 + f1;
    

    You are adding a fill and a font to the fills. That shouldn't work.

    number formats, fonts, fills and borders are input for styles.

    In this code you see the following:
    - the last xf-index, i.e. the index of the stlyes is saved and the
    - last font index is saved too.
    - A new font is defined
    - a new number format is defined
    - then four new styles are defined that are (also) based ont he new font and the new number format.
    - names are assigned to the four new styles, so that those names can be used instead of using a number

    // see built in styles here: https://datatables.net/reference/button/excelHtml5
    // take a look at https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.js , search for "xl/styles.xml"
    //styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
    //styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
    //styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
    //styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
    //styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
    //styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
    //on the last line we have the 67 currently built in styles (0 - 66), see link above
    
    var sSh = xlsx.xl['styles.xml'];
    var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
    var lastFontIndex = $('fonts font', sSh).length - 1; 
    var f1 = //bold and underlined font
    '<font>'+
            '<sz val="11" />'+
            '<name val="Calibri" />'+
            '<b />'+'<u />'+
    '</font>'
    
    //n1, n2 ... are number formats; s1, s2, ... are styles
    var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
    var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
    //define the style with the new font (number passed in as a variable)
    var s2 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                '<alignment horizontal="center"/></xf>';         
    //s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
    //just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
    var s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
    var s4 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                '<alignment horizontal="center" wrapText="1"/></xf>'
    sSh.childNodes[0].childNodes[0].innerHTML += n1;  //new number format
    sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
    sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4; //new styles
    
    var fourDecPlaces    = lastXfIndex + 1;
    var greyBoldCentered = lastXfIndex + 2;
    var twoDecPlacesBold = lastXfIndex + 3;
    var greyBoldWrapText = lastXfIndex + 4;
    
    var 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
    
  • MoluMolu Posts: 16Questions: 3Answers: 0

    due to above code, excel got corrupts. http://live.datatables.net/xoxerowi/3/edit

    @rf1234

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

    No, it doesn't. I used your test case and got a non-corrupted Excel export file - even though the code probably doesn't make a lot of sense in the context of your test case.

Sign In or Register to comment.