EXCEL Generator

Create Excel (XLSX) reports with data from Salesforce. Datadumps or complex groupings … We got you covered.

Example: Above video explains same example. Let’s say, you need to create an Excel report of Salesforce Opportunities. Follow below steps to achieve this.

1. Create an Excel file with merge fields. Here is the sample excel file you can download and make use of it.
   Few things that needs to be considered while creating excel file are
   – After adding merge fields, select the entire row of merge fields and give a name(Let’s say “opportunity”) in the Name box and press enter. Then save the excel file. As PDF Butler need to know which region in the excel should be repeated, this has to be done.
   
   – You can format the column data according to the data type of the merge field. Let’s say, you have a Date column and wanted to format as short date in the final report. You can do this in excel file by selecting that Date merge field and choosing Short Date in the number format section.
   
2. Create SOQL Data Source to fetch opportunity records as below.
   
3. Create Doc Config of Main Excel Document type as shown below.
   
4. Open PDF Builder from the Doc Config and add the Data Source.
5. Upload the Sample excel file with merge fields in PDF Builder in Doc Config Documents sections.
6. Create Region Configtype for the repeating row(opportunity) which you have mentioned in the excel file.
7. Create Configtype for each merge field in the excel file as a child for Region configtype.
   
8. Edit opportunity page to add the Excel DocConfig id.
   
9. Go to any opportunity and download the report. The report will fetch the opportunities from salesforce and displays all the information according to the format you have given in your doc config document excel file.
   

PERCENT field #

Need to handle a Salesforce PERCENT field in Excel? Salesforce does not store Percent fields with decimals eg it will store 75% as 75. While Excel expects 0.75 to show 75% for a Percentage field. To achieve this, check the option Check this box if the cell type in Excel is set to “Percentage” for the percent configtypes in pdf builder.

In the above example, probability is a percent field and you have to check the above option if you formatted the merge field as percent in the excel file as shown below.

#

Images #

Repeat images for each region you replicate in the Excel. Perhaps you want to show a thumbnail of a product in a list

See this video for more information:

For more information on the PICTURE DataSource: PICTURE DataSource – PDF Butler

Example: Let’s say you want a report of opportunity products with image of each product in the row. You already understood how to create an excel file with merge fields in the beginning of this topic. Insert the picture placeholder in picture cell and add merge field to the picture placeholder. You can download the placeholder here.

Follow below steps.

1. Create SOQL Data Sources to fetch the opportunity and opportunity products data. To fetch the product pictures, create picture list data source as a child for opportunity products Data Source as shown below.
2. Create Excel type Doc Config and upload the sample document from here. Add created Data sources in Doc Config pdf builder.
   
3. Create Configtypes for each column in the excel file. Remember to Create Picture Configtype for picture column and map it to picture list data source as shown below.
   
4. Edit opportunity page to add the Image Repeater Doc Config id. Go to any opportunity and download the report. The report will fetch the opportunity products with Images in each row.
   

Inline functions #

Creating function in rows that are repeating by a REGION is possible by only using standard Excel functions. Use the Excel INDIRECT function to dynamically identify the row.

Examples:

• Multiply the value in column “E” with a value in another sheet: =INDIRECT(“E” & ROW()) * ‘Static Data’!B1
• “IF” function to compare the value in column “F”: =IF(INDIRECT(“F”&ROW())>1000;”YES”; “NO”)

Demo with Inline functions:

Summations/Averages/… #

If you are repeating REGIONs, often you want a total of all values. You can just use the SUM function in Excel and specify a 1 cell region for the SUM. When generating, PDF Butler will automatically rewrite the region for the SUM.

In the example below, we want the SUM of all fixed costs when the are repeated. The template will use cell “F8” for this example. So we create a region of this cell “F8:F8”

Demo with Inline functions:

LINK ConfigType #

Add links (URL) to your Excel. This can be used from a SINGLE or from a LIST DataSource.

Demo:

RICH_TEXT ConfigType #

Excel support a downgraded version of Rich Text. So we cannot support all features but only what Excel allows. This can be used from a SINGLE or from a LIST DataSource.