This article is a step-by-step guide to setting up the output of Excel files in Salesforce using Office File Creator, along with examples. The main steps are template file creation and template record creation. For installation and authorization procedures, please refer to this page.
Create a template file
Prepare an Excel file. In this article, I will create a template file using the following invoice file as an example. The records to be output are Opportunity as the primary object and OpportunityLineItem as the related object.
First, set the Account name. The Account name is the standard field Account in Opportunity. The merge field is {!Opportunity.Account}.
Next, set the billing date. The billing date is created as a custom field with the API name BillingDate__c and data type date. The merge field is {!Opportunity.BillingDate__c}.
To output a date field in Excel, format the cell as a date type. It is recommended that you enter the sample values and set the cell formatting, then set the merge field.
Enter a sample date and format the cell.
After adjusting the cell format, set {!Opportunity.BillingDate__c} in the Billing Date cell.
Memo
Excel internally has dates as numbers. For example, if you enter the date 6/1/2022 and look at the standard in the cell format, you will see that the number 44713 is stored.
Office File Creator outputs serial values for date types when outputting to Excel. Date types should be set to date in the cell formatting because if the cell format is not a date but a standard or string, the serial value will be displayed.
Next, set the billing number. The billing number is created as a custom field with the API name BillingNumber __c and data type text. Set the merge field {!Opportunity.BillingNumber__c} in the cell.
Next, set the subject line. The subject is the standard Name field. Set the merge field {!Opportunity.Name}.
Next, set the billing amount. The billing amount is created as a custom field with the API name TotalIncludingTax__c and data type Currency. Set the cell formatting to Currency and set it to include the $ sign.
Enter the sample amount to check the formatting.
Set the merge fields {!Opportunity.TotalIncludingTax__c} in the cell.
Memo
In the record detail screen of Salesforce, fields of the currency type are displayed with a currency symbol, such as the $ sign, but in Office File Creator, the currency symbol is not output.
In Excel, set the currency in the cell formatting and set the $ sign to be output.
In Word, PowerPoint, and PDF, set the currency symbol at the beginning of the merge field, such as ${Opportunity.Amount}.
Next, set up the OpportunityLineItems in the related object of Opportunity. The child relationship is OpportunityLineItems. Since the Price and Subtotal fields are currency types, set them to currency in the cell formatting so that the $ sign is output.
Enter sample values and adjust cell formatting.
Set each merge field on the first line.
Product: {!OpportunityLineItems.Product2}
Product Code: {!OpportunityLineItems.ProductCode}
Price: {!OpportunityLineItems.UnitPrice}
Quantity: {!OpportunityLineItems.Quantity}
Subtotal: {!OpportunityLineItems.TotalPrice}
To set a sequential number to the first column No., set Office File Creator's function {!number()}. The {!number()} function allows setting sequential numbers for related records in the output order.
After setting the first line of the merge field of the related object, delete the second and subsequent lines. In this example, lines No. 2 through No. 10 are deleted. When outputting, the number of lines corresponding to the number of records of OpportunityLineItems will be output.
Next, set the Subtotal, Sales Tax, and Total of Opportunity. Each has been created with a currency type as a custom field. Since these are currency fields, set the cell formatting to currency and set the $ sign to be output.
Subtotal: {!Opportunity.SubTotal__c}
Sales Tax: {!Opportunity.Tax__c}
Total: {!Opportunity.TotalIncludingTax__c}
Next, set the Installation Support. Installation Support is created as a custom field with the API name IsInstallationSupport__c and data type checkbox type. Set the merge field {!Opportunity.IsInstallationSupport__c} in the cell.
The template file is now complete.
The next step is to create a template record.
Please refer to Generate an Excel file in Salesforce using Office File Creator – (2) Create a template record.
