Multi-row headers + rowspan/colspan in xlsx headers

[chrisvwn]

Hi all,

This is an extension of lisarush 's and dfader 's code over at (https://datatables.net/forums/discussion/40854/how-to-add-second-footer-to-print-in-tfoot?) to get multiple headers to work in csv, xlsx, pdf and print.

I also have added a basic merge working for colspan and rowspan headers in xlsx. It does not work in firefox but does in chrome and opera browsers and I don't know why yet. Hopefully someone can help with that. It should be possible to port this (at least the logic) for the footer and body as well.

I am not sure if the rstudio/DT code is modified from the datatables.net code but I found some differences and the edit suggestions at the datatables.net forums had to be altered to work. So these changes should work directly in rstudio/DT and with modification at datatables.net. Anyway, hopefully, this will help somebody.

Disclaimer: I am not a javascript programmer and so this code is not tested, might have bugs and might affect the currently functioning DT code. Use at your own risk and feel free to edit the code as you see fit. Any questions, comments or critique are welcome.

A. Add multiple headers

1.In "dataTables.buttons.min.js" file

(You will need to deminify the files first)

a. Add this function: Insert in the section with m.background = function(... and similar code

    /* ----- BEGIN added Code ----- */
    /*Ref: https://datatables.net/forums/discussion/40854/how-to-add-second-footer-to-print-in-tfoot?*/
    getHeaders = function( dt ){
        var thRows = dt.nTHead.rows;
        var numRows = thRows.length;
        var matrix = [];
     
        // Iterate over each row of the header and add information to matrix.
        for ( var rowIdx = 0;  rowIdx < numRows;  rowIdx++ ) {
            var $row = $(thRows[rowIdx]);
     
            // Iterate over actual columns specified in this row.
            var $ths = $row.children("th");
            for ( var colIdx = 0;  colIdx < $ths.length;  colIdx++ )
            {
                var $th = $($ths.get(colIdx));
                var colspan = $th.attr("colspan") || 1;
                var rowspan = $th.attr("rowspan") || 1;
                var colCount = 0;
              
                // ----- add this cell's title to the matrix
                if (matrix[rowIdx] === undefined) {
                    matrix[rowIdx] = [];  // create array for this row
                }
                // find 1st empty cell
                for ( var j = 0;  j < (matrix[rowIdx]).length;  j++, colCount++ ) {
                    if ( matrix[rowIdx][j] === "PLACEHOLDER" ) {
                        break;
                    }
                }
                var myColCount = colCount;
                matrix[rowIdx][colCount++] = $th.text();
             
                // ----- If title cell has colspan, add empty titles for extra cell width.
                for ( var j = 1;  j < colspan;  j++ ) {
                    matrix[rowIdx][colCount++] = "";
                }
              
                // ----- If title cell has rowspan, add empty titles for extra cell height.
                for ( var i = 1;  i < rowspan;  i++ ) {
                    var thisRow = rowIdx+i;
                    if ( matrix[thisRow] === undefined ) {
                        matrix[thisRow] = [];
                    }
                    // First add placeholder text for any previous columns.                
                    for ( var j = (matrix[thisRow]).length;  j < myColCount;  j++ ) {
                        matrix[thisRow][j] = "PLACEHOLDER";
                    }
                    for ( var j = 0;  j < colspan;  j++ ) {  // and empty for my columns
                        matrix[thisRow][myColCount+j] = "";
                    }
                }
            }
        }
        return matrix;
    };
  /*END added code*/

b. Replace this code:

a = b.columns(c.columns).indexes().map(function(a) {
                        return c.format.header(b.column(a).header().innerHTML, a)
                    }).toArray()

with this code:

/*BEGIN ADD*/ headerMatrix = getHeaders( b.settings()[0] ) /*END ADD*/

c. Replace this code:

f = a.length

with this code:

f = headerMatrix[headerMatrix.length - 1].length

d. Then replace:

return {
                header: a,
                footer: g,
                body: j
            }

with:

return {
                header: headerMatrix,
                footer: g,
                body: j
            }

2.In "buttons.html5.min.js" file

a. Add multiple headers in xlsx
i. Find the section s.ext.buttons.excelHtml5 and the action: function
ii. Replace this code:

if(c.header){d(b.header,e);g("row c",f).attr("s","2")}

with this code:

for (i = 0; i < b.header.length; i++){
                  d(b.header[i], e);
                  g("row c", f).attr("s", "2"); //maybe should be outside the loop?
                }

b. Add multiple headers in pdf
i. Find the section s.ext.buttons.pdfHtml5 and the action: function
ii. Replace this code:

c.header&&b.push(g.map(a.header,function(a){return{text:typeof a==="string"?a:a+"",style:"tableHeader"}}));

with

if(c.header) /*CHANGE*/
            for(i = 0; i < a.header.length; i++)
            b.push(g.map(a.header[i], function(a) {
                return {
                    text: typeof a === "string" ? a : a + "",
                    style: "tableHeader"
                }
            }));

3.In "buttons.print.min.js"

c. Add multiple headers in print
i. Find the action: function
ii. Replace this code:

f.header&&(b += "<thead>" + a(c.header[i], "th") + "</thead>");

with

if(f.header)
    for(i = 0; i < c.header.length; i++)
        (b += "<thead>" + a(c.header[i], "th") + "</thead>");

B. Merge colspan/rowspan headers in xlsx

(Note: This will only work if you have gone through the earlier steps in part A)

1.Edit the "buttons.html5.min.js" file

a. Back in the s.ext.buttons.excelHtml5 section
b. Replace the code we added earlier for multiple headers:

for (i = 0; i < b.header.length; i++){
                  d(b.header[i], e);
                  g("row c", f).attr("s", "2"); //maybe should be outside the loop?
                }

with this code:

            if (c.header) {
                //for each header row
                for(i=0; i < b.header.length; i++)
                {
                  //for each column (cell) in the row
                  for(j=0; j<b.header[i].length; j++)
                  {
                    //look for a non-colspan/rowspan cell
                    if(b.header[i][j] != "" && b.header[i][j] != "")
                    {
                      var startRow = i;
                      var startCol = j;
                      var endRow = i;
                      var endCol = j;
                      
                      //console.log(i+":"+j+"="+b.header[i][j]);
                      
                      //lookahead
                      if(j+1 < b.header[i].length)
                      if(b.header[i][j+1] == "") //is the cell to the right a colspan?
                      { 
                        //console.log("cspan start:"+b.header[i][j]);
                        startCol = j;
                        endCol = j+1;

                        //get to the last column in the colspan
                        while(endCol < b.header[i].length &&b.header[i][endCol] == "")
                        {
                          //b.header[i][endCol] = ""; //Use if cspan is a special char/sequence
                          endCol++;
                        }
                        endCol--;
                      }
                      
                      if(i+1 < b.header.length)
                      if(b.header[i+1][j] == "") //is the cell below a rowspan?
                      {  
                        //console.log("rspan start:"+b.header[i][j]);
                        startRow = i;
                        endRow = i+1;

                        //get to the last row in the rowspan
                        while(endRow < b.header.length && b.header[endRow][j] == "")
                        {
                          //b.header[endRow][j] = ""; //Use if rowspan is a special char/sequence
                          endRow++;
                        }
                      }
                      
                      //create and store merge ranges
                      //if endCol or endRow show movement
                      if(startRow != endRow || startCol != endCol)
                      {
                        sC = colLetter(startCol); //convert startCol to excel column letter
                        sR = startRow+1;
                        eC = colLetter(endCol); //conver endCol to excel column letter
                        eR = endRow;
                        
                        //console.log("sC="+sC);
                        merges[mgCnt] = sC+""+sR; //start of range
                        
                        //console.log("endrow > startrow="+endRow+">"+startRow);
                        //console.log("endCol > startcol="+endCol+">"+startCol);

                        if(endCol > startCol) //end column
                          merges[mgCnt] = merges[mgCnt] + ":" + eC;
                        else
                          merges[mgCnt] = merges[mgCnt] + ":" + sC;
                        
                        if(endRow > startRow) //end row
                          merges[mgCnt] = merges[mgCnt] + eR;
                        else
                          merges[mgCnt] = merges[mgCnt] + sR;
                          
                        //console.log("merges[mgCnt]="+merges[mgCnt]);
                        
                        mgCnt++; //increment number of merge ranges
                      }
                    }
                  }
                }
                
                //add multiple headers
                for (i = 0; i < b.header.length; i++){
                  d(b.header[i], e);
                  g("row c", f).attr("s", "2"); //maybe should be outside the loop?
                }
            }

c. In between this code:

            for (var i = 0, l = b.body.length; i < l; i++) d(b.body[i], e);
            if (c.footer && b.footer) {
                d(b.footer, e);
                g("row:last c", f).attr("s", "2")
            }

and this code:

d = o(f, "cols");

add this code:

            //if we have merges
            if (mgCnt > 0)
            {
              //create a mergeCells section
              z = o(f, "mergeCells", {
                  attr: {
                      count: mgCnt,
                  }
              });
              
              //add each merge range as a child
              for(i=0;i<mgCnt;i++)
              {
                n = o(f, "mergeCell", {
                        attr: {
                          ref: merges[i]
                        }
                      });
                z.appendChild(n);
              }
            }
            if(z.children.length > 0)
              g("worksheet", f).append(z) //add to the worksheet

That's it. You're done!

[PawanAgarawal]

@chrisvwn Hi, Chris! Can you please share with me all these .js files that you have modified to fix multiple row header issue. It would be helpful.

Thank you!

[chrisvwn]

Hi @PawanAgarawal . Let me create a fork with the code.

[chrisvwn]

Hi @PawanAgarawal Apologies for the delay. Please find the forked code at https://github.com/chrisvwn/DT

[Forno90]

Thank you very much for the solution!

Can someone please share with me the files:

• dataTables.buttons.min.js
• buttons.html5.min.js
• buttons.print.min.js

with the multiple row header fix?

Thank you very much!

Paolo

[chrisvwn]

Hi @Forno90,

You can find them here https://github.com/chrisvwn/DT/tree/master/inst/htmlwidgets/lib/datatables-extensions/Buttons/js

[javg15]

@chrisvwn
Excellent!!!!
Thank you

[avinashvalluru]

@chrisvwn Excellent work
Is it possible to export multiple footer rows with col span and row span.
thanks in advance

[chrisvwn]

@javg15 You're welcome

@avinashvalluru Thanks. It should be possible to replicate the same in the footer by making similar changes in the appropriate sections.

[larssoneg]

This is a very well thought out solution. @yihui, are there plans to incorporate this into a future release of DT? The ability to preserve multi-row headers when exporting tables built with custom containers would be very useful.

[chrisvwn]

@larssoneg I believe @yihui is waiting for someone to adopt the task and make a pull request so that the changes can be incorporated.

I am not a javascript programmer myself and am afraid that I will soon run out of my depth which is why I have not made the pull request. For example I found this change does not work in at least one browser and I am unable to fix that. My hope is that a more skilled javascript programmer can take over this issue, bring the changes to an acceptable standard and see it through to incorporation. I am willing to give any assistance that may be required where I can. In fact, I would recommend rewriting the code to use a more modular approach to allow for easier improvements in future.

So I guess this is a call to javascript programmers - volunteers needed.

[betsyrosalen]

I second @larssoneg's comments above. If I had the skills to do this I would take it on myself, but unfortunately I am not even close. Not being able to export all the headers really makes those buttons unusable for the project that I was hoping to use them for... :-(

[MichaelC77]

The complex headers works perfect for tables with complex headers, however I ran into an issue when I tried to export tables that didn't have complex headers. The issue arrised with this section

/* Old */
if(z.children.length > 0)
g("worksheet", f).append(z)
d = o(f, "cols");
g("worksheet", f).prepend(d);
i = 0;

I was able to correct this error by changing the code to the following

/* New */
if(z.children)
g("worksheet", f).append(z)
d = o(f, "cols");
g("worksheet", f).prepend(d);
i = 0;

Instead of checking the length of the children attributes for greater than 0 it now just checks if any children exist which doesn't cause the error to raise anymore and works for both kinds of tables.

Good work on the solution @chrisvwn!

[chrisvwn]

Thanks and good catch @MichaelC77 !

[mtyszler]

Can't we simply merge @chrisvwn fork into this? His solution seems to work and would greatly benefit others by being taken onto the master repo.

[mtyszler]

Dear all. I took on the original suggestions by @chrisvwn , @MichaelC77 and what was discussed at https://datatables.net/forums/discussion/comment/106434/ and made some additional changes.

First of all, all changes are matching the current (latest) version of https://github.com/rstudio/DT.

Second it improves the getHeaders function to handle the hidden and (un)selected columns. The problem was that the initial code suggested by https://datatables.net/forums/discussion/comment/106434/ introduces two problems:

1. It ignores user selection of columns. For example if the user would pass exportOptions = list(columns = c(1,2) this would be ignored
2. it reads only the column headers shown on screen. If the user includes the colVis and hides a column, this column would be excluded from the header, but not from the body and footer, creating a misshaped output.

This version corrects that, and is more complete than the previous solution from @chrisvwn as it handles not only excel, pdf and print, but also the copy and csv functions.

Moreover, the merge colspan/rowspan suggested by @chrisvwn does not take into account message top and headers, and therefore has incorrect row numbers. My version corrects that.

It does not handle the flash counter parts of the html5 functions, as I understand the flash versions are being deprecated.

The latest files are available at https://github.com/mtyszler/DT/tree/feature/complex_headers

It changes:

• dataTables.buttons.min.js
• buttons.html5.min.js
• buttons.print.min.js

which can be found at: https://github.com/mtyszler/DT/tree/feature/complex_headers/inst/htmlwidgets/lib/datatables-extensions/Buttons/js

[chrisvwn]

Good work @mtyszler !

[Taufikismail]

hi @mtyszler i have a problem, i can't export footer.

[mtyszler]

hi @mtyszler i have a problem, i can't export footer.

Hi @Taufikismail . As you can see in the upstream PR DataTables/Buttons#170, I handled both complex headers and footers.

Since @yihui and @shrektan pointed out that it was best to handle it at origin, I did not expand/continued the work on #751.

I hope upstream can pick it up quickly. I suggest you add at comment at the PR DataTables/Buttons#170 and/or refer to @DataTables

[brainstorm1313]

I found a solution to replace header and add merged cells instead

  extend: 'excelHtml5',
   text: 'export to Excel',
   customize: function (xlsx) {

       //copy _createNode function from source
       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;
       }

       var sheet = xlsx.xl.worksheets['sheet1.xml'];
       var mergeCells = $('mergeCells', sheet);
       mergeCells[0].children[0].remove(); // remove merge cell 1st row

       var rows = $('row', sheet);
       rows[0].children[0].remove(); // clear header cell

       // create new cell
       rows[0].appendChild(_createNode(sheet, 'c', {
           attr: {
               t: 'inlineStr',
               r: 'B1', //address of new cell
               s: 51 // center style - https://www.datatables.net/reference/button/excelHtml5
           },
           children: {
               row: _createNode(sheet, 'is', {
                   children: {
                       row: _createNode(sheet, 't', {
                           text: 'new header text'
                       })
                   }
               })
           }
       }));


       // set new cell merged
       mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {
           attr: {
               ref: 'B1:E1' // merge address
           }
       }));

       mergeCells.attr('count', mergeCells.attr('count') + 1);

       // add another merged cell
   }

I didn't try, but messageTop option can add another row. It can be replaced too.

[alaaseada]

Thanks a lot @chrisvwn. It works just perfect.

[chrisvwn]

You're welcome @alaaseada You may want to use the more complete code for DT buttons by @mtyszler at #751.

[Navnath83]

im tring from last 2 weeks finally got solution thank you so much.... Chris Njuguna chrisvwn