TableTools v1.0.2 - Save as Excel, CSV, copy and print!

allan

Hello all,

I'm very pleased to be able to release v1 of TableTools. TableTools is a plug-in piece of software for DataTables which adds a toolbar to a DataTables enhanced table with options for saving files as Comma Separated Values (CSV) or Excel (XLS), copying data to system Clipboard, or printing the table. Like DataTables is it quite customisable and I would very much encourage you all to have a hack around in the code and see what you can do with it and customise it to your own needs.

Demo:
http://datatables.net/extras/tabletools/

Download:
http://datatables.net/releases/TableTools.1.0.2.zip

For the moment TableTools will live as part of this site, although when I get time I'll write it up properly on my own site. Until then there are a few things to note:

1. Customisation is done through the manipulation of the global "TableToolsInit" variable
2. It requires Flash 10 for copy / save to file options
3. It required DataTables 1.5 beta 9 or higher
4. The print uses a slightly unusual mechanism. In order to maintain styles and state I've decided to use a method which will hide all nodes from the page which are not required for the table display. This seems quite effective in my tests, but I would be interested in feedback on this.
5. The Excel export is a "cheat". It's actually just a tab separated file with ".xsl" on the file name. Excel opens this very nicely.

I've also added a new section to the plug-ins page for additional software such as this (TableTools and KeyTable are the current two):
http://datatables.net/plug-ins#extras

All feedback (and bug reports, although hopefully not many ;-) ) are very welcome.

Enjoy,
Allan

amitdgr

Thanks a lot Allan. This has been a great help, at the time I needed it the most.

One thing I noticed : When I click on the print image, the page reloads without the extra datatable nodes, but what happens to the back button ?
Also how can I give a heading or caption to the table when it is in print mode?

I am already looking forward to Datatables 1.6 :)

Keep up the amazing work ...

Amit

allan

Hi Amit,

The print is a slightly funny one actually - so I'm interest to hear what people think of it. What I wanted to do was maintain all of the styles etc that are applied to the table, which basically means maintaining it's position in the DOM. So what I do is hide all the other elements on the page which are not needed for the table display.

In order to get back to the regular page, just hit the 'esc' button. There is a message which flashes up on the page (for about 2 seconds - perhaps this isn't long enough?) which says to press escape to return to normal view.

In order to add a heading (title, copyright information etc) which is used specifically for the title you can use the "TableToolsInit.sPrintMessage" parameter. For example:

[code]
$(document).ready( function () {
TableToolsInit.sPrintMessage = "Print header message!";
$('#example').dataTable( {
"sDom": 'T<"clear">lfrtip'
} );
} );
[/code]

As for DataTables 1.6 - got to finish 1.5 first ;-)

Regards,
Allan

chris

WOW! The export to excel feature is exactly what I need! I've been working with 1.4 for a little while and I am definitely going to start testing out the 1.5 betas now. Thanks a lot for doing such great work on DataTables, especially the documentation.

One question, would it be possible to have the excel/csv export file attempt to open immediately after export? Perhaps adding a callback function after saving the file would be good, with the filename as a variable.

- chris

allan

Hi Chris,

Thanks very much for your kind words. I'm afraid I don't think it would be possible to automatically open Excel (or whatever). I've had to resort to using Flash 10 to get the file saving to work (the other option is to use server-side processing but that would create a bottleneck for what is a distributed application), and I don't think there is anyway to send a system call to then automatically launch the program. If the user's browser is configured for opening xls files, then it might well just happen automatically.

The other option is to switch to using server-side processing (it would be a fairly small change to TableTools) and download a file for the user. Again it depends on how the user's browser is set up though - it might or might not automatically open the program associated with the file extension.

Sorry I can't give you a better answer than that.

Regards,
Allan

chris

I suppose the best solution for me would be to just be able to find out what the filename used was. The project I would use this for is an internal, IE only page in a corporate environment. I can safely use an activeX control to start up Excel and show the file. The only thing I would need is the file name and path that the user chose. Would there be any way to get this information?

Gremlyn1

So I have the buttons showing up and my browser detects that it is a flash script there (using FF, but IE is the same). I did move things around to fit my file structure, but I went through and changed all the file paths I could find. Here is my code:
[code]






$(document).ready(function() {
$('#results').dataTable( {
"sPaginationType": "full_numbers",
"aaSorting": [[0,'asc']],
"sDom": 'T<"clear">pfrtli',
"bProcessing": true,
"iDisplayLength": 50
} );
} );

[/code]

Any ideas? I went into the zeroclipboard.js and redirected the .swf (though there are two swf files that came in the zip, are they different?).

Everything looks like I want it to, just can't get the flash buttons to click :(

allan

Hi Gremlyn1,

There might be one more path which is catching you out. In TableTools.js there is a TableToolsInit object (which allows for customisation of features etc) and that has the path to the required swf. The one you want is in media/swf/ZeroClipboard.swf (although the other one is probably identical - not sure...).

I knew when writing this that the paths would be a bit of a pain, since it's one piece of software on top of another. I've tried to simplify the process as I can - but perhaps not quite enough! If this TableToolsInit change doesn't work, do you have a link you could post to the page you are having problems with?

