Wednesday, September 14, 2005

Automatic Salesforce Excel reports with PHP

Today, I gave a talk at Salesforce.com's dreamforce conference as part of their "Gearing Up: Tools for Your Next Project" developer tools session.

I must admit that I was pleasantly surprised as to how many in the audience were actually using PHP. It must have been about 50% of the audience and this was without PHP being explicitly mentioned on the session agenda.

In my talk I showcased two features of PHP 5, the new SOAP extension and the rewritten COM extension. The example I used is a script that creates an Excel report (with graph), of the geographic distribution of Salesforce leads.

The real interesting part of this example is actually how to create the Excel graphs. Probably one of the easier ways to do that is to use Visual Basic from within Excel. However, as at Zend we want to do everything with PHP, and there are advantages of automating the process from the server, our challenge was how to actually create the Excel files from PHP. We decided to automate Excel using COM and PHP on Windows. The main challenge for us was the lack of good Excel COM documentation. Creating graphs using COM is not a trivial task, especially as a lot of the material isn't well documented by Microsoft. The only somewhat useful reference is the Excel Visual Basic CHM (called VBAXL10.CHM), however, a lot of those features don't map directly into COM, nor are the constant values of the graph types listed in the docs, so you have to do a lot of guess working to actually get it working. We literally had to guess the constant ids for creating the various Excel graphs. After quite a lot of guess work we finally managed to brew up a class which made it easy for us to populate Excel with Data, Worksheets, and create the required graphs. This work is freely available at http://gutmans.org/COM_excel.php. If you use or enhance this code, if possibe, please be sure to share it with me. I'd like to hear from your experiences. Currently, it is very much tailored to what our requirements were.

The second piece of the example is actually getting the data out of Salesforce. Zeev and George have both blogged about accessing Salesforce so I won't add much more. In a nutshell what my code does is query Salesforce for leads, calculate the distribution by country, and display an Excel graph with the resulting information (the results are skewed so that I don't give away real Zend info but it really works...)

I've put up the script which glues the SOAP interoperability and Excel together at http://gutmans.org/SF_COM_example.php. Besides the username, password and exact query itself the script is complete.

I hope this info, especially the Excel class helps people out. Share your experiences!