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

rf1234

Got it working. Here is a screenshot of part of the resulting Excel sheet:

I changed style "s2" to be "greyBoldCentered" and underlined.

And this is the code:

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

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"/>';
//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

ibrahimayhan

thank you for informing,

{
extend: 'excel',
text: 'Testing Excel',
customize: function (xlsx, row) {
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row c[r^="L"]', sheet).attr('s', 57);
}
}
Which format should I use as 5,3316 in Turkish lira format?

arunraja

How to find the first row of the result array in customize

rf1234

Just copied this from one of my previous posts above:

//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('');
}

This is copying and pasting something from column B to column A of the second last row. If you wanted to do the same with the first row you would need to use $('row:eq(0) ....

Just take a look at the more complete code examples in the posts above.

Ashwarthini

Hello sir @rf1234 ,

Is it possible to to give any working example(like fiddle or something else) to add both bold and center to a same cell in excel export

Jdavid198

Hola amigos que tal quería saber como Hago para cambiar el tamaño de la letra
en esta estoy intentado hacer una combinacion de negrita centrada pero no se como cambierle el tamaño.

var s2 = '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="30" applyFill="10" applyFontSize="18" val ="50" applyBorder="1" xfId="0" applyAlignment="4">'+
'<alignment horizontal="center"/></xf>';

colin

@Jdavid198 This thread should help, it's asking the same thing.

Cheers,

Colin

MagicViolet

Please, does someone know how to apply style for the second row in the exel table? I tried this:
$('row c[r*="2"]', sheet).attr('s', '36');
But it takes the 12th row too...

colin

@MagicViolet : Try:

$('row:nth-child(2) c', sheet).attr('s', '36');

Please see example here,

Colin