Thanks,
Allan

Gremlyn1

Are you talking about this:
[code]var TableToolsInit = {
"oFeatures": {
"bCsv": true,
"bXls": true,
"bCopy": true,
"bPrint": true
},
"sPrintMessage": "",
"sTitle": "",
"sSwfPath": "../swf/ZeroClipboard.swf",
"iButtonHeight": 30,
"iButtonWidth": 30
};[/code]

I have my directory structure setup like this:
[code]
Main Folder
|---include
| |
| |---javascript
| |---php
| |---swf
|
|---images
[/code]

So I think I have that path correct (I'm not a trained programmer, so I get things wrong from time to time!). All the site files, like the one I am trying to make this feature work on, are stored in the Main Folder (that's not actually its name). Unfortunately I can't post a link to what I am developing as it is an internal system. I can put more code up here or screen shots if you can tell me what you might be looking for.

A few other notes... I combined the css from both dataTables and TableTools to my main css file. Hasn't been an issue with dataTables and I don't see anything in there that would screw up TableTools. FF3 does recognise that there SHOULD be a Flash object there, IE6 (ugh why is that still around) does not. Am I maybe missing something from my server to allow the Flash to run? I am running WAMP as a testing grounds for this before it is released (wish I could get it on a LAMP server), in case that has any bearing.

BTW Allan, thanks a billion for all the work you've done on this! It's exactly what I need.

allan

Hi Gremlyn1,

Presuming that you are loading the HTML from "Main folder" then the sSwfPath should actually be "include/swf/zeroClipboard.swf". The reason for this is that the browser is loading the media relative to where it's root document is. Note that this is _not_ the same as CSS. With CSS it takes it relative to the CSS file, not the root HTML document! Damned annoying really.

CSS: Yup - the combination of them should work fine.
IE6: The method for inserting the swf is different in IE. I think once you've got the path sorting out you should be okay (fingers crossed anyway!).
Server requirements for flash: No - there are none. It just serves up a binary file for the client to deal with
WAMP/LAMP: Should make no difference what platform you are running this on

:-)
Allan

Gremlyn1

Oh silly me, that sussed it. Well, in FF it did at least, IE6/7/8 aren't recognising it as a Flash object still.

EDIT: Noticed an interesting thing here. I have a good number of the columns in my table setup with a main column heading and then 2-3 sub-headings underneath. The copy/paste, save to *.* only likes the first four columns that are just single columns with single (non-complex) headers. Is this a DataTables things or a TableTools thing?

Here is my thead:
[code]
echo "";
echo "
Serial Number
Tester
Date
Board Status
Boundary Scan
Board Programming
Power TestsBootstrap and Autoscr
GPS Test
Audio Test
Video Test
USB Test";
echo "TestNotes
TestNotes
1.3V1.8V3.3VNotes
BootstrapAutoscrNotes
LockPowerNotes
TestNotes
TestNotes
TestNotes
[/code]

allan

Try clearing the cache in IE?

About the titles - that's a good one... What happens is that ToolTables reads the column titles from DataTables, which turn should have read them form the DOM. Assuming that you have one (and only one) unique TH for each column, it should use that as the title. This title cell should be whatever DataTables has put it's click event handler on.

If you want to customise this you could have a look at the fnGetDataTablesData() function in ToolTables. Or there might be a proper bug!

Allan

Gremlyn1

The IE issue occurred on two different computers, one of which I'd not used to test the page yet, so I don't think it would be a cache issue.

As for the TH thing, I'll see if I can rework my thead to see if I can have one TH per column (maybe having one TH over two columns is screwing something up?). I can actually only sort by the first four columns, and i don't recall setting it up to do it that way, though those happen to be the only columns I WANT sortable, so it kind of worked out until now.

Gremlyn1

OK, so I got my thead looking good with a single header per column and it works properly and sorts and whatnot like it should now.

My next question... I have some divs that I use to display a coloured box around the result in the table cell based on a pass/fail check that PHP does for me. When I export the table (in any of the 3 ways available), I get the Pass output, is there anyway you can think of to strip the HTML before outputting? I don't think the people that are going to use this will want to have to strip the divs out themselves unfortunately :(

allan

Hi Gremlyn1,

The IE issue: Yup - it's an issue with TableTools. Unfortunately it is proving to be non-trivial to solve. Working on it just now.

HTML: Good point - I hadn't thought of that. I'll have TableTools strip the HTML from the output.

New release as soon as this damn IE issue is sorted...

Allan

Gremlyn1

You're the best :D

allan

Oh wow - that was a nightmare! IE loses the Flash movie's exported functions when the movie's position is changed in the DOM... Took a while to get that figured, and then an easy fix it. If it wasn't 9am I'd be off for a beer :-)

But that's it done now. You can download TableTools v1.0.1:

- Full support for IE
- Strips HTML tags from the text

Download: http://datatables.net/releases/TableTools.1.0.1.zip

There is one small thing to note with file saving in Windows - Flash doesn't show the file type for how the file is being saved (it does in Mac OS). It will save it with the right file type, but it just doesn't show what this will be. It appears to be a limitation with saving files from Flash 10.

