ExcelTemplate in Depth > How to Use Templates

How to Use Templates

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 a data set created from the Orders table in a database, ExcelWriter will import the OrderID column to the spreadsheet's column B. In addition to data markers, your template workbook can include any values, formulas, formatting, charts, pivot tables, etc.

A data marker binds in script to a data source which may be the result of a database query, a variable, or an 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.

Template

Spreadsheet generated from template

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

1. Create an instance of the ExcelTemplate object, for example:

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

2. Call ExcelTemplate.Open to open a template Excel file, for example:

   XlwTemplate.Open Application("templatepath") & _
   	"\DataBinding\StringBindingTemplate.xls"

   The Open method takes the path of the template .xls file to open.

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

   '--- Bind the variables to the template data markers
   '--- %%=$RecipientName and %%=$RecipientCompany
   XlwTemplate.DataSource("RecipientName") = "J. Smith"
   XlwTemplate.DataSource("RecipientCompany") = "SoftArtisans"

4. Call 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 it 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.

Copyright 2006 © SoftArtisans, Inc. All Rights Reserved.