DataTables

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/1.5-beta/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:

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

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:

<link rel="stylesheet" href="style.css" type="text/css" />
<script type="text/javascript" charset="utf-8" src="/include/javascript/jquery.js"></script>
<script type="text/javascript" charset="utf-8" src="/include/javascript/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf-8" src="/include/javascript/ZeroClipboard.js"></script>
<script type="text/javascript" charset="utf-8" src="/include/javascript/TableTools.js"></script>
	<script type="text/javascript" charset="utf-8">
		$(document).ready(function() {
			$('#results').dataTable( {
				"sPaginationType": "full_numbers",
				"aaSorting": [[0,'asc']],
				"sDom": 'T<"clear">pfrtli',
				"bProcessing": true,
				"iDisplayLength": 50
			} );
		} );
	</script>

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:

var TableToolsInit = {
	"oFeatures": {
		"bCsv": true,
		"bXls": true,
		"bCopy": true,
		"bPrint": true
	},
	"sPrintMessage": "",
	"sTitle": "",
	"sSwfPath": "../swf/ZeroClipboard.swf",
	"iButtonHeight": 30,
	"iButtonWidth": 30
};

I have my directory structure setup like this:

Main Folder
 |---include
 |    |
 |    |---javascript
 |    |---php
 |    |---swf
 |
 |---images

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:

echo "<table id='results' class='display'>";
echo "<thead>
	<tr><th rowspan='2'>Serial Number</a></th>
	<th rowspan='2'>Tester</a></th>
	<th rowspan='2'>Date</a></th>
	<th rowspan='2'>Board Status</a></th>
	<th colspan='2'>Boundary Scan</th>
	<th colspan='2'>Board Programming</th>
	<th colspan='4'>Power Tests</th><th colspan='3'>Bootstrap and Autoscr</th>
	<th colspan='3'>GPS Test</th>
	<th colspan='2'>Audio Test</th>
	<th colspan='2'>Video Test</th>
	<th colspan='2'>USB Test</th></tr>";
echo "<tr><td>Test</td><td>Notes</td>
	<td>Test</td><td>Notes</td>
	<td>1.3V</td><td>1.8V</td><td>3.3V</td><td>Notes</td>
	<td>Bootstrap</td><td>Autoscr</td><td>Notes</td>
	<td>Lock</td><td>Power</td><td>Notes</td>
	<td>Test</td><td>Notes</td>
	<td>Test</td><td>Notes</td>
	<td>Test</td><td>Notes</td></tr>
      </thead>

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 <div class="pass">Pass</div> 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 :

<script src="jQ/jquery-1.3.2.min.js" charset="utf-8" type="text/javascript" /></script>
<script src="jQ/jquery.bgiframe.js" charset="utf-8" type="text/javascript" /></script>
<script src="jQ/dataTables-1.5.b9/media/js/jquery.dataTables.js" charset="utf-8" type="text/javascript" /></script>
<script src="jQ/TableTools/media/ZeroClipboard/ZeroClipboard.js" charset="utf-8" type="text/javascript" /></script>
<script src="jQ/TableTools/media/js/TableTools.js" charset="utf-8" type="text/javascript" /></script>

...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

squig

Hi Allan,

ok, just realized (again) why its called "Cascading style sheet" -.-

On a very high level was a definition which had broken TableTools. My fault. Sorry for that.

squig

braz

Hi allan,

Thanks for newe useful feature.

I think that many users will also like a kind of opposite feature: create dataTable from CSV or simple XLS file.
I think it can be implemented in the next way:
1. User posts the CSV or some other supported file on the server.
2. Server side script parses the file to to native dataTable JSON format
3. Data table makes AJAX request to the server to get the data and redraws itself.

What do you think about that ?

allan

Hi Braz,

Sounds like a very interesting idea. It should be fairly straight forward to do a transform from CSV to a JSON format for DataTables (or any source source to JSON transform, XML etc) and then use this tool in combination with DataTables to save it however you want. I like it :-)

Allan

braz

I think that parsers for commonly used text data formats to JSON exist. Maybe will try to implement this feature as a plugin to dataTables

orchid1

this plugin saved me like 1,000 hours worth of work

man DataTables is awsome

love it

adam

Hi allan

First of all, thank you so much for sharing your work. TableTools IS amazing. As orchild1 says above, it saved me tonnes of work! I was blown away with DataTables and TableTools is just the cherry on the cake :)

I have a few issues that I'm hoping you can assist me with:

1) When I export with the .xls button, my ampersands are getting encoded. So I end up with & amp ; (spaced to avoid it displaying as $amp; in the browser) in the spreadsheets. How can I turn this off?

2) The export option breaks accented characters (my data is UTF8). Is there a fix or work-around for this?

3) The copy and Excel buttons break in some versions of IE. But this may also be linked to the version of Flash. Are you specifying a Flash version in your code. Where is it? I'd like to lower it to see if this helps the situation.

Sorry if this sounds like I'm picking holes in it, that's not my intention. I'd love to roll this out through out my whole site, but these are just my last few niggles.

Please help and thank you again!

allan

Hi adam,

Great to hear that TableTools is really useful - it makes it all worth while when it's used!

1. This is quite odd - I suspect it's something to do with the Javascript -> Flash bridge, but I'll need to debug what is actually going on (plus my trial version of Flash I used to create that button has expired - doh).

2. Again (and without known any better) I'm going to point the finger of suspicion at the comms bridge. But that will need some debugging as well - quite important to make that work!

