Template Spreadsheets |
 |
What is an ExcelWriter Template?
ExcelWriter's ExcelTemplate object generates new files from template spreadsheets. ExcelTemplate inserts values in a template and generates a new workbook with great speed. (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.) Note: The ExcelTemplate object supports Excel's BIFF8 (Excel 97/2000/XP) format only; ExcelTemplate does not support BIFF7 (Excel 95) format templates, and will not generate BIFF7 format files.
An ExcelWriter template
is a file created in Microsoft Excel that contains data markers.
A data marker is a
cell value beginning with 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.
| Template | Spreadsheet generated from template |
|---|---|
 |
 |
Fast and Flexible
The ExcelTemplate object includes two methods - Open and Process - and one property - DataSource. Compared to the large and complex ExcelApplication object, ExcelTemplate's object model is simple and compact, making ExcelTemplate a much faster means of generating a file from an existing spreadsheet than ExcelApplication.Open.
When using templates, you will usually create the ExcelWriter template in Microsoft Excel (although you could also generate a template in script). You can apply any of Excel's features to the template, and these will be preserved when you open the template with the ExcelTemplate object. Note that you can include in a template features that ExcelApplication cannot generate, such as pivot tables and macros.
When you create an ExcelWriter template in Microsoft Excel, only a few simple lines of script will be required to populate the template and generate a new spreadsheet. The look and feel of the spreadsheet can be designed in Excel by anyone with Excel experience, and the ExcelWriter script can be quickly added by a developer.
Modifying at Runtime
At runtime, the ExcelTemplate object can fill in cell values, but cannot modify other aspects of the spreadsheet, such as formatting, formulas, and charts. If you need to modify a spreadsheet at runtime, use the ExcelApplication object. ExcelApplication may be used in combination with ExcelTemplate.
Using ExcelTemplate with ExcelApplication*
*This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriter Free.
You can use the ExcelTemplate object in combination with the ExcelApplication object. Using the two objects together allows you to take advantage of the benefits of both.Examples
- Use the ExcelTemplate
object to quickly load a pre-formatted spreadsheet with data, and then
pass the file (in memory) to the
ExcelApplication object, enabling
runtime modification of the spreadsheet. For an example of
this approach, see
Using ExcelTemplate with ExcelApplication.
- Use the ExcelApplication object to create a template file dynamically (either through pure scripting or through opening an existing spreadsheet). Dynamically assign data markers to cells in the spreadsheet, and save the file on the server. Then open the newly-created template file with the ExcelTemplate object and load it with data.
Copyright © 2003, SoftArtisans, Inc.