Overview #
Here we discuss how to generate spreadsheet-formatted (.xlsx) files from Excel templates. We assume here that you are familiar with how to use Apsona’s merge tool.
We say Excel template to mean a plain old .xlsx file with merge fields added. A merge field is just a place-holder that will be replaced with an actual data value. Typically, an Excel template will include the merge fields in the right places, as well as the colors, fonts and layout selected according to the way you want your finished Excel document to look.
Below are two screen shots, one illustrating an Excel template, and the second showing the results of using the template with some sample data. You can download the Excel template and examine it. You can also download the merge action that produced this document, and install the merge action into your Salesforce org to test it out.
When processing an Excel template, Apsona restricts its activity to replacing your merge fields with actual data from the data source, and adjusting any formula fields as necessary. It preserves all the worksheets, color, formatting and layout of your template. This ensures that its final output contains your report data with all the formula calculations carried out, and all the charts and pivoting calculations executed.
Merge fields #
The Excel generator recognizes three kinds of merge fields.
Replicated fields #
appear in a row, and indicate that the row of fields is replaced with as many rows as needed to accommodate all the data rows that match the row. For instance, in the screen shot above, we wish to produce a list of Opportunity records, so we use replicated fields as place-holders for Opportunity data. So the fields {{Closes}}, {{Amount}} and {{Stage}} are replicated fields, since they are part of a row that is replicated once for each data record.
The names of replicated fields must follow some conventions:
- Field names should be surrounded by double-curlies (i.e., should have two opening curly brackets at the left, and two closing curly brackets at the right) and should contain only alphabets, numerics and underscores (specifically, no spaces). For example,
{{CampaignName}}and{{total_products_2013}}are valid merge field names that Apsona will recognize. But{{my field}}is not, because it contains an embedded space, and neither is{{MyOtherField}(because it has only one closing curly bracket). - When you create merges with sub-lists or record groups, you can make up a record group name and include it as part of the merge field. More on this below, where we discuss record groups.
Single-value fields #
are meant for use where you need to replace just a single value, not an entire list of data rows. In template shown in the above screen-shot, the fields {$AccountName}, {$AccountPhone} and {$Website} are single-value fields, because they represent the data for one single Account record, not a list of Accounts. A valid single-value merge field begins with {$, ends with a single curly } and contains only alphabets, numbers and underscores. You could use a single-value field when, for instance, you are generating a “master-detail” Excel document containing “master” information about a specific data record (an Account, in the above example). The “detail” information about the Opportunities for that Account is produced using replicated fields, because there are several Opportunity records laid out in a list or table.
Built-in fields #
Three built-in fields are available:
{$built_in__:Today}is the date and time at which the Excel document is generated. If you use this field in a cell, make sure that the cell is formatted to Date/time, otherwise it will show a numeric value. Also note that this is not the same as using the ExcelTODAY()function, because the Excel function returns the current date, i.e., the date on which the user opened the Excel file. But the merge field shows the date on which the document is generated, which could be much earlier.{$built_in__:UserName}is the name of the user who generates the document.{$built_in__:UserEmail}is the email address of the user who generates the document.
Single vs multiple output documents #
When you run the Excel generator from a list containing multiple records, you can opt to produce either a single Excel document, or a zip file containing one Excel document for each record in your list. You can set this option via the choices in step 2 of the merge action builder.
Single output document #
This choice is the most common one, and is appropriate when you wish to merge all the data in your data source into the one Excel document you are producing. You might also use this choice when you are aggregating a bunch of unrelated reports into a single Excel document, perhaps in different worksheets of the document, and perhaps producing one or more summary worksheets with pivot tables or charts.
Multiple output documents #
In some situations, you might want to produce an Excel sheet with a master-detail-style rendering. For example, in the screen shots in the first section above, we need an Excel sheet with the details of a single Account and some of its related Opportunities. When applied to a list of Accounts, you would want a collection of Excel sheets, one per Account. In such a situation, you would select the second option “Zip file with one xlsx file for each record” for output.
When you select this choice, the Excel generator produces a zip file containing one Excel file for each data record in your data source. You can select the field that should be used for naming the Excel files in the output zip file. Then for each record, the content of that field in the record becomes the name of the Excel file for that record in the generated output. For example, if you generated Excel files from the Account object, and used the Account Name as the field to use for file name, then the generated zip file contains Excel files whose names are the Account Names in the record list you used.
How templates are processed #
The Excel document generator processes replicated merge fields using what we call template rows. Each template row contains one or more replicated fields to be replaced with data. When the template row is processed, the document generator removes the template row and replaces it with as many data rows as are provided by the data source. In doing so, the generator rebuilds any formula fields in the template row so that the corresponding formula fields in the output rows refer to the correct places. It also rebuilds any other formula fields in the worksheet as needed.
To illustrate, consider the small template below. Here we have a template row in row 4, with four merge fields, Name, Stage, Amount and Date. We also have a row-level formula in cell G4 for the Commission amount, and two summary formulas in row 7. The row-level formula for commission calculation, in cell G4, refers to another cell E4, the Amount merge field in the same row. The summary formulas in row 7 are deliberately set up so that their low and high ends both refer to the same cell – the cell which will be expanded to multiple rows when the template is expanded.
Note also that merge field cells should be set up in the correct format. For example, with the template above, right-click on the merge field cell for Amount (cell E4) and set its format to be currency. Similary, set the format of the Date cell F5 to be a date. These formats will then be carried over to all the expanded rows in the output template.
Suppose that, when this template is expanded, we obtain four data rows from the data source. Then the expanded result will contain the data in rows 4 through 7, and the formulas in column G will be adjusted accordingly. Also, the template row 7 will move down to row 10, and its formula fields will be adjusted accordingly – see below.
Apsona does not touch or propagate cross-sheet Excel formulas, i.e., formulas which use sheet names in them (e.g.,
Data sheet!A43 where Data sheet is another sheet’s name). This is because there is no way of knowing how to propagate such formulas. So if you use a cross-sheet formula in a part of a template that is being expanded, all expanded occurrences of the formula will contain the same value.
Multiple reports in one Excel template #
To create a template that renders multiple reports, Apsona offers record groups. By default, each template row in the template obtains its data from a default data source. But you can qualify the first (leftmost) cell of a template row with a record group name. You do this by prefixing the field name with the record group name followed by a colon. Doing so tells Apsona to treat all the merge fields in that template row as belonging to that record group. In the example below, we have a record group named ClosedOppties containing the four merge fields, Name, Stage, Amount and Date.
You can select any name you wish for a record group. The only constraints are that
- the record group name must be unique, i.e., there can be no other merge field or record group with the same name anywhere else in the spreadsheet; and
- the record group name must contain only alphabets, numerics and underscores.
When you create a merge action using such a template, Apsona recognizes all the record groups in the template, and lets you provide a separate data source for each record group. So you can (for example) create a template with three worksheets, one for the raw Opportunity data and one each for closed and open opportunities. And you can populate each record group using a different Apsona report.
Email messages with .xlsx attachments #
You can also create customized email messages with spreadsheet attachments, just like email with document attachments. The steps used for this process are described in this article.