3. Flash 10 is required (due to the 'save to local file system - which was introduced in Flash 10). I had thought that I had included the relevant tagging required to make Flash 10 required by the browser - but it's possible I've made a mistake there. Or it's possible that the browser just isn't prompting for the Flash upgrade, if Flash 9 is installed.

Regards,
Allan

adam

Hi allan!

Thanks for the quick reply. I'm well impressed you build this during a Flash trial period!

1/2. If you could investigate this in the next few weeks I'll buy you a few pints via the donation page :)
I'd really like to set this live and this is the only thing holding me back now.

3. There is no prompt for version 10. After more investigation we realised that it only breaks in IE 6/7 browsers that have Flash <10. But we are using some flash on the previous page, so will do our own prompt for v10 there, then it wont be an issue.

If there is anything I can do with regards the first point please let me know.

Thanks for your time,
Adam

allan

Hi Adam,

1/2 - I'll certainly have a look at this, but it will be at least a week before I can do so I'm afraid - heading off on holiday soon and not taking a computer! When I get a chance to update the flash software I'll update this thread.

3. Good to hear it won't be an issue for you - but I'll look into why it's not promoting for flash 10 anyway.

Regards,
Allan

iza

I found a bug:
If no filename option is supplied (so it defaults to page title), and the page title contains illegal characters, the export buttons don't work; no error message is given (unless you have the flash debugger plugin installed), the save dialogue simply doesn't open. Should be a simple matter of stripping illegal characters from the page title string before passing it to the file dialogue.
Took me forever to figure out why it wasn't working..

Another minor issue, the csv export doesn't account for existing commas in the table cells, so if your table data contains commas, the csv file gets messed up. (either stripping out commas or enclosing the values in quotes should work)

allan

Hi iza,

Thanks very much for letting me know about these bugs! I'll have a look at fixing them in the next revision of TableTools - hopefully not to far in the future).

Regards,
Allan

alimonti

Hy Allan,
i'm starting to use your library and i think it is very well done.
I'm using the plugin TableTool too, version 1.0.2 with the last version of dataTable ( 1.5.1) with Firefox 3.5.

It works great until it handles more and less 4 thousand rows,
but as soon as it exceeds this number of rows...it stop to works and i can't more save the rows into a file or copy to a clipboard.

There is a maximum number of rows to handle?

I need to handle a bigger number of rows ( over 40 thousands ) there is a way to do it or am i mistaking something?

Thank u for all.

allan

Hi alimonti,

I'm guessing that you've run into a limitation in the amount of data that can be transferred over the Javascript / Flash bridge. I'm not sure how big this limit might be (assuming that there is indeed one), but I can't think of any other reason why it wouldn't work. If you reduce it to 1000 rows (or even smaller) does it then work?

If this is indeed the case, then what you will probably need to do is use some kind of server-side processing to output the data to XSL etc.

Regards,
Allan

alimonti

Hy Allan,
thank u for your reply.

Yes, if i reduce the number of rows it works ( more and less until the file size is 350 KB).

Do u know if there is a way to set this limit on Javascript /Flash Bridge transfer data?

allan

Hi alimonti,

A quick scout around on the web shows that you might indeed be hitting a hard limit to the External Interface that Flash is using:

http://board.flashkit.com/board/showthread.php?t=719855
http://blog.deconcept.com/2005/08/16/external-interface/

So it's undocumented and not much we can do about it. Sorry. Best bet might be to ask around on the Adobe forums, but I rather suspect you might need to post your data to a server (you can hack TableTools do to this instead of passing it to flash) and then have it do whatever saving of files you need.

Regards,
Allan

alimonti

Hy Allan,
thank u for your quick reply.

I think that i will use your TableTools like it is, ( i prefer to don't hack it so that i can update it without effort :) ) and i will add a way to download the data from the server.

May i suggest u a thing?
I was using also KeyTable tool to navigate on the table but to be perfect it should have to handle also SHIFT and CTRL keys to allow multiple selection. :)

Again a time, thank u for this wonderful tools u did, i'm using dataTables like a window for the DataBase ( for this i need to handle all this rows ) and it is exactly what i was looking for.

Ciao Allan.

allan

Hi alimonti,

Your idea of being able to handle modifier keys to select more that one cell. It would be a fairly significant update (and would need consideration for how to deal with events, particularly editing etc), but I've added it to the wish-list :-)

Regards,
Allan

llirik

Where exactly is TableTools handling stripping the html? One thing it doesn't strip out is spaces written by code (as in,  ).

In my table, a few rows also have a hidden DIV for displaying additional information... the HTML gets stripped, but the content of the div still continues through. I would tell TableTools so completly strip out the contents of a certain class before it destroys the html elements. So basically, where does it do this work? I'm having trouble finding it.

Thanks.

allan

Hi llirik,

In the function fnGetDataTablesData() you'll see two instances of ".replace( /<.*?>/g, "" )" in the code - one for the titles, and one for the rows. This is where the tags are stripped. You can manipulate the data here as needed.

Regards,
Allan

JustLikeIcarus

Hey Allan, Im looking for a way to have the print display appear in a jquery ui dialog instead of the current method. Ive looked through the table tools code and dont see how I could do it with the code as it is. What is your opinion on the best method for this? Would I need to clone the table? Thanks in advance for any help you can provide.

DataTables

Howdy, Stranger!

Support

Categories

In this Discussion