CODEWITHSUNDEEP
jquerydatatables
Saleem Khan
Saleem Khan

Reputation: 339

jQuery Datatable export to excel with styles

I have used jQuery Datatable and exporting an excel, Exporting works fine but how can I add style and formatting to the excel or maintain the Style of Datatable.

Thanks & Regards

Saleem Khan

Upvotes: 2

Views: 8321

Answers (2)

Marc
Marc

Reputation: 11

I had to create my own custom styles by scoping out the datatables.js file export button file in my project.

Here's my script:

buttons: [{
       //"colvis",
            extend: 'excelHtml5',
            text: 'Export to Excel',
            autoFilter: true,
            title: '',
            customize: function (xlsx) {
                var styles = xlsx.xl['styles.xml'];
            //custom font
           f1 = '<font><sz val="11" /><name val="Calibri" /><color rgb="FFFFFFFF" /><b /> 
                </font>';
           //custom colors
           //green 67
s1 = '<fill><patternFill patternType="solid"><fgColor rgb="C6E0B4" /><bgColor indexed="64" /></patternFill></fill>';
s2 = '<xf numFmtId="168" fontId="0" fillId="6" borderId="1" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1"><alignment horizontal="center"/></xf>';

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

 $('row c[r^="Z"]', sheet).attr('s', '67');
}]

Another reference can be found at this site: https://datatables.net/forums/discussion/45846/datatable-excel-export-how-can-we-apply-multiple-styles-to-same-cell

Upvotes: 1

Candice
Candice

Reputation: 92

You can use the build in styles. for example, 42 is Bold, green background, thin black border.

$('row:first c', sheet).attr( 's', '42');

The list of all the build in styles are here:

https://datatables.net/reference/button/excelHtml5#Built-in-styles

Upvotes: 1

Related Questions