Monday, October 24, 2011

[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!

5 comments:

  1. With cloud computing, you eliminate those headaches because you’re not managing hardware and software—that’s the responsibility of an experienced vendor like salesforce.com. The shared infrastructure means it works like a utility: You only pay for what you need, upgrades are automatic, and scaling up or down is easy. Thanks for sharing this.


    Salesforce Training in Chennai
    Salesforce Certification

    ReplyDelete
  2. I have read your blog, it was good to read & I am getting some useful info's through your blog keep sharing... Informatica is an ETL tools helps to transform your old business leads into new vision. Learn Informatica training in chennai from corporate professionals with very good experience in informatica tool.
    Regards,
    Best Informatica Training In Chennai|Informatica training center in Chennai

    ReplyDelete
  3. The information you have given here is truly helpful to me. CCNA- It’s a certification program based on routing & switching for starting level network engineers that helps improve your investment in knowledge of networking & increase the value of employer’s network, if you want to take ccna course in Chennai reach to us, thanks for sharing…
    ccna training in Chennai | ccna training institute in Chennai

    ReplyDelete
  4. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    Regards,
    sas training center in Chennai|sas training in Chennai|sas training institute in Chennai

    ReplyDelete
  5. Thanks you very much for sharing these links. Will definitely check this out..
    ccna courses london

    ReplyDelete