Active Graphs and Charts - Reading Values from Spreadsheets

Getting Started

Documentation

Tutorials

Examples

Reading Values from Spreadsheets

In this tutorial we show how to use the graphing package to plot data directly from spreadsheets in real time.

Configure the Graph

The first step is to configure the graph software to request the data from a server script. The server script will open and read the spreadsheet and pass the data to the graph. This is done with the parameter 'JpGraphPropertyFile'.

Here is the configuration used for this graph:-

window.onload = function () {
  /**
   * draw the graph
   */
  var thegraph = new JpGraph('vertical bar',
                 'graph',
                 null,
                 {
                    JpGraphDataFile       : './scripts/bardata-spreadsheet.php',
                    JpGraphPropertyFile   : './scripts/barprops-spreadsheet.txt',
                 });
}

Create the Data Script

For this example we have created the data script with PHP but the server side script can be written in any server side language e.g. Java, ASP, Python etc.

The script is fairly straight forward and performs the following steps:-

Open the spreadsheet
Read the temperature data for the three locations London, Madrid and Berlin.
Output the data to the graph

<?php
/*
 * load the Excel Reader Library Files
 */
require_once('phpxlreader/oleread.inc');
require_once('phpxlreader/reader.php');        
/*
 * open the spreadsheet
 */
$spreadsheet = 'temperature-data.xls';
$xlreader 	= new Spreadsheet_Excel_Reader();
$xlreader->setOutputEncoding('UTF-8'); 
$xlreader->read($spreadsheet);
/*
 * output the temperature values for 
 * London, Madrid and Berlin
 */
for ($i=2; $i <= $xlreader->sheets[0]['numRows']; $i++)
{
    echo "series1data" . ($i-1) . " = " . $xlreader->sheets[0]['cells'][$i][1] . PHP_EOL;
    echo "series2data" . ($i-1) . " = " . $xlreader->sheets[0]['cells'][$i][2] . PHP_EOL;
    echo "series3data" . ($i-1) . " = " . $xlreader->sheets[0]['cells'][$i][3] . PHP_EOL;    
}
exit(0);
?>

Whenever the spreadsheet is updated the graph will automatically update with the new values.

back to tutorial index