Buttons Extension: Formatting data before export

vol7ron

Question

---

Is there an event hook to customize the data used by the custom button formats (e.g., csv, excel, pdf)?

Setup / Example

I've created a JSBin, which imports all of the available DataTables plugins. Here, I create a table and add subtotals on the drawCallback. However, those subtotals are not included when exporting the data - I'm guessing because the row() method was not used to add the subtotals. This is okay because I always want the subtotals to show on the webpage, but not necessarily be included with exported datasets. I don't think subtotals should be considered data rows, since that would affect sorting.

Example of Buttons API

When trying to format the exported data, I came across the aptly named buttons.exportData() method of the buttons api. However, I'm not sure how it's intended to be used. For instance, let's say I only want to add decimals when exporting to CSV; see the my custom addDecimalsToSalary() function, which simply iterates over the data and adds a string to the value. However, I don't know how to pass that created data to the csv call, so it can be downloaded by the user. Is there is a hook that I need to use?

Desired Outcome

What I want to do is be able to format the data based on certain output. For instance, if I click on excel, I want to be able to include the subtotals. If I click on csv I don't want to include the subtotals, but I may want to hide certain fields, or format them a particular way.

Perhaps none of this is possible, but the documentation has left me a little confused.

Code from JSBin (linked above)

jQuery(document).ready( function ($) {

  var table = $('#example').DataTable({
    paging: false,
    info: false,
    order: [[0,'asc']],
    dom: 'B<"clear">lfrtip',
    buttons:[
      'copy','csv','excel'
    ],
    drawCallback: addSubtotals
  });

  var export_data = table.buttons.exportData({
    format: {
      body: addDecimalsToSalary
    }
  });  
  
});



// Example: add decimals to salary for export
function addDecimalsToSalary(data, columnIndex){
  if (columnIndex === 5)
     return data + '.00';
  return data;
}


// Example: add subtotals by letter of first name
function addSubtotals(settings){
  var api = this.api(),
      rows = api.rows({ page: 'current' }),
      cols = api.columns({ page: 'current' }),
      last = null,
      next = null,
      agg  = {},
      sum_colNum = 5;
  
  // Iterate over the first column (used to determine if it is a group)
  api.column(0, {page: 'current'}).data().each(function( text, rowNum, stack ){

    var current_row = rows.data()[rowNum];
    var next_rowNum = rowNum + 1;
    var group       = text.split('')[0].toLowerCase();
    next = stack[ next_rowNum ] && stack[ next_rowNum ].split('')[0].toLowerCase();

    // if the current group is the same as the next group, add the numbers together    
    if (group===next){
      agg[group] = agg[group] || {};
      agg[group][sum_colNum] = agg[group][sum_colNum] || 0;
      agg[group][sum_colNum] += parseCurrency( current_row[sum_colNum] );
    }
    
   // if the current group is not the same as the next, but is the same as the last (last row 
   // of the group), then add the numbers and create the subtotal row
    else if (group===last){
      agg[group] = agg[group] || {};
      agg[group][sum_colNum] = agg[group][sum_colNum] || 0;
      agg[group][sum_colNum] += parseCurrency( current_row[sum_colNum] );

      var $subtotal = $('<tr></tr>', {'class': 'subtotal', 'data-group':group});
      
      // create a cell for each table heading
      cols.header().each(function(el, colNum){
        var $td = $('<td></td>');
        if (colNum === sum_colNum) {
          $td.text( formatCurrency(agg[group][sum_colNum]) );
        } else if (colNum === 0) {
          $td.text( ["Subtotal ('" , group , "')"].join('') );
        }
        $subtotal.append($td);
      });
      
      $(rows.nodes()).eq(rowNum).after($subtotal);
    }
    
    last = group;
  });
  
}

// Used so currency numbers can be added w/o dollar signs in the way
function parseCurrency(val){
  val += '';
  return parseFloat( val.replace(/[$,]/g,'') );
}

// Used to display currency again
function formatCurrency(val) {
  return parseCurrency(val).toLocaleString('US', {
    'style': 'currency',
    'currency': 'USD'
  });
}

vol7ron

