Different column layout for display vs export

Thad

Hi there,

I've got a table and it's fairly wide. Because of its width, we've chosen to combine some of the data. For example, the Address, City, State, and Zip variables are all presented in the same column:

[Address]
[City], [State] [Zip]

and similar for the daytime and evening phone numbers.

However, our users want those to be separate columns when they export to Excel or CSV.

At the moment, I'm handling it using the orthogonal attribute:

var spreadsheetOptions = {
  columns: [
    [...]
    'address:name',
    'city:name',
    'state:name',
    'postal:name',
    'workPhone:name',
    'homePhone:name',
    [...]
  ],
  rows: { selected: true },
  orthogonal: 'export'
};

[...]

var t = $('table').DataTable({

  [...]

  columns: [
    [...]
    {
      // Show Address<br/>City, State Zip on page/print,
      // just street address on spreadsheet export
      title: 'Address',
      name: 'address',
      data: {
        '_': function(data, type) {
          return type === 'export'
          ? data.address
          : data.address + ' <br/>' + data.city + ', ' + data.state + ' ' + data.postal;
        },
        sort: 'postal'
      },
      className: 'address'
    },{
      // City column for spreadsheet export
      title: 'City',
      name: 'city',
      data: 'city',
      className: 'hidden-modal',
      visible: false
    },{
      // State column for spreadsheet export
      title: 'State',
      name: 'state',
      data: 'state',
      className: 'hidden-modal',
      visible: false
    },{
      // Zip column for spreadsheet export
      title: 'Zip',
      name: 'postal',
      data: 'postal',
      className: 'hidden-modal',
      visible: false
    },{
      // Show Day<br/>Evening phone number as links on page/print
      title: 'Day<span class="visible-modal-inline">:</span> <br />Evening',
      name: 'phone',
      data: function(data) {
        var phones = '-';
        if(data.workPhone) {
          var phoneLink = parsePhoneLink(data.workPhone),
            phoneDisplay = parsePhoneDisplay(data.workPhone);
          phones = '<a href="tel:' + phoneLink + '">' + phoneDisplay + '</a>';
        }
        phones += ' <br/>';
        if(data.homePhone) {
          var phoneLink = parsePhoneLink(data.homePhone),
            phoneDisplay = parsePhoneDisplay(data.homePhone);
          phones += '<a href="tel:' + phoneLink + '">' + phoneDisplay + '</a>';
        } else {
          phones += '-';
        }
        return phones;
      },
      className: 'phone'    // no num-fmt because sometimes '-'; should center-align
    },{
      // Daytime Phone column for spreadsheet export
      title: 'Daytime Phone',
      name: 'workPhone',
      data: function(data, type) {
        if(type === 'export' && data.workPhone) {
          return parsePhoneDisplay(data.workPhone);
        }
        return '-';
      },
      visible: false,
      className: 'hidden-modal'
    },{
      // Evening Phone column for spreadsheet export
      title: 'Evening Phone',
      name: 'homePhone',
      data: function(data, type) {
        if(type === 'export' && data.homePhone) {
          return parsePhoneDisplay(data.homePhone);
        }
        return '-';
      },
      visible: false,
      className: 'hidden-modal'
    }
    [...]
  ],
  [...]
  buttons: [{
    extend: 'excel',
    exportOptions: spreadsheetOptions
  },{
    extend: 'csv',
    exportOptions: spreadsheetOptions
  }]
});

The downside to this approach is that I'm reading the city, state, postal, workPhone, and homePhone values in twice, and adding them all to the DataTables object in two different places. This has a noticeable impact on performance for larger tables (and some of our users have thousands of rows in their tables).

Is there another way of doing this? Is there a way I can read those values in once, and then combine multiple values into a single column in the browser but put them in separate columns on export?

Thanks.

tangerine

Where is your data coming from?
From a database, you could use "CONCAT_WS(',', fieldA, fieldB, etc) AS
concatenated" while also returning all fields individually. That gives you the data you need and improved response time.
I can't help you from that point though!

Thad

I don't think the response time from the server is the issue so much as the time generating the extra (hidden) columns.

I'm getting the data from the backend as a JSON object; adding the hidden city, state, zip, workPhone, and homePhone columns substantially increases table load time on large tables, without changing the content or format of the JSON data it's parsing.

allan

If you use the columns option of the excelHtml5 button type (or whichever other button), you will be able to define the order there - e.g. columns: [ 2, 1, 0 ].

