Using Microsoft Excel Template
You can create a Microsoft Excel .xlsx email attachment using a Microsoft Excel template.
The resulting Microsoft Excel document can include column headers and data cells containing:
- Variables that refer to column values in your tables
- Expressions that yield computed values
- Start Expressions that display the rows of a table.
- If Expressions that allow you to include or exclude portions of a template.
Creating the Template
You can create a Microsoft Excel template file as described here.
Template Start Expression
Typically the first row in the Excel template contains column header values.
The second row in the Excel template contains a Start expression, one or more cell data values, and an End expression.
- The leftmost cell contains a Start expression immediately followed by a data value or Microsoft Excel worksheet formula.
- Intermediate cells contain data value or Microsoft Excel worksheet formulas.
- The rightmost cell contains a data value or Microsoft Excel worksheet formula immediately followed by an End expression.
The Start expression retrieves a set of rows from a table you specify. Each retrieved table row populates a row in the resulting Excel file.
In the following example, the Start expression retrieves rows from the Customer table. The resulting Excel file contains one row for each customer who is 21 years of age or older.
Your template can include Excel worksheet formulas. In the following template, the Total column contains a worksheet formula which multiplies Price and Qty.
You can apply Excel formatting to the cells of the template. For example, the Price cell could be formatted as a Currency value.
Your Excel workbook template can contain one or more worksheets each of which is a template. The resulting ,xlsx file will contain each of these worksheets.
Viewing or Editing the Template
Once the template is created, click the View button to view the template.
The View button does not work well for editing Microsoft Excel templates because of built-in limitations in the way Microsoft Excel files can be opened and edited from the web. Instead, login to your data source and edit the Microsoft Excel template file there.
- Excel tables are not supported. Templates must be simple worksheets.
- Excel pivot tables are not supported.
- Nested tables cannot be displayed in the same Excel worksheet. For example, you cannot display each Order record followed its child Order Detail records.
You may see the following Excel error message when you attempt to open the resulting Excel .xlsx file.
Excel cannot open the file <filename.xlsx> because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
This error can occur if you use a Google Docs template file rather than a Excel template file. If you inadvertently use a Google Docs template file, the output is an HTML file that may look like a Excel worksheet but it will not be a valid Excel .xlsx file.
To create a valid Excel template file, set the HTTP Content Type to "XLSX" and click the Create button following the template. This will create a valid Excel template file which you can then customize.