Shiny Datatables : Multi-row headers + rowspan/colspan in xlsx headers

This is a writeup on some code (read hack) that we had to put together quickly to solve a problem. That is exporting data with multiple headers and merged rows/columns in the headers from an R Shiny datatable. Shiny datatables (Shiny/DT) which is based on (datatables.net) is a fantastic way to quickly display tabular data in an R Shiny web application and to export it to multiple formats. The changes required to try out the code are posted on the shiny/DT github issues page (here) and we thought we would just publish here for anyone who might want to give it a go and maybe even expand on it.

---

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! Some screenshots:

The datatable in Shiny

The datatable exported to .xlsx

The datatable exported to .pdf

The datatable print preview