jQuery datatable export to Excel customization - Make first row bold

Question

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

$('row:first c', sheet).attr( 's', '2' ); may work as well, I do not have it installed on this machine so I can not test. =/ — dale landry

andrewjames andrewjames · Accepted Answer · 2020-04-20T01:54:22

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:

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.

jQuery datatable export to Excel customization - Make first row bold

1 Answers