Multi-row headers + rowspan/colspan in xlsx headers #418

chrisvwn · 2017-05-23T20:48:45Z

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!

The text was updated successfully, but these errors were encountered:

PawanAgarawal · 2017-06-21T14:03:52Z

@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 · 2017-07-05T05:44:35Z

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

chrisvwn · 2017-07-31T10:30:23Z

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

…lspan in xlsx headers

Forno90 · 2019-06-05T13:08:38Z

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 · 2019-06-05T13:54:33Z

Hi @Forno90,

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

javg15 · 2019-07-07T00:56:48Z

@chrisvwn
Excellent!!!!
Thank you

avinashvalluru · 2019-07-12T09:57:12Z

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

chrisvwn · 2019-07-12T10:17:37Z

@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 · 2019-09-25T20:22:56Z

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 · 2019-09-28T12:05:29Z

@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 · 2019-12-11T15:59:03Z

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 · 2019-12-18T15:43:37Z

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 · 2019-12-19T08:35:50Z

Thanks and good catch @MichaelC77 !

mtyszler · 2020-01-02T20:10:39Z

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.

…g changes from https://github.com/chrisvwn/DT/blob/master/inst/htmlwidgets/lib/datatables-extensions/Buttons/js/dataTables.buttons.min.js

…g changes from https://github.com/chrisvwn/DT/blob/master/inst/htmlwidgets/lib/datatables-extensions/Buttons/js/buttons.html5.min.js

…g with minor adjustments changes from https://github.com/chrisvwn/DT/blob/master/inst/htmlwidgets/lib/datatables-extensions/Buttons/js/dataTables.buttons.min.js, which is inpired by https://datatables.net/forums/discussion/comment/106434/#Comment_106434 This prepares the dataTables.buttons.min.js to handle multiple row headers. Without further adjustments this change actually create problems for all output functions, which expect a single row header instead of an array.

…justments to what is suggested. This alloes the output function 'Print' to handle multiple row headers

…n commit ed15f2f allowing the outputfunction pdfHtml5 to print pdf with multi-row headers

…n commit ed15f2f improves the outputfunction excelHtml5 to export excel files with multi-row headers, without the merges. On top of what is described rstudio#418, it makes other modifications needed to match the latest rstudio/DT version.

…n commit 6b56d58 and process the col/row span information to merge the cells at the excel output via the function excelHtml5

…roduced on ed15f2f. Here the main improvements are: * It includes all headers, including the ones which are hidden using colvis, for example. This if done via using aoHeader instead of nThead, and then using the cellIndex property instead of the colspan of the shown table on screen. * After extracting a complete rawHeaderMatrix, it process it to remove hidden columns and format the content. (similar to the original single row header code)

mtyszler · 2020-01-06T09:23:13Z

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:

