Script, Tutorial, Free, Learn, In PHP Programming: Put an Interactive Spreadsheet on Your Page

Use the ActiveWidgets spreadsheet library to put an interactive JavaScript data control on your page.

Let's face it: some dataparticularly financial and statistical datajust looks better when it's presented as a spreadsheet. Unfortunately, HTML does a poor job of giving you an interactive spreadsheet-style feel, especially when it comes to scrolling around, sorting, or any of the truly interactive user experience elements of a spreadsheet.

This hack uses the ActiveWidgets (http://activewidgets.com/) grid control to create a spreadsheet-style interface on a web page.

The Code

Save the code in Example 1 as index.php.

Example 1. A script that provides state-specific data in a spreadsheet format

<?php $states = array(

array( "Alabama",4447100,1963711,

52419.02,1675.01,50744,87.6,38.7 ),

array( "Alaska",626932,260978,

663267.26,91316,571951.26,1.1,0.5 ),

array( "Arizona",5130632,2189189,

113998.3,363.73,113634.57,45.2,19.3 ),

array( "Arkansas",2673400,1173043,

53178.62,1110.45,52068.17,51.3,22.5 ),

array( "California",33871648,12214549,

163695.57,7736.23,155959.34,217.2,78.3 ),

array( "Colorado",4301261,1808037,104093.57,

376.04,103717.53,41.5,17.4 ),

array( "South Dakota",754844,323208,77116.49,

1231.85,75884.64,9.9,4.3 ),

…

array( "Tennessee",5689283,2439443,42143.27,

926.15,41217.12,138,59.2 ),

array( "Texas",20851820,8157575,268580.82,

6783.7,261797.12,79.6,31.2 ),

array( "Utah",2233169,768594,84898.83,

2755.18,82143.65,27.2,9.4 ),

array( "Vermont",608827,294382,9614.26,

364.7,9249.56,65.8,31.8 ),

array( "Virginia",7078515,2904192,42774.2,

3180.13,39594.07,178.8,73.3 ),

array( "Washington",5894121,2451075,71299.64,

4755.58,66544.06,88.6,36.8 ),

array( "West Virginia",1808344,844623,24229.76,

152.03,24077.73,75.1,35.1 ),

array( "Wisconsin",5363675,2321144,65497.82,

11187.72,54310.1,98.8,42.7 ),

array( "Wyoming",493782,223854,97813.56,713.16,

97100.4,5.1,2.3 ),

array( "Puerto Rico",3808610,1418476,5324.5,1899.94,

3424.56,1112.1,414.2 )

);

<html>

<head>

</head>

<body>

var data = [

<?php $first = true; foreach( $states as $state )

{ if ( !$first ) echo( "," );

[ "<?php echo($state[0]); ?>", <?php echo($state[1]); ?>,

<?php echo($state[2]); ?>, <?php echo($state[3]); ?>,

<?php echo($state[4]); ?>, <?php echo($state[5]); ?>,

<?php echo($state[6]); ?>, <?php echo($state[7]); ?> ]

<?php $first = false; } ?>

];

var columns = [ "State", "Population", "Housing Units", "Total Area",

"Total Water", "Total Land", "Population Density", "Housing Density" ];

function dataLookup( row, col )

{

return data[row][col];

}

function headerLookup( col )

{

return columns[ col ];

}

var grid = new Active.Controls.Grid;

grid.setRowCount( data.length );

grid.setColumnCount( columns.length );

grid.setDataText( dataLookup );

grid.setColumnText( headerLookup );

document.write( grid );

</script>

</div>

</body>

</html>

Running

Download the ActiveWidgets grid library and unpack it. Move that directory to your server and place the index.php file in the same directory as the ActiveWidgets files. Then point your browser to index.php; you should see something like Figure 1

Figure 1. An ActiveWidgets grid control with U.S. census data

This simple page does a JavaScript source include on the ActiveWidgets grid control library. Then it loads the data into a JavaScript array and creates the grid control. The script then sets the data lookup to a local function called dataLookup(), which just returns the data at that row and column. The column headers work the same way.