Buttons colvis `columns` option not working with multi-row header

lisarush

Link to test case:
Description of problem:

I am upgrading from DataTables 1.13.6 --> 2.1.8, Buttons 2.4.2 --> 3.1.2. I have tables with multi-row headers, some of which I wish to exclude from the "column visibility" list, e.g.:

      <table id="example" class="display nowrap" width="100%">
        <thead>
        <tr>
           <th rowspan="2">Name</th>
           <th rowspan="2" class="noColvis">Position</th>
           <th colspan="3">Values</th>
        </tr>
        <tr>
           <th>Val1</th>
           <th class="noColvis">Val2</th>
           <th>Val2</th>
        </tr>
        </thead>

        <tbody>
          <tr>
            <td>Tiger Nixon</td>
            <td>System Architect</td>
            <td>0.5</td>
            <td>Edinburgh</td>
            <td>61</td>
          </tr>
          <tr>
            <td>Garrett Winters</td>
            <td>Director</td>
            <td>0.5</td>
            <td>Edinburgh</td>
            <td>63</td>
          </tr>
          <tr>
            <td>Ashton Cox</td>
            <td>Technical Author</td>
            <td>0.5</td>
            <td>San Francisco</td>
            <td>66</td>
          </tr>
          <tr>
            <td>Cedric Kelly</td>
            <td>Javascript Developer</td>
            <td>0.5</td>
            <td>Edinburgh</td>
            <td>22</td>
          </tr>
        </tbody>
      </table>

My colvis button is configured as such:

new DataTable('#example', {
    layout: {
        topStart: {
            buttons: [
                {
                    extend: 'colvis',
                    columns: ':not(.noColvis)'
                }
            ]
        }
    }
});

When showing the list of columns to turn on/off, it is properly hiding the "Position" column (which is essentially in row 1), but "Val2" shows in the list. This used to work previous to the upgrade. Any help/pointers would be appreciated.

Thanks!
Lisa

allan

Hi Lisa,

Here is a live demo of that happening: https://live.datatables.net/difehevu/1/edit . It certainly looks like a bug I'm afraid. Let me get back to you shortly on that - I'll dig into it a bit more.

Allan

allan

Following up on this - the issue is that with the true multi-row header support in DataTables 2, it is now possible to select columns based on any of the cells that apply to them. So in this case the "Value" colspan cell applies to Val2 and results in that column being selected.

The workaround is to add class="noColvis" to the colspan cell: https://live.datatables.net/difehevu/2/edit

The API is implementing the behaviour I wanted there, so I don't see this as a bug as such, however I do see that it isn't what is expected here. I need to have a think about how to handle this in the API.

Is that workaround viable for you?

Allan

lisarush

Hey Allan,

