Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Excerpt

data marker is a cell value or sheet tab name beginning

...

with %%=

...

 or %%=$

...

 that specifies a database column, a variable, or an array to insert in the spreadsheet column or sheet tab 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.

...

%%=Products.ProductID

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

%%=#2.FirstName

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

%%=Employee.#3

The third column of the "Employee" data source.

%%=#2.#3

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

Data Marker Names
Anchor
names
names

...

Note

In ExcelWriter 4, a $ was required for all data markers that bind to 1-dimensional arrays. In ExcelWriter 5 and above, if a 1-dimensional array binds to a row of data markers by the ExcelTemplate.SetRowDataSource or ExcelTemplate.BindRowData methods, the $ should be omitted. However, for backward compatibility, if you include the $, the data marker will be populated without error.

...

Fieldname

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.

Uppercase

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.

Lowercase

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.

Optional

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.

Note
  • If a data marker contains a data source number and/or field number (for example %%=#7.#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.
  • If the RemoveExtraDataMarkers property is set to true, all data markers that do not bind to data sources will be removed without error.

Preserve

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

Convert

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.

Continue

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.

Hide

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.

...