Index column out of order when exported to Excel / CSV / PDF

mrr2ro

Hi Allan, thank you very much for this tool set, it is wonderful.

All my reports have an index first column- a simple counter just like any spread sheet enumerating the number of rows in the report.

I used your suggested code to fix the columns and NON sortable, and it is working fine.

However when using TableTools copy / csv / excel or pdf, the index column is all over the place, does not follow the 1,2,3,4,5,6, ... The printed version prints just fine since the display part is working fine.

The issue is the index column gets generated by PHP/Smarty and it is attached to the default sort (usually Lname ascending)

{section name=er loop=$columnarReport}

{$smarty.section.er.index_next}

So if i sort by any other columns, the display works very well as expected, but if I export, the index column is in disarray becasue it seems to be following the initial sorting by name, but in the browser display, the index column remains fixed as it should, and in order regardless of what columns I sort by and how many sorting levels I use.

Here is my code..
[code]

{literal}
/* Data Tables configuration -- Basic initialization*/

jQuery.noConflict();
jQuery(document).ready( function() {
jQuery('#theTable').dataTable( {

/*BEGIN Fixing the index row so they are not sorted*/

"fnDrawCallback": function ( oSettings ) {

/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
for ( var i=0, iLen=oSettings.aiDisplay.length ; i

allan

Hi,

Thanks very much for picking up the DataTables support :-).

The issue here is that my example code the highlighting is actually a little bit of a cheat - sacrificing data integrity (as you are seeing) for speed. To explain what is happening - my example code updates the TD elements that you can see, but not the internal data cache that DataTables holds for each cell. Normally not a problem, but when you interact with that data, it does become an issue, and indeed TableTools reads data from that internal data store (again for speed). This actually relates to this FAQ: http://datatables.net/faqs#append .

DataTables, however, has come a long way since I original wrote that example and it can be updated now to do exactly what you are looking for, without sacrificing speed, and using only the API (unlike the old demo which uses internal settings...).

This is how it can be done now (inside fnDrawCallback - replacing the previous for loop):

[code]
this.$('td:first-child', {"filter":"applied"}).each( function (i) {
that.fnUpdate( i+1, this.parentNode, 0, false, false );
} );
[/code]

So what is happening here is that we are using the $ API method (accessed thought 'this' since fnDrawCallback executes in the scope of the DataTables instance) to get the first TD element in each row. $ is basically a jQuery selector and returns a jQuery object, but the second parameter is DataTables specific and allows us to select the ordering of the elements, filtering, paging etc. In this case we want the filtering to be applied (so add the 'filter' option) and sorting in the sorted order (which is the default option).

Then we loop over each of the found elements with the 'each' function from jQuery and use the DataTables fnUpdate method to update the cell - this is the important change - it updates not only the HTML but also the internal data store of DataTables (i.e. it tells DataTables that something is happening). The this.parentNode (i.e. the TR element) and 0 indicate the row/column and the two false parameters are to stop DataTables redrawing on each update (which would end up with an infinite loop since this is being done in the draw callback!).

I've put the updated example up here: http://live.datatables.net/ununuh/3/edit and I'll commit it into the examples forthwith. Thanks for helping to make DataTables better :-)

Regards,
Allan

mrr2ro

Hi there, I changed the code for the suggested one as in the example below and it works like a charm - :=)
Adding the "bSortbale: false", it helped with performance becasue my first attempt using fnUpdate it was getting really sluggish.

My tables are in average 2500 rows by 12 or 15 columns, I have a report that is 10,500 rows and 18 columns.

The final code that did it, was as you posted in your example:

[code]

/*BEGIN Fixing the index row so they are not sorted -r2ros */
"fnDrawCallback": function ( oSettings ) {
var that = this;
/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
this.$('td:first-child', {"filter":"applied"}).each( function (i) {
that.fnUpdate( i+1, this.parentNode, 0, false, false );
} );
}
},
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] }
],

[/code]


Thank you Allan, this is a great resource, a great set of tools and your support is exemplary!