Thanks for the quick response, as always.
I verified that adding noColvis to the other header rows does work. It would be easy to add this as a workaround. (If a tiny bit tedious; we have 68 places with noColvis, so I'd just have a walk thru them all & see if they need a tweak.)

That being said, it's not "obvious" that this would be expected behavior. Let me know if you have any other thoughts in the next few days... (Otherwise, I will then start tweaking our tables as needed.)

Thanks & happy weekend,
Lisa

lisarush

Any further thoughts on this? (I don't want to take the effort of updating all my tables if you end up with a better solution.)

allan

Hi Lisa,

No, sorry - no better option for this at the moment. It would almost certainly require a modification to the API (perhaps an option in selector-options to indicate what row the column selector should run on - similar to what table().header.structure() does).

The immediate fix so you add the class into the second row as well.

Allan

lisarush

OK, thanks.

lisarush

I'm having another issue that I believe is likely related. For the Excel export, I have the columns option set to :visible:not(.noExport). For tables that have a single-row header (or an individual header in a multi-row header than spans all rows), this is working fine: neither hidden columns nor .noExport columns are exported. However, if the table has a multi-row header, both "hidden" columns and .noExport columns are exported.

In the example below, column Position is correctly not exported, because its <th> spans all header rows. Val1 is always exported -- unless I also add .noExport to the parent row, similar to what we discussed above.

The bigger issue is I would like to be able to use the Column Visibility extension to dynamically turn columns off -- and then, not included in the export. This will be a show-stopper for me. (I had this all working through code modifications I had added to the previous release. I was hoping that your latest release supporting multi-row headers would now handle this & I could abandon my mods; much of your implementation is smoother than mine & I would have to rework my mods, but this is an important feature for me.)

   <table id="example" class="display nowrap" width="100%">
      <thead>
      <tr>
         <th rowspan="2">Name</th>
         <th rowspan="2" class="noExport">Position</th>
         <th colspan="3">Values</th>
      </tr>
      <tr>
         <th class="noExport">Val1</th>
         <th>Val2</th>
         <th>Val3</th>
      </tr>
      </thead>

      <tbody>
      <tr>
         <td>Tiger Nixon</td>
         <td>System Architect</td>
         <td>0.5</td>
         <td>Edinburgh</td>
         <td>61</td>
      </tr>
      <tr>
         <td>Garrett Winters</td>
         <td>Director</td>
         <td>0.5</td>
         <td>Edinburgh</td>
         <td>63</td>
      </tr>
      <tr>
         <td>Ashton Cox</td>
         <td>Technical Author</td>
         <td>0.5</td>
         <td>San Francisco</td>
         <td>66</td>
      </tr>
      <tr>
         <td>Cedric Kelly</td>
         <td>Javascript Developer</td>
         <td>0.5</td>
         <td>Edinburgh</td>
         <td>22</td>
      </tr>
      <tr>
         <td>Jenna Elliott</td>
         <td>Financial Controller</td>
         <td>0.5</td>
         <td>Edinburgh</td>
         <td>33</td>
      </tr>
      <tr>
         <td>Brielle Williamson</td>
         <td>Integration Specialist</td>
         <td>0.5</td>
         <td>New York</td>
         <td>61</td>
      </tr>
      <tr>
         <td>Herrod Chandler</td>
         <td>Sales Assistant</td>
         <td>0.5</td>
         <td>San Francisco</td>
         <td>59</td>
      </tr>
      </tbody>
   </table>

<script>
   new DataTable('#example', {
      layout: {
         topStart: {
            buttons: [
               {
                  extend: 'excelHtml5',
                  exportOptions: { columns: ':visible:not(.noExport)' }
               },
               {
                  extend: 'colvis',
                  columns: ':not(.noColvis)'
               }
            ]
         }
      }
   });
</script>

allan

Hi Lisa,

What is happening is that when selecting columns each node in the header is being considered. Therefore the colspaning element "Values" is being considered for each, which in this case is not desirable, but I think is correct overall.

The key is to construct a selector string that will pick out only the cells that you want, which, if I've understood correctly, is basically to ignore the Values column and use only those which have colspan=1.

That could be done with this selector:

exportOptions: { columns: '[colspan=1]:visible:not(.noExport)' }

https://live.datatables.net/difehevu/8/edit

Will that work for you?

Allan

lisarush

Initially, I thought this was going to work brilliantly...
By adding [colspan=1], this fixed both "issues" with the export: 1) only visible columns are exported AND 2) I only have to put .noExport on the individual column headers (and not the parent colspan'ed headers).

I was then hopeful this would also work for the columns setting for colvis and searchBuilder as well. But alas, no. It looks like I will still have to add .noColvis and .noSearchBuilder to parent header cells throughout my tables...

BUT... I started to add .noColvis to parent headers as needed, along with this new setting with [colspan=1]... however, I noticed that now the ordering of columns is not correct. Everything displays fine on the screen. In the example below:
* For ColVis, the "Location" column is listed out of order. (If I remove the .noColvis from just the parent header, they are listed in order, but listing all columns though.)
* When Export, the data values for "Location" show up under the "Val2" column and other columns are then off. (If I remove the [colspan=1] from the columns setting, they go back to being in order, but I loose the functionality of excluding non-visible columns.
(I haven't looked at SearchBuilder yet.)

   <table id="example" class="display nowrap" width="100%">
      <thead>
      <tr>
         <th rowspan="2">Name</th>
         <th rowspan="2" class="noExport">Position</th>
         <th colspan="3" class="noColvis">Values</th>
         <th rowspan="2">Location</th>
      </tr>
      <tr>
         <th class="noExport">Val1</th>
         <th class="noColvis">Val2</th>
         <th>Val3</th>
      </tr>
      </thead>

      <tbody>
      <tr>
         <td>Tiger Nixon</td>
         <td>System Architect</td>
         <td>0.5</td>
         <td>123</td>
         <td>61</td>
         <td>Edinburgh</td>
      </tr>
      <tr>
         <td>Garrett Winters</td>
         <td>Director</td>
         <td>0.5</td>
         <td>456</td>
         <td>63</td>
         <td>Edinburgh</td>
      </tr>
      <tr>
         <td>Ashton Cox</td>
         <td>Technical Author</td>
         <td>0.5</td>
         <td>789</td>
         <td>66</td>
         <td>San Francisco</td>
      </tr>
      <tr>
         <td>Cedric Kelly</td>
         <td>Javascript Developer</td>
         <td>0.5</td>
         <td>45678</td>
         <td>22</td>
         <td>Edinburgh</td>
      </tr>
      <tr>
         <td>Jenna Elliott</td>
         <td>Financial Controller</td>
         <td>0.5</td>
         <td>892</td>
         <td>33</td>
         <td>Edinburgh</td>
      </tr>
      <tr>
         <td>Brielle Williamson</td>
         <td>Integration Specialist</td>
         <td>0.5</td>
         <td>15-501</td>
         <td>61</td>
         <td>New York</td>
      </tr>
      <tr>
         <td>Herrod Chandler</td>
         <td>Sales Assistant</td>
         <td>0.5</td>
         <td>751</td>
         <td>59</td>
         <td>San Francisco</td>
      </tr>
      </tbody>
   </table>

<script>
   new DataTable('#example', {
      layout: {
         topStart: {
            buttons: [
               {
                  extend: 'excelHtml5',
                  exportOptions: { columns: '[colspan=1]:visible:not(.noExport)' }
               },
               {
                  extend: 'colvis',
                  columns: ':not(.noColvis)'
               }
            ]
         }
      }
   });
</script>

allan

That's happening because of the way the cells are selected. They come out in selection order (which is to go across each column and then down the rows). I've committed a fix to have CSS column selector operations return in column index order now.

I suspect the selector might not be working on initialisation because perhaps the colspan=1 attribute hasn't been added by that point.

This is the key line in DataTables for what is happening here. I'm wondering if I should introduce a new option to selector-modifier to select which row from the header the cells should be selected.

Then it would be something like:

exportOptions: {
  columns: ':visible:not(.noExport)',
  modifier: {
    headerRow: 1
  }
}

However that isn't going to solve the ColVis or SearchBuilder issues since they don't have a way to pass into selector-modifier.

Thinking hat still on...

Allan

allan

Here is an example with ColVis and Excel using the nightly build with the change mentioned above.

Allan

lisarush

Looks better! ...but... if you turn off columns "Val1" and "Val3" (Val2 is not in the ColVis list) --> it exports all 3 columns, where I would expect it to only export Val2.

lisarush

and while you're in __column_selector, I had to add a null check here:

            if ( match ) { ... }

             // ----- added null check
             if ( s == null ) {
                return [];
             }

             // Cell in the table body
             if ( s.nodeName && s._DT_CellIndex ) {
                return [ s._DT_CellIndex.column ];
             }

allan

null isn't currently a valid value for column-selector. What is the set of conditions that causes the selector to be null?

Allan

lisarush

I don't remember exactly. I added the null check early on, when one of the first tables I looked at had the issue. I thought it was when the contents of a cell was empty, but that doesn't look to be the case. I removed the null check & will watch for it popping back up.

Did you see my previous comment about the exporting not being correct?

allan

I did, sorry I didn't reply regarding that point - I needed to come back to look at it.

The [colspan=1] selector isn't right, since if one were to hide Val1 and Val3, then Values would match that selector, and thus select all columns that belong under it.

There is a different selector that can be used to determine if a cell belongs to multiple columns though - [data-dt-column*=","].

So I think the selector needed is :visible:not([data-dt-column*=","], .noExport).

Updated example.

Allan

lisarush

Good morning (here at least), getting back to this... (was out on Friday & then meetings)...

I don't think fixing the selector is quite enough... Here in this case, ColVis is listing the columns out of order -- and the export column headers are not in the same order as the data.

https://live.datatables.net/bemoregi/2/

allan

Doh - the sorting I'd applied didn't work for more than 9 columns (1, 10, 2, 3, etc).

Fix committed here and the example now works.

Thanks for letting me know about that.

Allan

lisarush

Awesome! This seems to work for the correct columns in the export. Thank you so much for your help!

I may be bugging you for other bits as I work through all of our scenarios. Getting ready to make a separate post for Responsive plugin, related to multi-row headers.

allan

Thanks so much for your help in tracking that stuff down! Really good to get that fixed. Most of the changes were in DataTables core - I'll plan to do a release of that fairly soon - probably next week.

Allan

lisarush

I found another case where ColVis is not showing correct options in its list. I have columns that I initially hide (for real estate reasons), but can then be turned on via ColVis as desired.

https://live.datatables.net/nakomine/1/

In this case, the 2nd column "Current State" is initially hidden via .hideInitial (see columnDefs setting) -- but it is then not listed in ColVis's list of columns that I can then turn on.

allan

Sorry - posted in the wrong thread - original comment here removed.

allan

Use the same selector to determine if a column is colspan-ing or not as is used in the Excel export. The colspan=1 isn't actually written to the element until it is placed into the document, thus it isn't being selected!

columns: ':not([data-dt-column*=","], .noColvis)'

I believe does what you are looking for: https://live.datatables.net/nakomine/2/edit .

Allan

lisarush

Thanks, Allan! I think I have everything resolved & all working with ColVis, Buttons (export & print), and SearchBuilder.