Creating Data Markers

The "ProductID" column of the data source "Products."

The "FirstName" column of the second data source defined in the ExcelWriter code.

The third column of the "Employee" data source.

The third column of the second data source defined in the ExcelWriter code.

BindData
SetDataSource(deprecated)

%%=DataSourceName.ColumnName[(modifier)]
%%=#DataSourceNumber.#ColumnNumber[(modifier)]
%%=DataSourceName.#ColumnNumber[(modifier)]
%%=#DataSourceNumber.ColumnName[(modifier)]
%%=ColumnName[(modifier)]
%%=#ColumnNumber[(modifier)]

BindCellData
SetCellDataSource(deprecated)

%%=$DataSourceName[(modifier)]
%%=$#DataSourceNumber[(modifier)]

BindColumnData
SetColumnDataSource(deprecated)

%%=$DataSourceName[(modifier)]
%%=$#DataSourceNumber[(modifier)]

BindRowData
SetRowDataSource(deprecated)

%%=DataSourceName.ColumnName[(modifier)]
%%=#DataSourceNumber.#ColumnNumber[(modifier)]
%%=DataSourceName.#ColumnNumber[(modifier)]
%%=#DataSourceNumber.ColumnName[(modifier)]
%%=ColumnName[(modifier)]
%%=#ColumnNumber[(modifier)]

BindData
SetDataSource(deprecated)

%%=DataSourceName.ColumnName[(modifier)]
%%=#DataSourceNumber.#ColumnNumber[(modifier)]
%%=DataSourceName.#ColumnNumber[(modifier)]
%%=#DataSourceNumber.ColumnName[(modifier)]
%%=ColumnName[(modifier)]
%%=#ColumnNumber[(modifier)]

%%=#7 Seventh column of the first data source defined in the ExcelWriter code.

%%=$#7 First column of the seventh data source defined in the ExcelWriter code.

%%=OrderId "OrderId" column from the first data source defined in the ExcelWriter code.

%%=$Orders First column in the data source "Orders."

%%=Employees.FirstName "FirstName" column in the "Employees" data source.

%%=Products.#3 Third column of the data source "Products."

%%=#2.ProductId "ProductId" column of the second data source defined in the ExcelWriter code.

%%=#3.#2 Second column of the third data source defined in the ExcelWriter code.

%%=Employees.[Street Address] "Street Address" column of the data source "Employees."

Signifies the beginning of a grouping block. Place this marker in the column of the field by which you wish to group. Grouping and Nesting formatting will be applied to the rows from this marker to the %%endgroup marker. This row will not be displayed in the output spreadsheet. The group marker is required for grouping functionality to work.

Signifies the beginning of the header rows for a particular column in a grouping block. This marker should be placed in a row directly under the row containing the %%group markers or other header rows and above the data marker row in the column. All rows between this marker's row and the data marker row, or the next header marker, will be repeated for each new value in the grouping field. The row containing the %%header marker will not be displayed in the output spreadsheet. Header markers are optional.

The %%value(data marker) marker is used to include the values of a data marker row in the header or footer of a group. The current value of the field whose data marker is placed as a modifier for this marker will be brought into the header or footer, replacing the %%value() marker. Value markers are optional.

Placed below the data marker rows, this marker signifies the beginning of the footer rows for a particular column in a grouping block. All rows between this marker's row and the %%endgroup marker row or the next footer marker will be repeated once for each new value in the grouping field. The row containing the %%footer marker will not be displayed in the output spreadsheet. Footer markers are optional.

Signifies the end of a grouping block. Place this marker in the same column as the %%group marker of the group you are ending. The row containing the %%footer marker will not be displayed in the output spreadsheet. Only one endgroup marker is required, no matter how many group markers are in the first row of the grouping block.

%%group

%%header

%%value(data.#1)

%%=data.#1(hide)

%%=data.#2

%%=data.#3

%%=data.#4

%%=data.#5

%%footer

Subtotal row

%%endgroup

First category

first row

2

4

3

First category

second row

3

4

7

First category

third row

3

5

2

Second category

first row

5

2

9

Second category

second row

4

3

1

Second category

third row

8

5

5

First category

first row

2

4

3

second row

3

4

7

third row

3

5

2

Subtotal row

Second category

first row

5

2

9

second row

4

3

1

third row

8

5

5

Subtotal row

Use the Fieldname modifier to insert database column headings in a spreadsheet. For example, if template cell C5 contains
%%=Products.#2(fieldname)
ExcelWriter will insert the name of column 2 in C5.

If a data marker includes the Uppercase modifier, all text values in the data marker column or cell will be displayed in uppercase. If a data marker includes both the Uppercase and Lowercase modifiers, the last will be applied to cell values.

If a data marker includes the Lowercase modifier, all text values in the data marker column or cell will be displayed in lowercase. If a data marker includes both the Uppercase and Lowercase modifiers, the last will be applied to cell values.

By default, if a data marker's data source name or column name is not bound to a data source in the script, an error will occur. If a data marker contains the Optional modifier, and the data marker is not bound by name to a data source, ExcelWriter will discard the data marker in the generated spreadsheet and will not throw an error.
This modifier makes both a data marker's data source and column optional. For example, for this data marker:
%%=Orders.OrderID(Optional)
If either the data source "Orders," or the column "OrderId" does not exist, the data marker will be removed without error.

The Preserve modifier forces Excel to always insert a string value as a string, instead of attempting to convert the value to a number.

The Convert modifier forces Excel to always try converting a string to a number. If the conversion fails, the value will be inserted as a string.

The Continue modifier is used when the user wishes to bind data from a single data source into multiple worksheets. If the data source for the ExcelTemplate uses a forward-only cursor, such as with a DataReader, the second worksheet with the same data marker will automatically start from the row after the last row in the first worksheet. If the data source is scrollable, however, the second worksheet with the same data marker will start from the first row of the data source. The Continue modifier tells the ExcelTemplate object to start from the row after the last row in the previous worksheet when a scrollable data source is being used.

The Hide modifier removes the field from the spreadsheet after data is bound to the spreadsheet. This is usually used to allow you to remove field values if they are already expressed in a Group header or footer using the %%value() marker. Since the data are removed on the server side, they will not be evaluated in any formulas on the spreadsheet.