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. %%= specifies a database column, and %%=$ specifies a variable or array. 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. 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.

For detailed information on data marker formats, see ExcelTemplate.DataSource.

• Data Marker Modifiers  V4
• Using ExcelTemplate in Script
• Example 1: Generating a Spreadsheet from a Template Using Recordsets
• Example 2: Using ExcelTemplate with ExcelApplication*
• Example 3: Generating a Spreadsheet from a Template using Variables and Arrays
• Example 4: Passing a Spreadsheet from ExcelApplication to ExcelTemplate*
• Example 5: Data Marker Options

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

Top

Data Marker Modifiers  V4

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:

Top

Using ExcelTemplate in Script

To generate a spreadsheet from a template, in a server-side script,

1. Create an instance of the ExcelTemplate object:

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

2. To import database values to the template, create an ADO database connection, connect to and query the database, and create a recordset, e.g.,

   Set adoConnect = Server.CreateObject("ADODB.Connection")
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_ 
   	Server.MapPath(Application("vroot") & "northwind.mdb")
   sqlText = "SELECT top 20 * from Orders"
   adoConnect.Open strConn
   Set Recordset = adoConnect.Execute(sqlText)

3. If desired, create variables and arrays to store other types of data.
   Here we will assign the current date to a variable:
   

   dim rundate
   rundate = DateValue(now)
   

   Note regarding 2-dimensional arrays: ExcelWriter expects data in the order row, column. Therefore all 2-dimensional arrays must be created to take rows for the first dimension and columns for the second dimension. For example, if you want to create an array in VBScript with 4 rows and 2 columns, you should declare it as follows:

   dim myArray(3,1)

4. Use the ExcelTemplate object's Open method to open the template:

   objTemplate.Open "c:\templates\template.xls"

5. Set the template's DataSource(s), to any combination of ADO Recordsets, variables, one-dimensional, or two-dimensional arrays. When assigning the DataSource property, use one of the many formats available. See a complete description of all possible DataSource and DataMarker formats under the DataSource property.
    
   For example:

   objTemplate.DataSource("Products") = Recordset
   objTemplate.DataSource("Rundate") = rundate

   Note: DataSource assignments should take place after all data manipulation is complete. It is best to set the DataSources immediately before calling the Process method.




6. Use the ExcelTemplate object's Process method to generate the spreadsheet:

   objTemplate.Process "c:\template_result.xls", saOpenInExcel

   The Process method takes three parameters:

   FileName	Path and file name of the new spreadsheet
   ProcessMethod	Optional. Specifies whether to save the file to disk, open it in Excel, open it in the browser, or return an ExcelApplication object.	Default value: 0 (saProcessDefault)
   ExcludeMacro	Optional. If set to True, macros in the template will be excluded from the generated spreadsheet. Note: When ExcludeMacro is enabled, buttons associated with macros may produce the error "Data may have been lost." Therefore, when excluding macros, remove all associated buttons from the spreadsheet.	Default value: False

   Assign the ProcessMethod parameter by name or number:

   0	saProcessDefault	Save the file to disk
   1	saProcessOpenInExcel	Open the file in Excel
   2	saProcessOpenInPlace	Open in the browser
   3	saProcessOpenForScripting	Return the file in memory as an ExcelApplication object. Note: This value is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.

Top

Example 1: Generating a Spreadsheet from a Template Using Recordsets

Simpletemplate.asp opens the template simpletemplate.xls (located in doc-samples\templates), imports data from the Northwind database's Orders table, generates a new spreadsheet, and saves it as "c:\simpletemplate_result.xls."

Top

Example 2: Using ExcelTemplate with ExcelApplication*

Openforscripting.asp generates a new spreadsheet as an ExcelTemplate object and modifies it as an ExcelApplication object. Setting the ExcelTemplate.Process method's second parameter to saProcessOpenForScripting* instructs ExcelTemplate to return the spreadsheet in memory as an ExcelApplication object. The spreadsheet can then be modified using ExcelApplication's objects, methods, and properties.

See also, ExcelApp.Open vs. ExcelTemplate.

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

Top

Example 3: Generating a Spreadsheet from a Template using Variables and Arrays

Varbindform.asp posts a user-entered name to varbindprocess.asp. In varbaindprocess.asp, the ExcelTemplate object opens the template varbindtemplate.xls (located in doc-samples\templates), populates a new spreadsheet with the form value, the current date, a one-dimensional and two dimensional array, and streams the new file to the user as "DataBindXLW.xls".

Top

Example 4: Passing a Spreadsheet from ExcelApplication to ExcelTemplate*

AppToTemplate.asp uses the ExcelApplication object to create a template spreadsheet with data markers. ExcelApplication's Save statement includes the parameter saOpenAsTemplate*, so the spreadsheet is passed to ExcelTemplate. ExcelTemplate populates the template spreadsheet.

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

Top

Example 5: Data Marker Options

MarkerOptions.asp demonstrates using data marker modifiers.

Top

Copyright © 2003, SoftArtisans, Inc.