
		Forums Knowledge Base OfficeWriter Online


	This documentation is for OfficeWriter v3.8.1 ASP/COM Platform View Docs for Another Version or Platform

Quick Start > Create a Spreadsheet with ExcelTemplate

Create a Spreadsheet with ExcelTemplate

ExcelWriter's ExcelTemplate object opens an ExcelWriter template file, populates it with data from a specified data source, and generates a new Excel spreadsheet. An ExcelWriter template is a spreadsheet created in Microsoft Excel that contains 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. %%= specifies a database column, and %%=$ specifies a variable or array.

A data marker may include both a data source name and a column name, for example, %%=Products.ProductId where "Products" is a database table, and "ProductId" is a column in the table. When the data source is a simple variable or a one-dimensional array, the data marker should begin with %%=$. For example, %%=$RecipientName where the data source for "RecipientName" is a variable that will be assigned in script.

A template spreadsheet with two string
variable data markers.

In the following example, ExcelTemplate opens a a template spreadsheet, populates the template's data markers, and generates a new spreadsheet. The template contains two data markers: %%=$RecipientName and %%=$RecipientCompany. %%=$ (rather than %%=) indicates that the data source is a variable or an array. In code, ExcelWriter sets the data sources for the data markers to two simple string variables.

Step 1: Create a Template

In Microsoft Excel, create a new file.

In one or more cells, enter data markers. For example, in cell B11 enter the variable data marker %%=$RecipientName and in cell B15 enter the variable data marker %%=$RecipientCompany.

Enter any static cell values in the spreadsheet. Static values and any Excel features included in the template (such as formatting, formulas, charts, and macros) will be included in the generated spreadsheet.

Step 2: Write the ExcelWriter Code

Next, create an ASP script that uses ExcelTemplate to open the template, populate data markers, and generate a new Excel spreadsheet. The following lines of code generate a new spreadsheet from a template that contains two data markers: %%=$RecipientName and %%=$RecipientCompany. We will set 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 using ExcelTemplate:

Add the following METADATA directive to the ASP script to access ExcelWriter constants:
```

```
In ASP, Typelibs provide quick and convenient access to constants associated with a particular object. The script in this example includes ExcelWriter's TypeLib because the script uses the constant saProcessOpenInExcel. The UUID attribute specifies ExcelWriter's unique identifier.

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:

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

Call ExcelTemplate.Process to populate the template's data markers with data source values and generate 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.