Excel With Graphs

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mrortner
Forum Newbie
Posts: 1
Joined: Thu Jan 15, 2009 10:07 am

Excel With Graphs

Post by mrortner »

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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Excel With Graphs

Post by VladSun »

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

Post by Mark Baker »

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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Excel With Graphs

Post by Eran »

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

Post by Mark Baker »

pytrin wrote:This guy's problem is not generating the excel (as he says), but programmitaclly generate graphs in excel.
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:In my opinion this is out of scope for PHP, and probably better handled in a language with native excel bindings.
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. :?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Excel With Graphs

Post by Eran »

I'll be gladly looking forward to your excel contributions :)
Did you consider compiling a PECL extension for handling excel files?
Post Reply