Spreadsheet Document Template Functions

• Mar 20, 2026
• 5 minutes to read

The Data-Aware Spreadsheet control allows you to use built-in functions and operators available in the Spreadsheet control for calculation and data analysis in document template documents. This topic contains information on dedicated template-related functions that you use only in document templates.

Tip

The Spreadsheet control is designed for simple usage scenarios. For advanced report generation and Business Intelligence/data analysis, we recommend that you use our ExpressReports and ExpressDashboards Suites (available as part of the VCL Subscription +). These advanced solutions include both the powerful report generator and customizable print preview, BI dashboard designer and viewer available both at design- and runtime, smart/AI functions, non-GUI/service-based document generation, and much more.

If a template spreadsheet document is saved in the XLSX or XLS format, all formula expressions containing document template functions (template placeholder fields) are saved in the corresponding cells. Note that all placeholder fields outside of the template sections are not included in the generated document.

FIELD and FIELDPICTURE

FIELD and FIELDPICTURE functions are designed to create placeholder template fields replaced with actual values and images from the bound data source in the generated document.

FIELD

A value obtained from a data field with the name passed as a FIELD function parameter in a document template, replaces that function in the generated document. Use the following syntax in a formula expression to add a placeholder data field to one of the template sections:

=FIELD("Data_Field_Name")

Data_Field_Name is the name of the data source field from which the value is obtained.

The control displays the entered placeholder field in a template cell as the data source field name enclosed in square brackets by default, with the database icon at the cell’s upper-right corner:

All placeholder fields in template cells are displayed as formula expressions if the control’s OptionsView.ShowFormulas property is set to True:

Additionally, you can hide both database icons and template section rectangles by setting the Options.DesignView property to False:

FIELDPICTURE

The FIELDPICTURE function entered into a cell within a document template is replaced by a floating picture container that hosts an image obtained from a data field with the name passed as the function’s Data_Field_Name parameter. This picture placeholder function uses the following syntax:

=FIELDPICTURE("Data_Field_Name", "Picture_Placement", Target_Range, [Ignore_Aspect_Ratio], [Offset_X], [Offset_Y], [Width], [Height])

Picture_Placement specifies how an inserted image is positioned within the target cell range passed as the Target_Range parameter. You can use the following two picture placement options:

Range

"Range" indicates that the inserted image is scaled to fit the target range. To localize this option, use the sRange resource string.

TopLeft

"TopLeft"Indicates that the inserted image’s top-left corner matches the top-left corner of the target range. To localize this option, use the sTopLeft resource string.

The FIELDPICTURE function can accept up to five optional parameters, based on its syntax:

• Ignore_Aspect_Ratio is a Boolean parameter that indicates if the original aspect ratio of the inserted image is ignored. If the parameter is unspecified, the image retains its original height-to-width ratio;

• Offset_X and Offset_Y specifiy the absolute distance (in pixels) from the left and top borders of the target range, respectively. Both of these parameters are ignored if the Picture_Placement parameter is set to "TopLeft" or the Ignore_Aspect_Ratio parameter is set to FALSE.

• The Width and Height parameters specify the absolute dimensions (in pixels) of the inserted image.

The following table lists multiple examples of the FIELDPICTURE function used in formula expressions:

Function Syntax Example	Description
=FIELDPICTURE("PictureFieldName", "range", C2) =FIELDPICTURE("PictureFieldName", "range", A1:B2, FALSE)	Inserts a picture and scales it to fit in the specified cell range (locks the aspect ratio).
=FIELDPICTURE("PictureFieldName", "range", A1:B2, TRUE)	Inserts a picture to fit in the specified cell range (without locking the aspect ratio).
=FIELDPICTURE("PictureFieldName", "topleft", A1) =FIELDPICTURE("PictureFieldName", "topleft", A1:B2)	Inserts a picture, so that its top-left corner matches the top-left corner of the specified cell or cell range.
=FIELDPICTURE("PictureFieldName", "topleft", A1, 100) =FIELDPICTURE("PictureFieldName", "topleft", A1:B2, 100)	Inserts a picture, so that its top-left corner matches the top-left corner of the specified cell or cell range without retaining the aspect ratio.
=FIELDPICTURE("PictureFieldName", "topleft", A1, 0, 100) =FIELDPICTURE("PictureFieldName", "topleft", A1:B2, 0, 100)	Inserts a picture, so its top-left corner matches the top-left corner of the specified cell or cell range, and sets the image height to 100 pixels without retaining the aspect ratio.
=FIELDPICTURE("PictureFieldName", "topleft", A1, 120, 100) =FIELDPICTURE("PictureFieldName", "topleft", A1:B2, 120, 100)	Inserts a picture, so its top-left corner matches the top-left corner of the specified cell or cell range, and sets the image height and width to 120 and 100 pixels, respectively.

RANGE

The RANGE function specifies a template cell expanded to a range in the generated document (repeats a single template section for each record in a bound dataset).

Use the following syntax to refer to the expanded cell range in a formula expression:

=RANGE(Reference)

The Reference parameter is used to pass the absolute reference to the template cell that is copied for each data source record in the resulting document.

The RANGE function is particularly useful if you need to sum field values obtained from multiple records from the bound data source: