Hack11.Put an Interactive Spreadsheet on Your Page | PHP Hacks: Tips & Tools For Creating Dynamic Websites

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

3.2.1. The Code

Save the code in Example 3-1 as index.php.

Example 3-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> <link href="runtime/styles/xp/grid.css" rel="stylesheet" type="text/css" ></link> <script src="/books/1/201/1/html/2/runtime/lib/grid.js"></script> </head> <body> <div style="width:500px;height:300px;"> <script> 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>

3.2.2. Running the Hack

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

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

At the time of this writing, the grid library is not compatible with Apple's Safari browser.

This library is licensed under the GNU Public License (GPL). If you want to use it in a commercial product, you'll have to buy it from the developers. But if you're looking for a spreadsheet-style HTML control, it's probably well worth the cost.

3.2.3. See Also

"Create an Interactive Calendar" [Hack #25]
"Create Link Graphs" [Hack #24]
"Create Drag-and-Drop Lists" [Hack #13]