[Salesforce] The one minute Salesforce Workbook!

Many times during my assignments, customers and partners ask: "Olivier, we need a workbook, but it will require at least 2 days to build one manually because we have so many objects. Could you help?". So I wrote this very small piece of PHP code that automatically builds an Excel workbook. And today, I am sharing it with you! Under the License GPL v2

Requirements

First of all, you need PHPExel: http://phpexcel.codeplex.com/

Then, you need the Salesforce.com PHP Toolkit: http://wiki.developerforce.com/index.php/Web_Services_API#PHP
Download the partner wsdl from your org, go to:
Setup | App Setup | Develop | API > Partner WSDL > Generate Partner WSDL
Do not forget that the partner wsdl can be shared with any org, but the endpoint is different in sandbox and in the production.

And last, check that you have a PHP environment to execute the script from the command line.
Here is a sample of a PHP configure (the step before the make):

./configure --with-apxs2=/usr/local/apache2/bin/apxs --enable-ftp --enable-bcmath --enable-calendar --with-jpeg-dir --with-png-dir --with-gd --enable-gd-native-ttf --with-freetype-dir --with-gettext --with-mysql --with-zlib-dir --with-ldap --with-openssl --enable-mbstring --enable-exif --enable-soap --enable-zip

If your execution environment is Windows, make sure that the correct DLLs are selected in your ini file.

The file system

Create a directory that will host the code.
Unzip PHPExcel in a folder called PHPExcel.
Unzip in the folder called phptoolkit the PHP Tool kit.
Copy the wdsl to phptoolkit/soapclient
Copy/paste the PHP source to a file called workbook.php

Your directory should look like (3 first levels, dir only):

olivier@Ubuntu1:~/SFDC/blog/Tortuga-crm/Post_workbook$ tree -d -L 3
.
|-- PHPExcel
|   |-- Classes
|   |   `-- PHPExcel
|   |-- Documentation
|   |   |-- API
|   |   `-- Examples
|   `-- Tests
|       |-- images
|       `-- templates
`-- phptoolkit
    `-- soapclient

The workbook.php code :

// replace with your user & password + token
$user='username@domain.com';
$password='yourPassword';
$token='yourToken';
$objectNames = array('Account', 'Contact', 'Opportunity');
$objectFields = array('name', 'label', 'type', 'length', 'nameField', 'namePointing', 'byteLength', 'calculated', 'caseSensitive', 'createable', 'updateable', 'sortable', 'custom', 'defaultedOnCreate', 'deprecatedAndHidden', 'digits', 'filterable', 'groupable', 'idLookup', 'nillable', 'precision', 'restrictedPicklist', 'scale', 'unique');
date_default_timezone_set('Europe/Paris');

define("BASEDIR1", "./phptoolkit/soapclient");
define("BASEDIR2", "./PHPExcel/Classes/");
require_once (BASEDIR1.'/SforcePartnerClient.php');
set_include_path(BASEDIR2);
require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');

echo "Connecting to Salesforce... ";
$conn = new SforcePartnerClient();
$conn->createConnection(BASEDIR1.'/partner23-prod.wsdl');
$mylogin = $conn->login($user, $password.$token);
echo "connected\n";

$excel = new PHPExcel();
$excel->removeSheetByIndex(0);  // remove 1st worksheet
$spreadsheetNb=0;
$no=1;
foreach($objectNames as $objectName) {//sheets: one per object
 echo "Sheet #".$no++.": $objectName\n";
 $objWorksheet = $excel->createSheet();
 $objWorksheet->setTitle(substr($objectName,0,30)); 
 $row=1; $col=0;
 foreach($objectFields as $objectField) $objWorksheet->setCellValueByColumnAndRow($col++,$row,$objectField); //headers
 $response = $conn->describeSObject($objectName);
 $row++;
 foreach($response->fields as $field) {//fields
  $col=0;
  foreach($objectFields as $objectField)
   $objWorksheet->setCellValueByColumnAndRow($col++,$row,$field->$objectField);
  $row++;
 }
}
echo "Saving to Excel\n";
$excelWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$excelWriter->save('workbook.xlsx');

Running the script

olivier@Ubuntu1:~/SFDC/blog/Tortuga-crm/Post_workbook$ php workbook.php 
Connecting to Salesforce... connected
Sheet #1: Account
Sheet #2: Contact
Sheet #3: Opportunity
Saving to Excel

One sheet has been generated per object:

Enjoy your 1 minute workbook!