It ignores user selection of columns. For example if the user would pass exportOptions = list(columns = c(1,2) this would be ignored
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 · 2020-01-06T20:43:07Z

Good work @mtyszler !

Taufikismail · 2020-06-28T22:37:22Z

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

mtyszler · 2020-06-29T06:45:24Z

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 · 2020-07-17T13:56:01Z

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 · 2021-05-04T12:11:28Z

Thanks a lot @chrisvwn. It works just perfect.

chrisvwn · 2021-05-05T07:03:04Z

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

Navnath83 · 2022-04-06T08:30:32Z

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

chrisvwn added a commit to chrisvwn/DT that referenced this issue Jul 31, 2017

rstudio/DT Issue rstudio#418: Suggested multirow headers + rowspan/co…

31b76b2

…lspan in xlsx headers

chrisvwn added a commit to chrisvwn/DT that referenced this issue Jul 31, 2017

rstudio/DT Issue rstudio#418: Suggested multirow headers + rowspan/co…

619e973

…lspan in xlsx headers

occultskyrong mentioned this issue Aug 30, 2018

dataTables导出excel，复杂表头显示不全 ssy341/datatables-cn#380

Closed

mtyszler added a commit to mtyszler/DT that referenced this issue Jan 6, 2020

According to rstudio#418, improve buttons.print.min.js, with minor ad…

8e9357c

…justments to what is suggested. This alloes the output function 'Print' to handle multiple row headers

mtyszler added a commit to mtyszler/DT that referenced this issue Jan 6, 2020

According to rstudio#418, improve buttons.html5.min.js. This builds o…

bfa1548

…n commit ed15f2f allowing the outputfunction pdfHtml5 to print pdf with multi-row headers

mtyszler added a commit to mtyszler/DT that referenced this issue Jan 6, 2020

According to rstudio#418, improve buttons.html5.min.js. This builds o…

f7742c0

…n commit 6b56d58 and process the col/row span information to merge the cells at the excel output via the function excelHtml5

mtyszler linked a pull request Jan 6, 2020 that will close this issue

Feature/complex headers #751

Open

mtyszler mentioned this issue Jan 27, 2020

Feature/complex headers and footers DataTables/Buttons#170

Closed

shrektan added the enhancement label Feb 1, 2020

shrektan mentioned this issue Feb 1, 2020

Unable to have Complex headers export to Excel and csv #764

Closed

3 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Multi-row headers + rowspan/colspan in xlsx headers #418

Multi-row headers + rowspan/colspan in xlsx headers #418

chrisvwn commented May 23, 2017 •

edited

PawanAgarawal commented Jun 21, 2017

chrisvwn commented Jul 5, 2017

chrisvwn commented Jul 31, 2017

Forno90 commented Jun 5, 2019

chrisvwn commented Jun 5, 2019

javg15 commented Jul 7, 2019

avinashvalluru commented Jul 12, 2019

chrisvwn commented Jul 12, 2019 •

edited

larssoneg commented Sep 25, 2019

chrisvwn commented Sep 28, 2019

betsyrosalen commented Dec 11, 2019

MichaelC77 commented Dec 18, 2019

chrisvwn commented Dec 19, 2019

mtyszler commented Jan 2, 2020

mtyszler commented Jan 6, 2020 •

edited

chrisvwn commented Jan 6, 2020

Taufikismail commented Jun 28, 2020

mtyszler commented Jun 29, 2020

brainstorm1313 commented Jul 17, 2020

alaaseada commented May 4, 2021

chrisvwn commented May 5, 2021

Navnath83 commented Apr 6, 2022

Multi-row headers + rowspan/colspan in xlsx headers #418

Multi-row headers + rowspan/colspan in xlsx headers #418

Comments

chrisvwn commented May 23, 2017 • edited

A. Add multiple headers

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

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

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

B. Merge colspan/rowspan headers in xlsx

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

PawanAgarawal commented Jun 21, 2017

chrisvwn commented Jul 5, 2017

chrisvwn commented Jul 31, 2017

Forno90 commented Jun 5, 2019

chrisvwn commented Jun 5, 2019

javg15 commented Jul 7, 2019

avinashvalluru commented Jul 12, 2019

chrisvwn commented Jul 12, 2019 • edited

larssoneg commented Sep 25, 2019

chrisvwn commented Sep 28, 2019

betsyrosalen commented Dec 11, 2019

MichaelC77 commented Dec 18, 2019

chrisvwn commented Dec 19, 2019

mtyszler commented Jan 2, 2020

mtyszler commented Jan 6, 2020 • edited

chrisvwn commented Jan 6, 2020

Taufikismail commented Jun 28, 2020

mtyszler commented Jun 29, 2020

brainstorm1313 commented Jul 17, 2020

alaaseada commented May 4, 2021

chrisvwn commented May 5, 2021

Navnath83 commented Apr 6, 2022

chrisvwn commented May 23, 2017 •

edited

chrisvwn commented Jul 12, 2019 •

edited

mtyszler commented Jan 6, 2020 •

edited