Sortable Tables

Posted by RobElliott on 12 Feb 2009 21:37, last edited by GoVegan on 09 May 2010 06:01

Tags: excel google javascript sort sortable table

rating: +15+x

At the time of writing this how-to (February 2009) the native wikidot syntax does not have a way to create tables which are sortable by clicking on the header of a column. The javascript solution here enables you to add a sortable table to your wiki without the need to embed external content. It does, however, require you to use html code and an iframe which is described below.

The code uses google's visualization api but does not require you to register or obtain an api key.

Click on the column headings in the table below to sort on that column:

The full HTML code

Appearing below is the full code to enter in your wiki page. Make sure you change the address in the iframe to your wiki.

[[collapsible show=" " hide="Hide"]]
[[html]]
<html>
  <head>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["table"]});
      google.setOnLoadCallback(drawTable);
      function drawTable() {
      var data = new google.visualization.DataTable();

    data.addColumn('string', 'Department');
    data.addColumn('number', 'Revenue');
    data.addColumn('number', 'Change');
    data.addColumn('boolean', 'Staff Bonus');

    data.addRows(5);

    data.setCell(0, 0, 'Shoes');
    data.setCell(0, 1, 10000, '£10,000');
    data.setCell(0, 2, 12, '12.0%');
    data.setCell(0, 3, true);

    data.setCell(1, 0, 'Sports');
    data.setCell(1, 1, 22000, '£22,000');
    data.setCell(1, 2, -7.3, '-7.3%');
    data.setCell(1, 3, false);

    data.setCell(2, 0, 'Toys');
    data.setCell(2, 1, 4000, '£4,000');
    data.setCell(2, 2, 0, '0%');
    data.setCell(2, 3, false);

    data.setCell(3, 0, 'Electronics');
    data.setCell(3, 1, 31000, '£31,000');
    data.setCell(3, 2, -2.1, '-2.1%');
    data.setCell(3, 3, false);

    data.setCell(4, 0, 'Food');
    data.setCell(4, 1, 80000, '£8,000');
    data.setCell(4, 2, 22, '22.0%');
    data.setCell(4, 3, true);

  var table = new google.visualization.Table(document.getElementById('arrowformat_div'));

  var formatter = new google.visualization.TableArrowFormat();
  formatter.format(data, 2); // Apply formatter to third column

  table.draw(data, {allowHtml: true, showRowNumber: true, alternatingRowStyle: true});
      }
    </script>
  </head>

  <body>
    <div id="arrowformat_div"></div>
  </body>
</html>

[[/html]]
[[/collapsible]]

Breakdown of the Code

So you know what each of the elements in the above code means we will break it down.

As with all html code we don't usually want to see it so it can go inside collapsible tags. We then add the [[html]] to denote the start of the html code.

After that there is the html and initial javascript code and you will see that it goes inside the html head tag:

[[html]]
<html>
  <head>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["table"]});
      google.setOnLoadCallback(drawTable);
      function drawTable() {
      var data = new google.visualization.DataTable();

 
Now add the column headings. You set the datatype as string, number or boolean (true/false) and give the column a title: 
data.addColumn('string', 'Department');
data.addColumn('number', 'Revenue');
data.addColumn('number', 'Change');
data.addColumn('boolean', 'Staff Bonus');

Now add the number of rows you want in your table (note: this solution does not dynamically add rows or columns, you must specify the number in advance):

data.addRows(5);

The next stage is to add the data in the table cells. Make sure you use the same datatype you specified in the column header. The format of identifying the cell is row,column with the first row being 0 and the first column being 0. You will see that when enterig numbers you enter both the number and the formatted number. For a boolean column if you specify it as true it will put a tick/check in the cell, if you enter false it will put a cross in the cell. The first row (row 0) is shown below:

data.setCell(0, 0, 'Shoes');
data.setCell(0, 1, 10000, '£10,000');
data.setCell(0, 2, 12, '12.0%');
data.setCell(0, 3, true);

and the following rows (Rows 1, 2, 3 and 4) are:

data.setCell(1, 0, 'Sports');
data.setCell(1, 1, 22000, '£22,000');
data.setCell(1, 2, -7.3, '-7.3%');
data.setCell(1, 3, false);

data.setCell(2, 0, 'Toys');
data.setCell(2, 1, 4000, '£4,000');
data.setCell(2, 2, 0, '0%');
data.setCell(2, 3, false);

data.setCell(3, 0, 'Electronics');
data.setCell(3, 1, 31000, '£31,000');
data.setCell(3, 2, -2.1, '-2.1%');
data.setCell(3, 3, false);

data.setCell(4, 0, 'Food');
data.setCell(4, 1, 80000, '£8,000');
data.setCell(4, 2, 22, '22.0%');
data.setCell(4, 3, true);

The next section of javascript is needed to setup the table, to apply the arrow format in the third column and to style the table. In this example we are showing row numbers, allow custom formatters and have alternate shading on rows:

var table = new google.visualization.Table(document.getElementById('arrowformat_div'));

var formatter = new google.visualization.TableArrowFormat();
formatter.format(data, 2); // Apply formatter to third column
table.draw(data, {allowHtml: true, showRowNumber: true, alternatingRowStyle: true});
      }
    </script>
  </head>
<body>
    <div id="arrowformat_div"></div>
  </body>
</html>
[[/html]]

Excel utility to generate this stuff

ExcelToGoogleTable.xls

Links

There are many options, parameters and table styles that you can use. The full description of what can be achieved with Google's Visualization API is at http://code.google.com/apis/visualization/documentation/gallery/table.html#Overview

Author

RobElliottRobElliott. Please visit his/her userPage.

Rate this solution

If you think this solution is useful, please rate it up.

rating: +15+x
Add a New Comment

Related articles

Comments

Add a New Comment
_content-advanced_l5excelgooglejavascriptsortsortabletable
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.