+ Post New Thread
Results 1 to 13 of 13
Coding Thread, PHP to produce graphs based on data from Excel spreadsheet in Coding and Web Development; Hello, I am trying to find a way to build a graph based on the data found in an excel ...
  1. #1

    Join Date
    May 2006
    Location
    West Bromwich
    Posts
    2,190
    Thank Post
    299
    Thanked 215 Times in 185 Posts
    Rep Power
    56

    PHP to produce graphs based on data from Excel spreadsheet

    Hello,

    I am trying to find a way to build a graph based on the data found in an excel spreadsheet. I have found some code here...

    building graphs with PHP from spreadsheets

    I have got it working with the example given but now I want to develop it further. I want to select certain data from the spreadsheet rather than just selecting everything in the array.

    The code I currently have is:-

    Code:
    <?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/wellcomm.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
    ?>
    This is the default Data Function.php file found on the above site. How can I manipulate the array to retrieve rows 2-26 and only columns 1 and 5?

    I think I know what is needed to do but don't know how to write it? I am guessing you need to put a count on the $j part for the rows but no idea on the columns.

    Can anyone help me with this?

    TIA

  2. #2

    Join Date
    Apr 2010
    Posts
    2,011
    Thank Post
    82
    Thanked 184 Times in 153 Posts
    Rep Power
    68
    Why not upload it to Google docs spreadsheet and let it build the graphs.

  3. Thanks to edutech4schools from:

    HodgeHi (26th March 2012)

  4. #3

    Join Date
    May 2006
    Location
    West Bromwich
    Posts
    2,190
    Thank Post
    299
    Thanked 215 Times in 185 Posts
    Rep Power
    56
    It's an idea, although I would be a little concerned over data confidentiality as the graphs being built are based on pupil assessment data.
    Can you manage permissions on Google docs like you would in Windows? Since its running on a web-server internally at the moment its not such a big issue.

  5. #4
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    54
    Quote Originally Posted by HodgeHi View Post
    Hello,

    I am trying to find a way to build a graph based on the data found in an excel spreadsheet. I have found some code here...

    building graphs with PHP from spreadsheets

    I have got it working with the example given but now I want to develop it further. I want to select certain data from the spreadsheet rather than just selecting everything in the array.

    The code I currently have is:-

    Code:
    <?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/wellcomm.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
    ?>
    This is the default Data Function.php file found on the above site. How can I manipulate the array to retrieve rows 2-26 and only columns 1 and 5?

    I think I know what is needed to do but don't know how to write it? I am guessing you need to put a count on the $j part for the rows but no idea on the columns.

    Can anyone help me with this?

    TIA
    Looks to be $i for rows but it takes a variable of numRows from somewhere so I would guess that the file you will need the edit is where-ever the function Spreadsheet_Excel_Reader is

  6. #5

    Join Date
    May 2006
    Location
    West Bromwich
    Posts
    2,190
    Thank Post
    299
    Thanked 215 Times in 185 Posts
    Rep Power
    56
    The spreadsheet spreadsheet excel reader is some open source code that literally just reads the contents of the spreadsheet into an array and then the data function.php file reads the array into the dateline variable and displays it in a graph, I think

    So there's no way of doing the changes in the datafuction script as the numRows would affect anything using this script to read spreadsheets?

  7. #6

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    11
    Quote Originally Posted by HodgeHi View Post
    Hello,

    I am trying to find a way to build a graph based on the data found in an excel spreadsheet. I have found some code here...

    building graphs with PHP from spreadsheets

    I have got it working with the example given but now I want to develop it further. I want to select certain data from the spreadsheet rather than just selecting everything in the array.

    The code I currently have is:-

    Code:
    <?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/wellcomm.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
    ?>
    This is the default Data Function.php file found on the above site. How can I manipulate the array to retrieve rows 2-26 and only columns 1 and 5?

    I think I know what is needed to do but don't know how to write it? I am guessing you need to put a count on the $j part for the rows but no idea on the columns.

    Can anyone help me with this?

    TIA

    I think
    $dataNum = $i-1;
    $seriesNum = $j-1;
    should be
    $dataNum = $i;
    $seriesNum = $j;

    Remember Excel number Rows and Cols from 1 to n (Not 0 to n-1) - you have already started your loops at 2

    How can I manipulate the array to retrieve rows 2-26 and only columns 1 and 5?
    Try something like
    Code:
            for ($i = 2; $i <= 26; $i++) {
                 $col_1 = $dataLines[$i, 1];
                 $col_5 = $dataLines[$i, 5];
                 // do something with this data before completing the loop
                 .........
                 // do something with this data before completing the loop
            }

  8. Thanks to mrwITch from:

    HodgeHi (30th March 2012)

  9. #7

    Join Date
    May 2006
    Location
    West Bromwich
    Posts
    2,190
    Thank Post
    299
    Thanked 215 Times in 185 Posts
    Rep Power
    56
    I just found that the software/code I downloaded to do this job isn't as free as I thought it was. It is free to download and use but has watermarks running all the way through it. Only when you purchase the code can you then remove the watermarks. So back to the drawing board on this one for me

    Apologies for the folks who when to the trouble to find a solution. It is most appreciated.

  10. #8
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    882
    Thank Post
    84
    Thanked 233 Times in 192 Posts
    Rep Power
    82

  11. Thanks to Marci from:

    HodgeHi (30th March 2012)

  12. #9

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    11
    Quote Originally Posted by HodgeHi View Post
    I just found that the software/code I downloaded to do this job isn't as free as I thought it was. It is free to download and use but has watermarks running all the way through it. Only when you purchase the code can you then remove the watermarks. So back to the drawing board on this one for me

    Apologies for the folks who when to the trouble to find a solution. It is most appreciated.
    Since you wanted to manipulate/access some cols from some rows
    Do you really need the charting functionality
    or do you just need access to data that happens to be in an Excel spreadsheet

    There are several other PHP charting libraries that you could try

    Similarly there are other ways of getting data out of Excel - if that is all you need to do

    Write up what you want to achieve - I'm sure people will come up with a few options

  13. #10

    Join Date
    May 2006
    Location
    West Bromwich
    Posts
    2,190
    Thank Post
    299
    Thanked 215 Times in 185 Posts
    Rep Power
    56
    Thanks for the feedback.

    According to the user I am looking into this for, they require the graphs as well as the data. She has an excel spreadsheet for nursery assessment data that she uses to track the progress of the pupils with. We have the graphs that do this for an overall group, i.e. how many come out green, amber red etc. She went to a meeting to provide this data only to be told that they wanted to see what progress was being made for each individual child rather than the whole group. So now she has been using the spreadsheet for a while and now needs to interrogate the data further. Data which at the moment she doesn't have any way of interrogating.

    I was going to try to develop an online system that would allow her to update the data on a site and dynamically build the graphs in real-time. I know that share point can do this with MSSQL DB backend. I am just looking a various ways at the moment.

    I have read about a PHP module which uses the GD module library called GDChart which looks promising but any builds apparently being made for OS X seem to have bugs. OS X is what I use to host my internal sites.

    Thanks for listening
    Last edited by HodgeHi; 30th March 2012 at 04:22 PM.

  14. #11

    Join Date
    Apr 2010
    Posts
    2,011
    Thank Post
    82
    Thanked 184 Times in 153 Posts
    Rep Power
    68
    Google, It's an idea, although I would be a little concerned over data confidentiality as the graphs being built are based on pupil assessment data.
    Can you manage permissions on Google docs like you would in Windows? Since its running on a web-server internally at the moment its not such a big issue.
    Probably not what you are looking for but many schools, universities and companies use Google Apps, so yes it is secure. When you make a new document it is private, you then have a choice to make that document shared with specific people, shared with a group, shared with your domain or public on the web. If something is public on the web you can view it from your website.

  15. #12

    Join Date
    Feb 2009
    Posts
    45
    Thank Post
    1
    Thanked 5 Times in 5 Posts
    Rep Power
    11
    Quote Originally Posted by HodgeHi View Post
    Thanks for the feedback.

    According to the user I am looking into this for, they require the graphs as well as the data. She has an excel spreadsheet for nursery assessment data that she uses to track the progress of the pupils with. We have the graphs that do this for an overall group, i.e. how many come out green, amber red etc. She went to a meeting to provide this data only to be told that they wanted to see what progress was being made for each individual child rather than the whole group. So now she has been using the spreadsheet for a while and now needs to interrogate the data further. Data which at the moment she doesn't have any way of interrogating.

    I was going to try to develop an online system that would allow her to update the data on a site and dynamically build the graphs in real-time. I know that share point can do this with MSSQL DB backend. I am just looking a various ways at the moment.

    I have read about a PHP module which uses the GD module library called GDChart which looks promising but any builds apparently being made for OS X seem to have bugs. OS X is what I use to host my internal sites.

    Thanks for listening
    Since this is a single User problem --
    seems a bit over the top to build some sort of web app (unless of course it is useful to all the other teaching staff once you've done it)

    From what you've described - teacher already has 'group' data charted -- presumably in the Excel file
    Why not just add in a bit of VBA code so that teacher can select one, or more, students worth of data and have that drawn as an alternative chart

    see charts.xlsm in attached zip file as an example of what I think you want
    charts.zip

    Sheet #1 shows Chart of the Group
    Sheet #2 shows chart for selected students
    Sheet #3 contains Data for charts + Selection mechanism (hopefully obvious)
    Last edited by mrwITch; 31st March 2012 at 03:41 PM.

  16. #13

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,991
    Thank Post
    851
    Thanked 2,653 Times in 2,253 Posts
    Blog Entries
    9
    Rep Power
    764

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 18
    Last Post: 4th December 2011, 10:44 PM
  2. Replies: 9
    Last Post: 6th October 2010, 03:54 PM
  3. Replies: 35
    Last Post: 12th June 2010, 01:26 PM
  4. Script to call another based on machine name
    By originofsymmetry in forum Scripts
    Replies: 5
    Last Post: 2nd May 2008, 12:04 PM
  5. Passing data from dreamweaver to an Access file
    By StewartKnight in forum How do you do....it?
    Replies: 2
    Last Post: 26th January 2007, 11:35 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •