≡

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

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

hjwoohjwoo Posts: 2Questions: 2Answers: 0

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

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 3,200Questions: 92Answers: 439
    Answer ✓

    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

  • rf1234rf1234 Posts: 3,200Questions: 92Answers: 439
    Answer ✓

    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.

  • allanallan Posts: 66,186Questions: 1Answers: 11,040 Site admin
    edited February 2025 Answer ✓

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

    Allan

  • GuirauteGuiraute Posts: 14Questions: 4Answers: 0

    Bonsoir Allan, je serai preneur avec grand plaisir

  • rf1234rf1234 Posts: 3,200Questions: 92Answers: 439

    C’est un boulot de bagnard, Guiraute :smile:

    I can understand that Allan is a bit reluctant to get this started.
    You might want to look at solutions like PHP spreadsheet that allow you to do almost anything with Excel on the server side. I am using this extensively for my reporting functions.

  • WernfriedWernfried Posts: 62Questions: 14Answers: 0
    edited 11:36AM

    With libraries like exceljs you can do it also on the client side. You can create a Custom button for it. Here a sample code to make a plain, simple export (generated by AI, not tested):

    bottomStart: {
        buttons: [
             'csvHtml5', 'pdfHtml5',
            {
               text: 'Excel',
               action: function (e, dt) {
                  const data = dt.buttons.exportData({ orthogonal: 'export' }); // { header, body, footer }
    
                  const wb = new ExcelJS.Workbook();
                  const ws = wb.addWorksheet('Sheet 1');
    
                  ws.addRow(data.header);
                  data.body.forEach(row => ws.addRow(row));
    
                  wb.xlsx.writeBuffer().then(buf => {
                     const blob = new Blob([buf], {
                        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                     });
                     const a = document.createElement('a');
                     a.href = URL.createObjectURL(blob);
                     a.download = 'DataTable-export.xlsx';
                     a.click();
                     URL.revokeObjectURL(a.href);
                  });
               }
            }
        ]
    }
    

    Then you don't have to deal with all these XML low-level stuff.

    Creating the Excel by yourself from scratch is easier than using the DataTables customize function, because you would need to serialize and deserialize, zip and unzip the xlsx object forth and back.

    Best Regards
    Wernfried

Sign In or Register to comment.