Search code examples
jquerydatatablesexport-to-excel

jQuery datatable export to Excel customization - Make first row bold

I have a datatable with export to excel button I am trying to customize. I looked around to see how to customize and found a few but still having problem with this.

When you export to excel the first row seems to be page title. I am trying to make this row bold; and if possible replace the text with something else other than the page title.

Here is what I saw and tried to modify to achieve what I need:

buttons: [
{
    extend: 'excelHtml5',
    customize: function ( xlsx ){
        var sheet = xlsx.xl.worksheets['sheet1.xml'];

        // jQuery selector to add a border
        //$('row c[r*="0"]', sheet).attr( 's', '25' );
        //$('row c[r^="C"]', sheet).attr( 's', '2' ); // third column is bold

        // Trying to make first row bold, this makes last row bold
        $('row c[r*="0"]', sheet).attr( 's', '2' );
        $(sheet.body)
            .css('font-size', '10pt');

        $(sheet.body).find('table')
            .addClass('compact')
            .css('font-size', 'inherit');
    },
    exportOptions: {
        columns: [0, 1, 2]
    },
},

Also, when applying formatting it seems we use 'attr' with first parameter 's' and a number. How do I find out that 25 implies border and 2 implies bold?

I don't understand how to manipulate the following to effect styling changes:

    $('row c[r*="0"]', sheet).attr( 's', '2' );
    $('row c[r^="C"]', sheet).attr( 's', '25' );
Solution

  • Assuming you have the buttons extension installed, then the following will print a custom title in the first row of the spreadsheet, and it will make that title bold:

    $(document).ready(function() {

  $('#myTable').DataTable( {
    dom: 'Bfrtip',
    buttons: [
        {
          extend: 'excel',
          customize: function ( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            $('c[r=A1] t', sheet).text( 'Custom Heading in First Row' );
            $('row:first c', sheet).attr( 's', '2' ); // first row is bold
          }
        }
    ]
  } );

});

    Example:

    enter image description here

    Regarding the pre-defined formats you mentioned, you can see a list of them here.

    0 - Normal text
1 - White text
2 - Bold
3 - Italic
4 - Underline
etc...

    The custom commands to manipulate the exported data are also summarized in the same page. But you would need to refer to SheetJS for more details, I think.