Subscribe to
Posts
Comments
NSLog(); Header Image

Scripting Excel via PHP

Here's what I need to do daily:

  1. Look up data in a MySQL database from a PHP script.
  2. Insert that data into an Excel template.
  3. 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.

14 Responses to "Scripting Excel via PHP"

  1. 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.

  2. Yeah, it's a binary mess which includes my full name as plain text a more than a few times to make me comfortable.

  3. 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)...

  4. How about creating a CSV file (or other ASCII format) that Excel can import and use an Excel macro to format it as necessary?

  5. 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.

  6. 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.

  7. 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/.

  8. There is also a PEAR package Spreadsheet_Excel_Writer that should do what you want.

  9. Thanks - those last two comments are very helpful. I'll look into those later today!

  10. 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.

  11. 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.

  12. 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...

  13. the PEAR package, i may say, is not so reliable. It's buggy when it comes to encoding and file versions even biff5.

  14. [...] 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 [...]