HodgeHi Posted March 26, 2012 Posted March 26, 2012 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:- 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
edutech4schools Posted March 26, 2012 Posted March 26, 2012 Why not upload it to Google docs spreadsheet and let it build the graphs. 1
HodgeHi Posted March 26, 2012 Author Posted March 26, 2012 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.
cromertech Posted March 26, 2012 Posted March 26, 2012 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:- 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
HodgeHi Posted March 26, 2012 Author Posted March 26, 2012 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?
mrwITch Posted March 26, 2012 Posted March 26, 2012 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:- 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 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 } 1
HodgeHi Posted March 30, 2012 Author Posted March 30, 2012 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.
Marci Posted March 30, 2012 Posted March 30, 2012 /me points at jqPlot Charts and Graphs for jQuery 1
mrwITch Posted March 30, 2012 Posted March 30, 2012 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
HodgeHi Posted March 30, 2012 Author Posted March 30, 2012 (edited) 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 Edited March 30, 2012 by HodgeHi
edutech4schools Posted March 31, 2012 Posted March 31, 2012 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.
mrwITch Posted March 31, 2012 Posted March 31, 2012 (edited) 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) Edited March 31, 2012 by mrwITch
SYNACK Posted March 31, 2012 Posted March 31, 2012 If it is in excel anyway why not just use a VBS and excel to crack out a pretty graph instead. It may be simpler in the long run: How to draw a chart in Excel using VBScript? « QTP Lab:- A touch of madness! How to automate Excel from a client-side VBScript Hey, Scripting Guy! How Can I Save an Office Excel Chart as a Picture? - Hey, Scripting Guy! Blog - Site Home - TechNet Blogs Automatically Create Chart Images
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now