How can ajax pager support output csv ?

[airthomas]

Hi,

I have implemented ajax pager to my php site, but I have no idea how can it support output ? and I have tried below, but the csv also contains 15 rows only....

$('.download').click(function(){
$("#table").trigger('pageSize', 0);
$("#table").trigger('outputTable');
$("#table").trigger('pageSize', 15);
});

Many thanks,
Thomas

[TheSin-]

you have to make a new ajax call to output more then the page. the reason for using ajax is to have a smaller dataset, output can only see the loaded dataset thus to get a complete dataset you need a new ajax call or to not use ajax in the table to start with, those are the only options and the nature of ajax loaded data.

[airthomas]

thanks your reply, would you please advise the syntax about new ajax call, since i'm a junior ajax user. Many thanks.

[TheSin-]

that is something that is impossible since I know nothing about your project, I don't even know what you are calling (php, perl, cgi, asp, etc etc) nor do I know where your data is stored. That being said you'd basically use the same call just remove the 15 row limit. But you won't be able to use output widget it you'll have to make your own output call. Or the easy route is just remove the limit on your table load the data, output the csv, then put the limit back (you will see the table flash doing this that is the down side), but there are lots of options just depends on the time you want to spend on it. Adding ajax to the output widget is something I have wanted to do for a bit but I have found ways around it so I haven't done it yet. Maybe in the future it'll have a way to communicate with the pager widget and make it's own internal call, sadly it's not there yet.

[airthomas]

thanks, i have tried below to change the limit to 999 (i saw the table is refeshed to more rows) and output it then restore back to 15, but the csv also output 15 rows only.

$('.download').click(function(){
$("#table").trigger('pageSize', 999);
$("#table").trigger('outputTable');
$("#table").trigger('pageSize', 15);
});

Many thanks.

[Mottie]

Hi @airthomas!

You'll need to trigger the output widget after the "pagerComplete" event fires, to ensure that all the rows are in the table when the output is triggered. Try something like this (completely untested code):

var $table = $('#table');

$('.download').click(function(){

    $table
        .one('pagerComplete', function(){
            $table.trigger('outputTable');
            // give the output widget some time to process
            setTimeout(function(){
                $table.trigger('pageSize', 15);
            }, 500);
        })
        .trigger('pageSize', 999);

});

This method is by no means ideal... the best thing would be to have your server provide the csv data for download.

[airthomas]

Thanks Mottie. It's work and I will put the export feature to standalone php and run server side to stream the file to client, many thanks your help.

[airthomas]

Hi Mottie,

Or, will you enhance tablesorter to support ajax pager on output csv easier ? thanks.

Thomas

[Mottie]

I don't think it would be as efficient to modify the pager to make the client side do the work.

[TheSin-]

I agree it would have to be done in the output widget, it could detect ajax pager, get the ajax call and just remove the limits, there is more to it then that of course but that would be the best way as an outline.

[airthomas]

Hi, and I created tablesorter in http://jsfiddle.net/tomChai/yefw6fh6/5/, but I can't solve the UTF-8 encoding and multiple line feature on the output csv feature, can help ? Many thanks.

[Mottie]

If you want to output multiple lines, then set the output_trimSpaces option to false (updated demo).

The output file is set as UTF-8, by default, in the output_encoding option.

output_encoding : 'data:application/octet-stream;charset=utf8,'

[airthomas]

sorry for wrong URL for encoding issue, should be http://jsfiddle.net/tomChai/yefw6fh6/7/, many thanks.

[Mottie]

Oh, one other thing... the replaceCR option needs to be changed:

$.tablesorter.output.replaceCR = '\x0d\x0a';

This makes Excel properly show the carriage returns.

This is also the default setting... it was being changed back to the old setting in that demo.

[airthomas]

the multiline works great, thanks Mottie.

[Mottie]

Hmm, ok the new demo has Chinese characters and Excel doesn't appear to like them...

The widget will probably need to encode those characters to make it work.

[Mottie]

For now, you can get around this in Excel by importing the data....

• Create a new "Blank workbook"
• Choose "Data" > "From Text"
• Find the downloaded csv file and "Import" it.
• In the import wizard, check "My data has headers"
• Then click "Finish"
• Choose a location for the start of the data (Press "OK")

[airthomas]

Thanks Mottie, will waiting TableSorter to support UTF-8 encoding expot csv and open in Excel directly instead of import. Thanks again your GREAT work.

[airthomas]

and FYI, about the exported Chinese csv....
if I open the csv by notepad then save it, then csv will able to open by Excel and able to see the Chinese.

[Mottie]

Ok, I think this latest change should fix the issue... I just need to make sure it won't cause problems in iOS.

Download the "widget-output.js" file from the master branch.

And updated demo.

[airthomas]

Mottie, Great, many thanks your help. It's work !!!