Enjoy,
Allan

Nsedgwick

Hi

Attempted to use this - but unable to make it work in actual situ - I always get the javascript error message:

_oSettings.oFeatures is undefined
..../jQ/TableTools/media/js/TableTools.js Line: 87

Any ideas?
N

allan

That's very odd. _oSettings is just a copy of TableToolsInit. Have you defined TableToolsInit yourself at some point? If so it's probably best to assign the parameters for it individually, so the object retains it's internal structure (such as the features).

Allan

Gremlyn1

OK, well it it PERFECT in FF now. HTML is stripped and everything looks/works as well as I could ever dream.

EDIT: NM, it seems I forgot to change the path to the button pics. Looks like I am working in IE now too! I'll test it out a bit more and report back :) Thanks allan!

Colin

Nsedgwick

Well, I tried defining TableToolsInit at one point to see if it made a difference... But it didn't.

One thing I have got going on is an outer html page (containing the main navigation etc). This outer page is where all my javascript files (such as dataTables.js etc) are declared, such as :







...Then all of the dataTables manipulation etc takes place within an inner DIV for a tabbed content pane. This works fine for all regular jQuery aspects of dataTables and other jQuery utilities - just TableTools seems to fail.

allan

Hi Nsedgwick,

Do you have a link you could provide to show this in action. It would make debugging much easier.

Thanks,
Allan

Nsedgwick

Tricky as the dev server is on a private intranet IP, behind a secure firewall etc.
I''ll see if I can cobble a small installable demo togther (soon) that isolates the problem

Nsedgwick

After some further trials - I think I partly know the cause of the problem....

I also use Mootools as well as jQuery. And although I write all jQuery references longhand (ie 'jQuery' instead of '$' - to avoid clashes), there must still be something else that clashes with your TableTools plugin.

[ I'm using mootools-1.2-core-nc.js and mootools-1.2-more.js ]

Even so, although I have commented out the Mootools stuff to test - I'm not yet fully out of the woods. Because I'm using an inner DIV region (uses jQuery tabs to change the inner content) - for some reason the first three TableTools icons (which are at least now visible) do nothing - they are not even clickable (and no jscript errors appearing in the error console). Only the last icon - the Print icon - is clickable and will actually go to a Print Screen.

Just thought you'd like to know.

Regards
N

allan

Good point! I always forget to wrap my code up so this kind of interaction doesn't occur. So I've just updated the package to account for this.

TableTools v1.0.2:

- Wrap $ variable for interaction with other libraries

Download: http://datatables.net/releases/TableTools.1.0.2.zip

Allan

Gremlyn1

Nsedgwick,
The print button doesn't appaear to be Flash based, so it'll work even if the other three don't. I had the same thing happen to me and found I wasn't referencing my .swf properly in the TableTools.js file.

Nsedgwick

Cheers Gremlyn1 - that did the trick!
You got any idea how you can position the set of icons in a different place?
Thanksamundo.
N

Gremlyn1

Sure do. You can position it left vs right in the CSS using the float, and fine tune with your margins. The actual placement of the box is determined by the order of your items in the sDom (http://datatables.net/usage#sDom).

Rob

Thanks, Allan, for a great add-on. This is a great "added value" for DataTables users. Following are my notes after briefly using TableTools. Hopefully, they prove helpful in some way. Note that I am using server-side processing. Some of my notes may be intended behavior. I am using Internet Explorer v7.0.5730.13 and FireFox v3.0.11.

Rob


Copy, CSV, Excel:

Ad Block comparable browser plug-ins may display a "block" option next to each option (Just an FYI in case you're surprised).


Print:

Clicking the Print option performs a query (table refresh).

Only the current results display is printed (e.g., 10 records, 20 records). With local results, the entire recordset (not just the current results display) is visible in the "print preview".

If row highlighting is enabled, that functionality remains active.

Column headers are clickable and the visual sort cues change, but sort functionality isn't active.

After pressing ESC to return to the grid, sort options select in "print preview" are carried out (except in FireFox, see note below).

After pressing ESC to return to the grid, a query occurs (table refresh)

Within Firefox, after pressing ESC to return to the grid, the "processing" alert doesn't disappear (this could be happening because (per Firebug) the query fails).


General:

Firebug reports the following error (which may be a ZeroClipboard error and doesn't appear to cause an issue):
this.movie.setText is not a function
http://PATH/ZeroClipboard.js
Line 255

squig

Hi all,

all functionality just works fine but I'm having some strange formatting problem, display in IE6 is broken. I know, I know ... omg IE6 ... but customer is king.
So this is how it looks like:
http://img188.imageshack.us/img188/5093/ie6strangbehaviour.png

The surrounding div is placed right but the buttons are lost.

Maybe someone has an hint for me to bring them back together.

Thanks.

squig

allan

Hi squig,

Looks like it's probably just a CSS issue. Might be an idea to crack out the developer tools for IE and see what they say. XRay ( http://www.westciv.com/xray/ ) can be very useful as well. Also, are you using a strict doctype?

Allan