SoftArtisans ExcelWriter Guide

Creating Template Data Markers

Creating Data Markers in ASP

Data Markers
Data Marker Names
Data Marker Formats
Using Short Data Markers
Data Marker Modifiers

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 %%= 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.

A data marker binds in script to a data source which may be a variable, an array, or an ADO recordset. 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. Note: In ExcelWriter versions before 3.1, data source and field numbers were 0-based. If you upgraded from an earlier version, you may need to modify your code.

The following sample generates an Excel spreadsheet from the template ArrayBindingTemplate.xls. The template contains several data markers that bind to array data sources in the ASP code.

ASP Example: Using an Array as a Data Source

[Run Sample] | [View Source: Form] [View Source: ExcelWriter]

[View Template]

In this sample, the data markers are bound to data sources in code as follows:

Data Marker Code

%%=$SimpleArray
Dim SimpleArray(6) ... SimpleArray(0) = "Sunday" SimpleArray(1) = "Monday" SimpleArray(2) = "Tuesday" SimpleArray(3) = "Wednesday" SimpleArray(4) = "Thursday" SimpleArray(5) = "Friday" SimpleArray(6) = "Saturday" ... XlwTemplate.DataSource("SimpleArray") = SimpleArray

%%=$TwoDimArray.#1
%%=$TwoDimArray.#2
%%=$TwoDimArray.#3
Dim TwoDimArray(1,2) '--- 2-D array ... TwoDimArray(0,0) = "Nancy Davolio" TwoDimArray(0,1) = "Sales Manager" TwoDimArray(0,2) = "Northwind Sales Dept." TwoDimArray(1,0) = "Andrew Suyama" TwoDimArray(1,1) = "Analyst" TwoDimArray(1,2) = "Northwind Finance Dept." ... XlwTemplate.DataSource("TwoDimArray") = TwoDimArray

%%=$TwoDimArray2.#1
%%=$TwoDimArray2.#2
%%=$TwoDimArray2.#3
Dim TwoDimArray2(0,2) '--- 2-D array, 1 row ... TwoDimArray2(0,0) = "2001" TwoDimArray2(0,1) = "2002" TwoDimArray2(0,2) = "2003" ... XlwTemplate.DataSource("TwoDimArray2") = TwoDimArray2

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_

Do not include spaces anywhere in a data marker.

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

%%=DataSource.[Column Name]

For example:

%%=Employees.[Street Address]

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

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890_ @$#

Data Marker Formats

Data Marker Formats

For this data source: Use one of these data marker formats:

Recordset %%=DataSourceName.ColumnName[(modifier)]

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

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

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

%%=ColumnName[(modifier)]

%%=#ColumnNumber[(modifier)]

Variable %%=$DataSourceName[(modifier)]

%%=$#DataSourceNumber[(modifier)]

One-Dimensional Array data marker %%=$DataSourceName[(modifier)]

%%=$#DataSourceNumber[(modifier)]

Two-dimensional Array data marker %%=$DataSourceName.#ColumnNumber[(modifier)]

%%=$#DataSourceNumber.#ColumnNumber[(modifier)]

%%=$#ColumnNumber[(modifier)]

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 $.

Note on $
A data marker that binds to a variable, a 1-d array, or a 2-d array must begin with %%=$ (not %%=), whether the data marker format is short or long.

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.

Form	Example
`%%=#ColumnNumber`	`%%=#7` Seventh column of the first data source defined in the ExcelWriter code.
`%%=$#DataSourceNumber`	`%%=$#7` First column of the seventh data source defined in the ExcelWriter code.
`%%=ColumnName`	`%%=OrderId` "OrderId" column from the first data source defined in the ExcelWriter code.
`%%=$DataSourceName`	`%%=$Orders` First column in the data source "Orders."

Data Marker Modifiers

An ExcelWriter data marker can include modifiers. The format for a data marker with a modifier is %%=datamarker(modifier). If a data marker includes more than one modifier, the modifiers should be separated by commas. Important: Do not include spaces between modifiers.

ExcelWriter supports the following data marker modifiers:

Fieldname	Use the Fieldname modifier to insert database column headings in a spreadsheet. For example, if template cell C5 contains `%%=Recordset.#2(fieldname)`, ExcelWriter will insert the name of recordset column 2 in C5.
Uppercase	If a data marker includes the Uppercase modifier, all text values in the data marker column or cell will be displayed in uppercase. Note: If a data marker includes both the Uppercase and Lowercase modifiers, the last will be applied to cell values.
Lowercase	If a data marker includes the Lowercase modifier, all text values in the data marker column or cell will be displayed in Lowercase. Note: If a data marker includes both the Uppercase and Lowercase modifiers, the last will be applied to cell values.
Optional	By default, if a data marker in the template spreadsheet 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 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. Note: In ASP, If a data marker contains a column number (for example `%%=Orders.#3`) and is not bound to a data source in script, the data marker will be removed without error whether the "Optional" modifier is present or not.

Top