DataTables Excel Export - Customize Border & Background Color for Merged Headers

hjwoo

Question:
I am using DataTables to export data to Excel and customizing the exported file using the customize function in excelHtml5. I want to apply a border and background color to all cells, including merged headers (complex headers), but the border and background color do not appear on the merged header cells.

url: https://live.datatables.net/repepazu/1/edit
code:

new DataTable('#example', {
    layout: {
        topStart: {
            buttons: ['copyHtml5',   
                   {
                    extend: 'excelHtml5',
                    customize: function (xlsx) {
                      var sheet = xlsx.xl.worksheets['sheet1.xml'];
                      var mergeCells = $('mergeCells', sheet);
                      mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {attr: { ref: ['B2:C2'] }}));
                      mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {attr: { ref: ['D2:F2'] }}));
                      mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {attr: { ref: ['B3:D3'] }}));
                      mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {attr: { ref: ['E3:F3'] }}));
                      
                      mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {attr: { ref: ['A2:A3'] }}));
                        
                      $('row[r=1] c', sheet).attr('s', '47');
                      $('row[r=2] c', sheet).attr('s', '47');
                      $('row[r=3] c', sheet).attr('s', '47');
                      function _createNode(doc, nodeName, opts) {
                          var tempNode = doc.createElement(nodeName);
                          if (opts) {
                            if (opts.attr) {
                              $(tempNode).attr(opts.attr);
                            }

                            if (opts.children) {
                              $.each(opts.children, function(key, value) {
                                tempNode.appendChild(value);
                              });
                            }

                            if (opts.text !== null && opts.text !== undefined) {
                              tempNode.appendChild(doc.createTextNode(opts.text));
                            }
                          }

                          return tempNode;
                        }
                      
                        // Selector to add a border
                         sheet.querySelectorAll('row c').forEach((el) => {
                           el.setAttribute('s', '25');
                         });
                    }
                }, 
                'csvHtml5', 'pdfHtml5']
        }
    }
});

rf1234

I commented out some of your code - and the background color change started working. Just take a look please.

https://live.datatables.net/luriyidu/1/edit

rf1234

I see your problem. You are adding styles sequentially. First 47 and then 25. That doesn't work. 25 just overwrites 47. And that makes custom formatting so difficult.

You will need to create and apply your own style which combines 47 and 25 as one proprietary style.

Can be very cumbersome. You'll find posts from me in the forum where I combine multiple styles into one. One is called "greyBoldCentered" to give you an idea of what this is all about.

allan

Styles in Excel are an absolute PITA. One day I'll get around to writing some kind of abstraction for it. One day...

Allan