Allan

Thad

Thanks Allan,

I'm already using columns (see lines 2-11 in the code block above); the problem is that I'm getting a noticeable performance hit when I do. I think what's happening is that the values are being read to two different places in the DataTable -- for example, I have a column for display on the HTML page that shows "[Street Address]<br/>[City], [State] [Zip]", and then a [City] column, a [State] column, and a [Zip] column. What I'm wondering is, is there a way to read in those variables, and store them in the DataTable, only once, but organize the columns differently in the browser than in the export?

I hope that's clear; I gather this is an unusual request.

Unfortunately I can't just separate out the columns on the page, which would be the easiest solution, because the table has a lot of columns, and splitting them up any more than they already are would make it too wide for the whole thing to fit in the viewing area.

allan

DataTables does what you describe already - it will read the values from the DOM into a cache for each row and then access that cache whenever it needs the value in Javascript.

If you have a link to the page showing the performance issue I can profile it and try to see what is going wrong.

Thanks,
Allan

Thad

It's getting the data via AJAX/JSON, not the DOM; would that make any difference?

It's on an internal website, but I'll see if I can work on a mockup version that I can share.

allan

It shouldn't DataTables will still retain a cache of the data, regardless of where it comes from.

Yes, if you could provide an example that would be great.

Allan

Thad

Okay, I've reworked it into something I can share as a jsfiddle.

It's a bit of a Frankenstein's Monster of functions from several different files; sorry about that. It also populates the table with random numbers, similar to your 50,000 rows example but random instead of sequential; since we're looking at loading time here I didn't want to feed the table a set of data that was already sorted.

There are two buttons at the top of the page, "Combined Columns Page & Export" and "Combined Columns Page; Separate Columns Export". Both buttons use the same set of data and use it to generate a DataTable. The DataTable has HTML5 export buttons for Excel and CSV; they export whatever rows are selected. (FYI, the version on the real site has Select All/Select None buttons and grays out the export buttons when no rows are selected, but I figured those features were unnecessary for our purposes here so I didn't copy them over.)

Clicking the Combined Page/Export button creates the following columns:

Launch Modal
Checkbox (indicates whether row is selected)
Read/Unread (hidden; assigns a CSS class to the row if true)
Name
Address
Phone Numbers
Age
Media
Language
Received
Assigned
Letter

and exporting to Excel or CSV will output all the columns from "Name" on.

Clicking the Combined Page/Separate Export button adds five more columns that are set as visible: false and only appear on export:

City
State
Zip
Day Phone
Evening Phone

(The "both phones" column does not show on export, and the "Address" column uses orthogonal and only shows the street address, leaving city, state, zip for their own columns.)

I've set the script to generate 30,000 rows; this can be changed by modifying the numRows variable right at the top of the document. The reason that I picked 30,000 is that that's a point where it's clear that the Combine/Separate version of the table takes longer to load (a couple of extra seconds in my tests). If you don't notice a difference, try increasing numRows.

Lastly, and this is unrelated but you probably want to look at it: there appears to be a bug in Responsive v2.1.1; if you open the responsive modal, you'll see that every value is off by one -- the "Name:" column gives the value for the "Read/Unread" column, the "Address" column gives the value for the "Name" column, and so on down the line. (This is easiest to spot in "Age", which lists the two phone numbers, and "Received", which gives the language.) Reverting to Responsive v2.0.2 fixes the issue.

Thanks for looking at this! Let me know what you figure out.

allan

If you enable the deferRender option it makes a significant difference to the initialisation of the table: https://jsfiddle.net/m97ah5v4/1/ . That's because it no longer has to create all of the nodes up front.

Its early in the morning here, so I feel like I've missed something, but does that address the issue?

Other than the Responsive issue...

Allan

Thad

Looks like deferRender does the trick; thanks!

ETA: Ah, now I remember why I wasn't using deferRender: it really does a number on automatic column sizing. (Try widening the viewport to lg and you'll see what I mean; the Phone column ends up needlessly wide and the Address column extremely narrow.)

I think I should be able to fix that by forcing column widths in CSS; I'll let you know if I have any problems with that.

allan

What's really odd about that is that deferRender should has no effect on the column widths. DataTables' column sizing is string based (using the DOM was way too slow). I don't have an immediate explanation for that I'm afraid.

Allan