Scripting Excel via PHP
Posted March 24th, 2004 @ 06:57pm by Erik J. Barzeski
Here's what I need to do daily:
- Look up data in a MySQL database from a PHP script.
- Insert that data into an Excel template.
- Email the Excel template.
I can send a .xls file via email the same way I send a vCard, but only the kind you can create by tab-delimiting text and attaching it as an ".xls" file (which the recipient's machine "automatically" opens in Excel due to the extension). I have no idea how to add something to an Excel template (or how to recreate the template in PHP).
Clearly I could use AppleScript to do this, but I doubt that this will work if a user isn't logged in. Any solutions need to operate successfully when the machine is online. A root cronjob properly runs when nobody is logged in, but probably can't go around scripting Excel itself.
Donate Life
Posted 24 Mar 2004 at 7:17pm #
Have you looked at your finished product (after the data is in the template) in a text editor? Since it's Microsoft, it surely isn't something easy to decode (Read: XML), but you might get lucky.
Posted 24 Mar 2004 at 7:24pm #
Yeah, it's a binary mess which includes my full name as plain text a more than a few times to make me comfortable.
Posted 24 Mar 2004 at 8:44pm #
Does it have to be in PHP? Both Python and Perl have really nice interfaces to read/write Excel files (and not just CSV files)...
Posted 24 Mar 2004 at 8:57pm #
How about creating a CSV file (or other ASCII format) that Excel can import and use an Excel macro to format it as necessary?
Posted 24 Mar 2004 at 8:59pm #
I just googled and found some promising stuff.
I found an article on reading and writing Excel files with Perl. Part of it applied only to Perl on Windows machines but the other part used platform agnostic modules called "Spreadsheet::ParseExcel" and "Spreadsheet::WriteExcel." Going to search.cpan.org and searching for Excel turned up some other possibilities.
What version of Excel do you need to support? If it's recent enough (Excel XP?) then the file format should be able to be XML. Unfortunately I don't think Excel v.X has any XML capabilities. The ParseExcel and WriteExcel modules pre-date XML support in Excel so they might do the job.
Posted 24 Mar 2004 at 10:35pm #
No, this isn't a PHP solution, but it's one that would work from cron. Java. POI (Apache/Jakarta POI) reads and writes Excel files. JDBC to the data base, suck in the info, and spit it at a class written to use POI to output to excel and then use one of the many Java Mailers to send it. I wouldn't run it from the root crontab though, though... if you're going to run ANYTHING from root's crontab that's not a shell script or some system thing Java would probably be the thing to do it with, gotta love java's built in security protections.
Posted 25 Mar 2004 at 8:30am #
I don't know for sure that it'd accommodate a *template*, but there's a PHP Biff class that'll write in XLS format pretty reliably, and it's a cinch to use. Check it out at http://www.web-aware.com/biff/.
Posted 25 Mar 2004 at 8:36am #
There is also a PEAR package Spreadsheet_Excel_Writer that should do what you want.
Posted 25 Mar 2004 at 9:45am #
Thanks - those last two comments are very helpful. I'll look into those later today!
Posted 25 Mar 2004 at 1:30pm #
Okay, so BIFF is $1000 or so and requires installation of Zend Optimizer (which I'm not sure I wish to do). I've never installed a PEAR module either, so, that's also stepping out there.
Part of my hesitancy here isn't an unwilingness to learn. It's a cost vs. benefit analysis: I have about eight hours to do this whole project, and learning these modules would more than soak up that time.
I'm seven hours in now and everything seems to be working nicely except that the Excel document is just your typical tab-delimited variety. Hmm.
Posted 25 Mar 2004 at 10:58pm #
You should take the time to learn about maintaining PEAR -- the quality of most of the modules is excellent, and *will* pay off if you do PHP dev frequently. That being said, the PEAR modules are just PHP script files, and you can simply download them and include them (and their dependent modules, if necessary) in your code. The PEAR installer system just serves to make installations/upgrades/maintenance easier, much like Fink's apt-get. With it, you should be able to just type "sudo pear install Spreadsheet_Excel_Writer", and get the module installed and ready to include in your code (although it might require some path munging -- I haven't messed much with the default PHP install in OS X).
I've used the Spreadsheet_Excel_Writer to generate XLS versions of data from a MySQL database, and it worked quite well.
Posted 16 Apr 2004 at 7:29pm #
Y'know what, after having Mr. Mike, Child Extraordinaire, run through his (albeit meager) paces, I can't say comment registration would be such a bad thing. Nor would comment moderation. Mr. Mike, upset that his inability to comprehend simple English h...
Posted 18 Apr 2007 at 2:24am #
the PEAR package, i may say, is not so reliable. It's buggy when it comes to encoding and file versions even biff5.
Posted 13 Oct 2010 at 10:16am #
[...] person, upset that his inability to comprehend simple English has been pointed out, is on a tear (in his mind). He's leaving comments using other people's names [...]