Producing Graphs from Spreadsheet Data

This tutorial demonstrates how to implement the graphing software to dynamically read data from a spreadsheet (in this case an MS Excel Spreadsheet).

Prerequisites

The following tutorials assume you have already downloaded and installed the
PHP Advanced Graphs and Charts package.

The latest release can be downloaded here

PHP Excel Reader

In this tutorial we will be using the open source package 'PHP Excel Reader' package. This package can be obtained from:-
http://sourceforge.net/projects/phpexcelreader/

Once downloaded place the following two files into the ./jpowered/graph/ directory:-

reader.php
oleread.inc

At the time of writing we had to make the following small change to the reader.php file:- We replaced:-

require_once 'Spreadsheet/Excel/Reader/OLERead.php';

With:-

require_once 'oleread.inc';

A good general article on using the 'PHP Excel Reader' package can be viewed at:-

http://devzone.zend.com/article/3336-Reading-and-Writing-Spreadsheets-with-PHP

Directory Structure

In this tutorial you will be create a web page and a couple of configuration files. For the purposes of this tutorial you should create a directory /graphtest/ directory under your web root directory.

This tutorial also assumes that you installed the graphing software directly under the web root
(e.g. /webRootFilePath/jpowered/graph/ )

Step 1 - Create a Spreadsheet

For this tutotial we shall create a simple MS Excel spreadsheet with the following layout in sheet 1:-

Once created, save this as "spreadsheet.xls" and place it on your web server in a directory /graphtest/ under your web root directory

Step 2 - Create Data Function

Create a file named datafunction.php which contains the following:-

<?php
function datascript() {
        $dataLines = array();
        // initialise the spreadsheet reader
        require_once 'reader.php';
        $reader = new Spreadsheet_Excel_Reader();
        // Set output Encoding.
        $reader->setOutputEncoding('CP1251');
        // read in the spreadsheet
        $reader->read('../../graphtest/spreadsheet.xls');
        // place the data from the spreadsheet into
        // the dataLines array for use by the graph software
        // the first column and first row of our spreadsheet are
        // labels and so we will ignore these
        // i.e. we will begin reading data from column 2 and row 2
        $dataLines = array();
        for ($i = 2; $i <= $reader->sheets[0]['numRows']; $i++) {
                for ($j = 2; $j <= $reader->sheets[0]['numCols']; $j++) {
                        $dataNum = $i-1;
                        $seriesNum = $j-1;
                        $dataLines[] = "data".$dataNum."series".$seriesNum.": ".$reader->sheets[0]['cells'][$i][$j];
                }
        }
        // return the data lines array to the graphing software
        return $dataLines;
}
// Make sure there are no characters (not even a space or return character)
// after the following line
?>

Again place this file on your web server in a directory /graphtest/ under your web root directory

Step 3 - Create the Configuration File

Create a file named 'config.txt' and add the following contents to it. This file should then be placed in the /graphtest/ directory.

width:             800
height:            400
quality:           very high
backgroundcolor:   white
grid:              true
axis:              true
3d:                true
ndecplaces:        0
thousandseparator: ,
barspacing:        2
barwidth:          16
displaybarvalues:  false
gradientfill:      true
series1:           #BB0000|Product X|left|8|triangle|true|solid|
series2:           #0000BB|Product Y|left|8|box|true|solid|
series3:           #FF8800|Product Z|left|8|diamond|true|solid|
gridbgcolor:       #FFFFCC
axiscolor:         dark grey
floorcolor:        light gray
gridcolor:         grey
gridstyle:         dotted
ylables:           true
yfont:             arial
yfontsize:         11
yfontbold:         bold
yfontitalic:       false
ylabelcolor:       black
ylabelpre:         $
ylabelpost:
titletext:         Product Sales
titlefont:         arial
titlefontsize:     14
titlefontbold:     bold
titlefontitalic:   false
titlecolor:        #8080B0
titleposition:     -1,55
xtitletext:        2008
xtitlefont:        arial
xtitlefontsize:    11
xtitlefontbold:    bold
xtitlefontitalic:  false
xtitlecolor:       #8080B0
xtitleposition:    -1,-1
ytitletext:        Value
ytitlefont:        arial
ytitlefontsize:    11
ytitlefontbold:    bold
ytitlefontitalic:  false
ytitlecolor:       #8080B0
ytitleposition:    -1,230
xlabels:           January|February|March|April|May|June|July|August|September|October|November|December
xlabelorientation: Up angle
xlabeloffset:      0
xlabelcolor:       #8080B0
xlabelfont:        arial
xlabelfontsize:    10
xlabelfontbold:    false
xlabelfontitalic:  false
legend:            true
legendstyle:       horizontal
legendbgcolor:     #EEEEEE
legendbordercolor: #CCCCFF
legendtextcolor:   #8080B0
legendtitle:
legendfont:        arial
legendfontsize:    11
legendfontbold:    false
legendfontitalic:  false
legendposition:    -1,64
// for a full explanation of these parameters see:-
// http://www.jpowered.com/php-scripts/adv-graph-chart/documentation/configurationOptions.htm

Note: This config.txt file contains the parameters which determine certain aspects of the graph like colors and fonts. For the function and options of each parameter see the following page:-
Graph Parameters »

Step 4 - Create the HTML page

Add this HTML code to your page (replacing [www.YourDomain.com] with the actual domain of your web site. Place this page in the /graphtest/ directory.

<img src="http://www.yourdomain.com/jpowered/graph/vertical-cylinder-graph.php?
datascript=../../graphtest/datafunction.php&
config=../../graphtest/config.txt" width="800" height="400"/>

Note:The relative file paths are relative to the location of the vertical-cylinder-graph.php file (they are NOT relative to the location of the web page).

Save the web page and load it up to your web server placing it in the /graphtest/ directory.

Final Step - View the page in a browser

Open your web browser and view the page. You should see a Vertical Cylinder Graph with 36 bars plotted (12 for each series).

How it works

When the page loads the graph function is called which in turn runs the function contained within the datafunction.php file

This function then uses the PHP XL Reader to open and extract the data from the spreadsheet. Finally the data is passed to the graphing function and the graph is produced.

Graphing Tutorials - Producing Graphs from Spreadsheet

Producing Graphs from Spreadsheet Data

Prerequisites

PHP Excel Reader

Directory Structure

Step 1 - Create a Spreadsheet

Step 2 - Create Data Function

Step 3 - Create the Configuration File

Step 4 - Create the HTML page

Final Step - View the page in a browser

How it works

Tutorials

More

Documentation

Database Connections

Installation Help