Graph data from an HTML table using jQuery and flot
NOTE: A couple of commenters have pointed out that this plugin has issues in IE. I haven't had time to look into it, so I'm not sure whether they are related to my plugin or to flot. Make sure you read flot's readme about getting flot working in IE, I think it will address most IE issues. NOTE 2: I've posted a minor update to graphTable to remove an errant console.log call. NOTE 3: I've changed the license on the plugin; it is now dual-licensed MIT and GPL, like jQuery itself. I just got done with a first draft of the graphTable plugin, which lets you take a simple HTML table and turn the data in it into a graph using jQuery and flot. Here's a demo, and here's the jQuery plugin page where I'll continue development if there's any interest. The most basic usage is simple:
$('#table1').graphTable({series: 'columns'});- choose which parts of the table will be used for the graph data
- choose where the graph will go in relation to the table and how big it will be
- run a function on cell contents (to remove dollar signs, for example) before passing them to flot
$('#table1').graphTable({
series: 'columns',
dataTransform: function(s) { return(s.replace('$','')); }
});options for reading the table
defaults will work in most cases except you'll want to override the default args.series if your series are in columns- series 'rows', 'columns'
- labels integer index of the cell in the series row/column that contains the label for the series; default is 0
- xaxis integer index of the row/column (whatever args.series is) that contains the x values; default is 0
- firstSeries index of the row/column containing the first series; default is 1
- lastSeries index of the row/column containing the last series; will use the last cell in the row/col if not set; default is null
- dataStart index of the first cell in the series containing data; default is 1
- dataEnd index of the last cell in the series containing data; will use the last cell in the row/col if not set; default is null
graph size and position
- position 'before', 'after', 'replace'; indicate whether the graph should go before the table, go after the table, or replace the table
- width leave as null to use the width of the table for the width of the graph; otherwise, set to a width in pixels
- height leave as null to use the height of the table for the height of the graph; otherwise, set to a height in pixels
data transformation before plotting
- dataTransform function to run on cell contents before passing to flot; string -> string
- labelTransform function to run on cell contents before passing to flot; string -> string
- xaxisTransform function to run on cell contents before passing to flot; string -> string
57 comments
Just one suggestion though, you might want to consider a setting for the margin of the graph, dependent upon position. When I place the graph below the table, the top edge of the graph is right on the bottom of the table. Although this could be resolved with CSS as well.
Anyway, great job.
Brandon: I hadn't given much thought to making this work with Flash instead of flot -- I think it wouldn't be hard, if there is a Flash chart library that could accept JSON data like flot can. That's really all this plugin does -- build an object that gets handed off to flot -- and it could just as easily hand that object off to something else. By the way -- you can use excanvas.js to get around the fact that IE doesn't support canvas. See the installation section of the flot README.
I hope, they make this part of jquery UI with lots of customizations.
I'm just wondering if I can use it for a time line with having the month (January, February,...) on the bottom line. Is that possible?
That would be a really great feature.
Thx
thanks
Thanks for your help.
i pass to flot the last x axis plot value as the "max" option to insure that the x axis extends out to the end of the data in the table (it seems to max out at 100 if no argument is passed).
i also created x and y axis ticks options to the plugin and pass those to flot to allow the ticks to be set manually.
I've got the plugin working nicely in stand alone pages, thanks!
When I put the page content into a jquery ui ajax tab (append new tab), the graph does not display fully. All I see is the legend and axes labels. Any ideas why that might be?
This would avoid a lot of later disappointment :)
if (args.xaxisTransform) { x = args.xaxisTransform(y); }
1. Include excanvas in your html e.g:
(see flot doc for additional details)
2. Comment out the call to console.log in jquery.graphTable-0.1.js:
// console.log(tableData);
I did notice one problem (on IE and Firefox). I couldn't work out how to render bar graphs. The following code rendered a line graph with points:
$("#testGraph").graphTable({height: "250px"},
{xaxis : {tickDecimals: 0},
points: {show: true},
lines: {show: true}});
Downloads by Browser
Browser/Year
2006
2007
2008
FireFox
12
23
15
IE
37
43
23
Netscape
5
8
2
Changing this to
$("#testGraph").graphTable({height: "250px"},
{xaxis : {tickDecimals: 0},
bars: {show: true}});
Displayed a legend but no bars.
for some unknown reason i had to declare local variable to test on becuase the inline !null was not working as expected.
// index of the row/column containing the last series
notdefined=!args.lastSeries;
if (notdefined) {
// index of the last cell in the series containing data
notdefined=!args.dataEnd;
if (notdefined) {
i added a plug-in option to specify a placeholder for the position instead of it being relative to the table which allows the graph to be anywhere on the page.
if (args.placement) {
$div = $("#"+args.placement);
} else
switch (args.position) {
i made the assumption that the x-axis will not be numeric and added the following:
rowData[rowData.length] = [i+0.5,y];
tickmarks[i-args.dataStart] = [i+0.5,y];
and
colData[colData.length] = [i+0.5,y];
tickmarks[i-args.dataStart] = [i+0.5,x];
and changed the flotArgs to be:
var flotArgs = { xaxis: { min: 1, max: tickmarks.length+1, ticks: tickmarks }, yaxis: { min: min, max: max } };
and the final change was to add a third configuration argument to allow for specifying the series properties. Like Peter G., i wanted to have one series to use bars.
$.fn.graphTable = function(graphArgs_,flotArgs_,seriesArgs_) {
for (j=0;j 0) ? seriesArgs_[j] : seriesArgs_;
$.extend(true,tableData[j], pointArgs);
}
$.plot($div, tableData, flotArgs);
so my call is:
$('#tabledata').graphTable({series: 'columns', placement: 'dataplot', width: '500px'},
{y2axis: { min: 0, max: 100 }},
[
{bars: { show: true, align: "center", barWidth: 0.5 }},
{lines: { show: true }, points: { show: true }, yaxis: 2}
]);
('#table1').graphTable({series: 'rows'},{legend:{container:$('#legend')}}); $(this).remove();
Hope someone can help me out, I tried playing with the flot parameters a little but couldn't figure it out. It seems like I'm missing a setting.
it can be found here:
http://code.google.com/p/explorercanvas/
Thanks.
Thanks for your contribution. The seriesArgs_ extension was exactly what I needed. And being new to Javascript, your example was invaluable.
Here is my modified snippet:
$.fn.graphTable = function(graphArgs_,flotArgs_,seriesArgs_) {
.....
switch (args.series) {
......
}
if (seriesArgs_ != undefined) {
for (j=0;j<seriesArgs_.length;j++) {
$.extend(true,tableData[j], seriesArgs_[j]);
}
}
....
This change allows columns to have blank entries ignored (previously, the script will set blank cells to 0). The following the is section under the case 'columns': portion (approx line 147)
...
case 'columns':
// iterate over each of the columns in the series
var $labelRow = $rows.eq(args.labels);
for (j=args.firstSeries;j<=args.lastSeries;j++) {
// j designates the column
var colData = new Array();
var label = $labelRow.find('th,td').eq(j).text();
if (args.labelTransform) { label = args.labelTransform(label); }
for (i=args.dataStart;i<=args.dataEnd;i++) { // i designates the row
$cell = $rows.eq(i).find('th,td').eq(j);
var y = $cell.text();
if (args.dataTransform) { y = args.dataTransform(y); }
test_y = parseFloat(y);
// test for a valid y value
if(!(isNaN(test_y))) {
if (test_y max) { max = test_y; }
var x = $rows.eq(i).find('th,td').eq(args.xaxis).text();
if (args.xaxisTransform) { x = args.xaxisTransform(x); }
test_x = parseFloat(x);
colData[colData.length] = [x,y];
}
}
tableData[tableData.length] = { label: label, data: colData };
}
break;
...
Thanks Rebecca!
i added your code, but now I get a firebug exception:
tickmarks is not defined
[Break on this error] tickmarks[i-args.dataStart] = [i+0.5,x];
I used your code
"$(’#tabledata’).graphTable({series: ‘columns’, placement: ‘dataplot’, width: ‘500px’},
{y2axis: { min: 0, max: 100 }},
[
{bars: { show: true, align: "center", barWidth: 0.5 }},
{lines: { show: true }, points: { show: true }, yaxis: 2}
]);"
so this should not be the error.
Can maybe someone post a link to a working js with this added features or does anyone have an idea what i did wrong?
Thanks
if i define var tickmarks = new Array();
i do not get an error, but nothing shows up anyway.
this is my js file:
http://veare.net/libs/js/jQuery.chart2.js
And this is trying to get ot to work:
$('#table').graphTable({series: 'columns', position: 'replace', dataTransform: function(s) { return(s.replace(' €','')); } , labels: 0, width: '860px', height: '500px'});
I seem to be having the issue that Noel, Charles and Peter had with the Bar Graph not showing up. I have made some changes in the code and even explicitly put:
174 tableData[tableData.length] = {label: label, data: colData};
175> $.extend(true,tableData[tableData.length-1],seriesArgs_);
which applies the seriesArgs_ to all series, then i used the following to call the function:
flotoptions={xaxis:{mode:'time'}};
flotseriesoptions={points:{show:true}, bars:{show: true, barWidth:24*60*60*1000}};
$("table").graphTable({},flotoptions, flotseriesoptions);
This does not seem to solve the problem, has anyone see what's wrong?
change:
171 colData[colData.length] = [x,y];
to
171 colData[colData.length] = [test_x,test_y];
canvas = window.G_vmlCanvasManager.initElement(canvas);
An application that I developed works fine in IE 7 as well as IE 6.
Hopefully the author will look into this.
Do I need to use a transformation arg? If so what would this look like?
Thanks
I got date data transform into epoch format but there are not parsed as date values , should I used xaxisTrasform or dataTransform .
I tried :
$('#table1').graphTable({series: 'columns', xaxis: { mode: "time" } });
but it didn't work .
Thanks eva
Great plug in BTW.
In reply to Eva and Reiwoldt, I had the same problem with dates but have had success with the following.
$('#myTable').graphTable({series: 'columns', width:'50%', position:'replace',xaxisTransform:function(s){return(Date.parse(s));}},{xaxis:{mode:'time'}}); $(this).remove();
The xaxisTransform function gives the data to graphTable as epoch format (i.e milliseconds from 01/01/1970), from dates supplied from a database in the form of yyyy/mm/dd hh:mm:ss.
xaxis mode is a flot argument and needs to be separated from the graphTable args.
Eva if you haven't solved this try
$(’#table1′).graphTable({series: ‘columns’}, {xaxis: { mode: “time” } });
as I think your syntax is giving the xaxis mode as a graphTable argument.
Hope this helps, I'm little more than a hobbyist but have struggled with the same issues and now got mine working so I'm passing it on.
Make sure you have added the include that is in the flot read me file.)
Hopefully that should solve your IE problems.
Its failing at
line no: 153 in excanvas.js
el.innerHTML = '';
"el is undefined"
For getting the date labels working use following code:
jQuery('#chart').graphTable({series: 'columns'}, {xaxis: {mode: "time"}
for the data source use sth. like this:
<th><?php echo strtotime($data->date.' UTC')*1000; ?></th>
It's important to format the date into timestamp and multiplicate with 1000 (for milliseconds). the dataformat in my example i got from a mysql db with the format like 2010/11/16.
hope that helps.