SoftArtisans ExcelWriter Guide

How To Use Templates

Creating an ExcelWriter Template

An ExcelWriter template is an Excel spreadsheet that contains ExcelWriter data markers. A data marker is a cell value beginning with %%= or %%=$ that specifies a database column, variable, or array to insert in the spreadsheet column containing the marker. A data marker may include modifiers.

You can create an ExcelWriter template in Microsoft Excel, or in script using the ExcelApplication object. Include data markers where you want to insert values. For example, if cell B6 contains the data marker %%=Orders.OrderID, where Orders represents the ADO recordset created from the Orders table in the Northwind database, ExcelWriter will import the OrderID column to the spreadsheet's column B. In addition to data markers, you can include in your template workbook any values, formulas, formatting, charts, pivot tables, etc.

Template Spreadsheet generated from template

A template should not include strings that contain more than one font. These strings will not be preserved in the generated spreadsheet.

A data marker binds in script to a data source which may be a recordset, variable, or array, and 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.

Data Marker Formats
Recordset data marker	`%%=[DataSourceNameOrNumber.]FieldNameOrNumber[(modifier)]`
Variable data marker	`%%=$DataSourceNameOrNumber[(modifier)]`
One-Dimensional Array data marker	`%%=$DataSourceNameOrNumber[(modifier)]`
Two-dimensional Array data marker	`%%=$DataSourceNameOrNumber.#ColumnNumber[(modifier)]`

Top

Data Marker Modifiers *

* This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.

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:

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.NET, 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. 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. In ASP.NET, if `RemoveExtraDataMarkers` is set to `true`, all data markers that do not bind to data sources will be removed.

Top

Using ExcelTemplate in ASP

Example: Using a Variable as a Data Source

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

This ASP example generates a new spreadsheet from the template StringBindingTemplate.xls. The template contains two data markers: %%=$RecipientName and %%=$RecipientCompany. In string.asp, ExcelWriter sets the data sources for the data markers to two simple string variables.

The ExcelTemplate object represents the template Excel file. To generate a new spreadsheet with ExcelWriter:

Create an instance of the ExcelTemplate object, for example:

	Set XlwTemplate = Server.CreateObject("SoftArtisans.ExcelTemplate")

Call ExcelTemplate.Open to open a template Excel file, for example:
```
	XlwTemplate.Open Application("templatepath") & _
		"\DataBinding\StringBindingTemplate.xls"
```
The Open method takes the file path and name of the template .xls file to open.

Use the ExcelTemplate.DataSource property to assign data sources to bind to the template's data markers, for example:

	'--- Get the values submitted from the form.
	'--- These will be the string values that will bind 
	'--- to the ExcelTemplate data markers.
	RecipientName = Request.Form("RecipientName")
	RecipientCo = Request.Form("RecipientCompany")
	...
	'--- Bind the variables to the template data markers
	'--- %%=$RecipientName and %%=$RecipientCompany
	XlwTemplate.DataSource("RecipientName") = RecipientName
	XlwTemplate.DataSource("RecipientCompany") = RecipientCo

These lines from the sample above (string.asp), get the data source strings from form values submitted with the request to ExcelWriter.

Call ExcelTemplate.Process to populate the template's data markers with data source values and generates a new spreadsheet:
```
	XlwTemplate.Process "StringBinding.xls", saProcessOpenInExcel, True
```
The Process method's second parameter specifies whether to save the generated spreadsheet on the server, send to the browser and open it in Excel, send to the browser and open it in the browser window, or return an ExcelApplication object. If the third parameter is set to true (as in the example), macros in the template will be excluded from the generated spreadsheet; to preserve macros, set the parameter to false.

Top