combine formating of export excel

Molu

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.

rf1234

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

rf1234

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

Molu

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

@rf1234

rf1234

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.