ExcelTemplate in Depth > Creating Data Markers

Creating Data Markers

An ExcelWriter template is a spreadsheet created in Microsoft Excel that contains data markers. A data marker is a cell value beginning with %%= or %%=$ that specifies a database column, a variable, or an array to insert in the spreadsheet column containing the marker. Data markers are created in Microsoft Excel and bound to data sources in code. When you run the code, ExcelWriter populates the data markers with values from a data source.

Refer to the ExcelTemplate Code Samples to see live demonstrations of various ExcelTemplate applications. Click the "View template" link on each sample page to see the template and view the Data Markers.

A data marker binds in script to a data source which may be a variable, an array, or an ADO.NET DataSet, DataTable, or DataReader. A data marker may include modifiers. Data source and field numbers are 1-based. If ExcelWriter encounters %%=#0[.field] or %%=[DataSource.]#0, an error will occur.

Implicitly or explicitly, a data marker always specifies both a data source and a column - even if the data source is a variable or a one-dimensional array. For example, in the data marker:

	%%=Products.ProductID

"Products" is the data source, and "ProductID" is the column. And the data marker:

	%%=ProductID

Means the "ProductId" column from the first data source defined in the ExcelWriter code.

Both the data source and the column can be specified by either name or number:

%%=Products.ProductID

%%=#2.FirstName

%%=Employee.#3

%%=#2.#3

Data Marker Names

Follow these rules when naming data markers:

• Data source and column names must not include Unicode characters.



• Data source and column names must begin with a letter (A-Z, a-z).



• Data source and column names may include the following characters only:
  
  ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890_



• Spaces are not allowed anywhere in a data marker except within a column name in brackets (for example, %%=Products.[Product Category].

To include spaces in the data source column name, use this format:

For example:

The [Column Name] format allows you to match any SQL column name exactly. Legal characters within the brackes are:

Data Marker Formats

Data Marker Formats
For this data source:	And this method:	Use one of these data marker formats:
DataSet DataTable DataReader	SetDataSource	%%=DataSourceName.ColumnName[(modifier)] %%=#DataSourceNumber.#ColumnNumber[(modifier)] %%=DataSourceName.#ColumnNumber[(modifier)] %%=#DataSourceNumber.ColumnName[(modifier)] %%=ColumnName[(modifier)] %%=#ColumnNumber[(modifier)]
Variable	SetCellDataSource	%%=$DataSourceName[(modifier)] %%=$#DataSourceNumber[(modifier)]
1-Dimensional Array	SetColumnDataSource	%%=$DataSourceName[(modifier)] %%=$#DataSourceNumber[(modifier)]
1-Dimensional Array	SetRowDataSource	%%=DataSourceName.ColumnName[(modifier)] %%=#DataSourceNumber.#ColumnNumber[(modifier)] %%=DataSourceName.#ColumnNumber[(modifier)] %%=#DataSourceNumber.ColumnName[(modifier)] %%=ColumnName[(modifier)] %%=#ColumnNumber[(modifier)] Note on backward compatibility
2-dimensional Array	SetDataSource	%%=DataSourceName.ColumnName[(modifier)] %%=#DataSourceNumber.#ColumnNumber[(modifier)] %%=DataSourceName.#ColumnNumber[(modifier)] %%=#DataSourceNumber.ColumnName[(modifier)] %%=ColumnName[(modifier)] %%=#ColumnNumber[(modifier)]

Note on Backward Compatibility In ExcelWriter 4, a $ was required for all data markers that bind to 1-dimensional arrays. In ExcelWriter 5 and 6, if a 1-dimensional array binds to a row of data markers by the method SetRowDataSource, the $ should be omitted. However, for backward compatibility, if you include the $, the data marker will be populated without error.

Using Short Data Markers

ExcelWriter supports several full-length and short data marker forms. When you use any of the long data marker formats, you explicitly include both the data source name or number and the column name or number. Short formats do not specify both data source and column explicitly; one or the other is omitted. How ExcelWriter understands a short data marker depends on whether or not the marker contains a $.

ExcelWriter understands %%=$Employee as the first column of the data source "Employee." If you omit the $, ExcelWriter will read "Employee" as a column from the first data source defined in the ExcelWriter code. So:

• %%=$Employee means the first column in the data source "Employee."
• %%=Employee means the "Employee" column in the first data source defined in the ExcelWriter code.

Examples

Long Data Markers

When you use any of the long data marker formats, you explicitly include both the data source name or number and the column name or number. Using the long formats may prevent confusion when writing the ExcelWriter code, since you must be able to distinguish between data source and column to set the data source in code.

Examples

Data Marker Modifiers

An ExcelWriter data marker can include modifiers. Modifiers are included in parenthesis at the end of the data marker. For example:

	%%=Products.ProductID(Lowercase)

ExcelWriter supports the following data marker modifiers:

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.