SoftArtisans ExcelWriter Guide

Create a Spreadsheet from a Template (ASP)

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.

The term "template" refers to an ExcelWriter template, not a Microsoft Excel template (.xlt file). However, ExcelWriter can open and generate both .xls and .xlt files. Use an .xlt file the same way you would an .xls file.

A template spreadsheet with two string variable
data markers.

In the following sample, 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 string.asp, ExcelWriter sets the data sources for the data markers to two simple string variables.

Example: Using a Variable as a Data Source

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

[View StringBindingTemplate.xls]

Step 1: Create a Template

To generate an Excel spreadsheet with ExcelWriter, first 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 sample above generates a new spreadsheet from the template StringBindingTemplate.xls. This 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:

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:

	'--- 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.