Excel With Graphs
Moderator: General Moderators
Excel With Graphs
I need to create a specific excel spreadsheet from PHP for my company. I was given an excel spreadsheet from them and they want me to duplicate it with php pulling the data from mysql. Its not a problem to get the data from mysql to excel. THe problem is they want a graph page that is created from the data in the excel spreadsheet. Next problem is they don't want the graph to be an image but to be generated by excel so the graph is changeable in the excel. Anyone do this with php or with a combination or some language?
Re: Excel With Graphs
I think that if there is a way to do this in PHP, it will be by using Excel COM object, but it would require a Windows server.
There are 10 types of people in this world, those who understand binary and those who don't
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: Excel With Graphs
As VladSun has noted, the only way to actually generate an Excel in PHP (currently*) that is capable of including a genuine Excel chart is by using Excel COM object, which requires a Windows server.
One potential alternative might be to use a 'template' workbook that reads the data using the Data/Import External Data functions of Excel, set to automatically refresh from that external data source when it is opened, and which has a graph worksheet based on the imported data.
I don't believe that Excel can query against MySQL directly, unless you can set up access to MySQL via ODBC; but you could use a cron task written in PHP or any other scripting language to generate that data source from MySQL, either shifting it to an Access or MSSQL database (or any db that Excel can query via an OLAP connection), or even to a plain CSV.
* I'm working on this, but estimate that it's going to be at least another year before Excel charts are included within PHPExcel
One potential alternative might be to use a 'template' workbook that reads the data using the Data/Import External Data functions of Excel, set to automatically refresh from that external data source when it is opened, and which has a graph worksheet based on the imported data.
I don't believe that Excel can query against MySQL directly, unless you can set up access to MySQL via ODBC; but you could use a cron task written in PHP or any other scripting language to generate that data source from MySQL, either shifting it to an Access or MSSQL database (or any db that Excel can query via an OLAP connection), or even to a plain CSV.
* I'm working on this, but estimate that it's going to be at least another year before Excel charts are included within PHPExcel
Re: Excel With Graphs
The CSV option is the easiest. This guy's problem is not generating the excel (as he says), but programmitaclly generate graphs in excel. In my opinion this is out of scope for PHP, and probably better handled in a language with native excel bindings.
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: Excel With Graphs
Yup, the structure of the chart data in xls or xlsx isn't the easiest to fathom, let alone build dynamically though any tool other than Excel itself. Just for luck, it also duplicates the data values rather than simply pointing to the cells containing the data.pytrin wrote:This guy's problem is not generating the excel (as he says), but programmitaclly generate graphs in excel.
I have to disagree with you on that though, else I'd be looking for another open source development project to keep me occupied and out of trouble.pytrin wrote:In my opinion this is out of scope for PHP, and probably better handled in a language with native excel bindings.
Re: Excel With Graphs
I'll be gladly looking forward to your excel contributions 
Did you consider compiling a PECL extension for handling excel files?
Did you consider compiling a PECL extension for handling excel files?