I think I found what I was looking for:

outputFormat-function
outputFormat-orthagonal

That being said, I'm curious if the documentation should be updated. I didn't find anything related to the export on any of the extension or reference pages:

Extension: Buttons
Extension: Buttons-Config
Extension: Buttons-Built-in
Extension: Buttons-Custom
Reference: Option:Buttons
Reference: API:Buttons
Reference: Buttons

I did find them on the examples pages; which, I'm glad because examples are some of the best way to learn things quickly:

Extension: Buttons-Examples

allan

That's probably the best Markdown formatted post we've ever had in this forum - nice one :-)

And good to hear that you found the two formatting options for output data control. There is a reference to the buttons.exportData() method in the buttons reference documentation (csv for example), but it isn't great - it still leaves a number of dots to be connected. I think this topic will probably be the next Buttons manual page to be created as it does come up from time to time.

Thanks,
Allan

vol7ron

The buttons are an amazing feature and documentation is tough. Fortunately, DataTables has a lot of documentation and examples, which makes it a recommended plugin.

Regarding the formatting, the markdown updates you've included in the forum (and new styling of the website) are amazing. New may be a bit exaggerated, but I can remember what it looked like years ago. It all feeds into a better UX.

To digress: there was one small bug in the markdown when including a link as a header. For instance: ### [Header Link](http://...) should work, but it seemed to have some parsing issues; see below:

---

Header Link

---

As always, thanks for providing, maintaining, and supporting such a great tool

allan

Interesting - thanks for pointing that out. And thanks for your kind words :-). Hopefully more improvements to come (specifically in the search).

Allan

vol7ron

Please correct me where wrong

I've been able to look at the exportOptions formatting. I couldn't find too much information, but from what I found, the formatting is row specific. Meaning, there's the cell value, the column number, and the row number that is supplied to the formatting function, but the api and other datatable related objects are not included.

Also, the exportOptions function can be called on header/body/footer rows, but I haven't seen anything suggesting a pre-export or post-export acting on the entire table, without affecting the source data.

exportData

Hoping that there is a solution here

var data = $table.buttons.exportData();
var row = 4;
data.body.splice( row, 0, [
   'foo', 'foo', 'foo', ...
]);

data now contains multiple rows, but how does the csv know to use this data source? I am faltering in my understanding of how to use the data and still hopeful that an extend exists.

Future Features?

If that's all correct, may I make the following features for future versions:

• pre-export hook
• post-export hook

It would be nice to have a hook that could also iterate over rows, columns, or the entire table. The idea would be that a developer could format the generated TXT, CSV, or XLS file (adding new columns, rows, formatting, etc) before the file download dialog opens. It'd also be nice to be able to work on the data (without affecting the source) prior to the CSV or XLS has been generated - so pre-compilation/post-compilation hooks as well, which would enable a developer to work on source data (without applying the changes to the source table).

I'm curious how it could be created, without confusing or bloating the documentation. I suppose a section called "Hooks"

Goals

The goal is to have a button generate a formatted report, without affecting the source data, and then carry out the export.

In my case, I'd like to perform calculations, aggregations, and derived data; which requires new rows (e.g., blanks, subtotals, and totals) as well as new columns. I'm not sure how I can do this without affecting the source data.

If anyone knows, perhaps we can use my JSBin to demonstrate how the subtotals would be included in the output/report (copy is also desired).

allan

There is no pre-export hook, but there is a customize option in most of the buttons (csv for example) which can be used to modify the export data that the button generates before it is actually exported. That is probably the best way to modify the exported data at the moment. (Note the excel option doesn't include such as option at the moment).

The other option is to not use the built in button types but simply built the button yourself. Its a lot easier than it might initially sound (particularly if you don't need Flash support) - you have access to the DataTables API so you can built the data you want to export fully.

Perhaps a future enhancement for Buttons would be to expose the copy and save to file methods so custom buttons such as this could reuse that code.

Allan

vol7ron

I made changes here: https://github.com/DataTables/Buttons/pull/74

Admittedly, I rushed for my use case and didn't give the flash-based methods much thought. Included in the first commit message is a note/suggestion about for future changes.