Contact us Blog

Generating Excel (.xlsx) files

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:

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:

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.

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

  1. 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
  2. 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.