|
ExcelTemplate in Depth
> Creating Data Markers
Creating Data Markers
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 template
ArrayBindingTemplateASP.xls.
contains several data markers. The following table shows you how to bind the data
markers to array data sources in code.
In this sample, the data markers are bound to data sources in code as follows:
| Data Marker | Code |
|---|
%%=$SimpleArray |
[VBScript]
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 |
[VBScript]
Dim TwoDimArray(1,2)
...
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 |
[VBScript]
Dim TwoDimArray2(0,2)
...
TwoDimArray2(0,0) = "2004"
TwoDimArray2(0,1) = "2005"
TwoDimArray2(0,2) = "2006"
...
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.
|
|---|
Copyright 2006 © SoftArtisans, Inc. All Rights Reserved.
|
