Create a Excel(.xlsx, .xlsm) template file when outputting Excel files.
Formatting of Merge fields
Date
Serial values are output. Set the date format from "Format Cells" in Excel.
Currency
The currency symbol is not output. Set the format to display the currency symbol from "Format Cells" in Excel.
Decimal Point
Set the decimal point from "Format Cells" in Excel.
3-digit commas in numbers/currency
Three-digit commas are not output. Set the comma delimiter format from "Format Cells" in Excel.
Long Text Area
Set the Alignment to "Wrap text" from "Format Cells" in Excel.
Memo
When creating the Excel template, enter the sample values, adjust the cell formatting, and then set the merge field.
Excel Formulas
Excel formulas can be used in the template.
- Formulas that specify a range, such as the total value of child records, should be specified as "child record first row cell:child record first row cell" (e.g., A1:A1). When outputting, the range is changed to "A1:A3" according to the number of records in the child records.
- If the formula refers to a cell in the child record row and there are no records in the child record, the formula is output as "=0".
- After entering the formula, please set the formatting again. After entering a formula in a cell, the formatting may automatically change to general.
- Merged fields cannot be set within a formula. NG e.g. ="{!Opportunity.Amount}"+"{!Opportunity.Tax__c}"
- Complex layouts are not supported. If the output is not correct, such as a misalignment of formulas, create formula fields in Salesforce and set the formula field as merge fields.
Multiple Sheet Output
Merge fields can be set on multiple sheets. There is no limit to the number of sheets, but if there are many output contents, the governor's limit may cause an output error.
Template Sample
Download Sample Template Files
Limitations
- Excel features such as graphs and pivot tables are not available.
Reference
Generate an Excel file in Salesforce using Office File Creator – (1) Create a template file
Setting Formulas in Excel Template Files with Office File Creator
Troubleshooting
When outputting Excel files, the long text area fields are output as a single line without line breaks.
Set the Alignment to "Wrap text" from "Format Cells" in Excel.
When outputting Excel files, the currency symbols are not output.
The currency symbol is not output. Set the format to display the currency symbol from "Format Cells" in Excel.
When outputting Excel, the 3-digit comma symbol in numerical values is not output.
Three-digit commas are not output. Set the comma delimiter format from "Format Cells" in Excel.
When outputting Excel files, decimal points are truncated.
Use Excel's cell formatting to set the decimal point to be displayed.
When outputting Excel files, formulas in Excel are not output correctly. Formulas are output incorrectly.
If the Excel formula does not output correctly, create a formula field in Salesforce and set the formula field as a merge field. It is possible that the template is not supported by the application.
When outputting Excel files, Graphs and pivot tables do not output correctly.
Excel features such as graphs and pivot tables are not available.
When Outputting Excel files, the error message "We found a problem with some content in 'filename.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." is shown, and when opening the file, the contents are output as empty.
OFC may not support the output pattern. Try the following.
- If the file has a complex layout, simplify the layout.
- In Excel, delete unused rows and columns. Excel normally saves rows with values and formatting, but for some reason an empty row may be recognized as a used cell and a large number of unnecessary rows are saved.
- In Excel, check for hidden and unnecessary sheets, and delete any unnecessary sheets.
- If Office functions (e.g., graphs, pivot tables, etc.) are used, remove Office functions. OFC may not support them.
- If images are included in the template file, reduce the file size of the images by lowering the image quality.