Support Knowledge Base, Article 384

ExcelWriter's ExcelTemplate object generates new files from template spreadsheets. ExcelTemplate imports an ADO recordset (or recordsets) to a template and generates a new spreadsheet. Note: The ExcelTemplate object supports Excel's BIFF8 (Excel 97/2000/2002) format only; ExcelTemplate does not support BIFF7 (Excel 95) format templates, and will not generate BIFF7 format files.

The ExcelTemplate object includes two methods - Open and Process - and one property - DataSource. Compared to the large and complicated ExcelApplication object, ExcelTemplate's object model is simple and compact, making ExcelTemplate a faster means of generating a file from an existing spreadsheet than ExcelApplication.Open. However, while the ExcelApplication object is rich in features, and can modify spreadsheets at run time, ExcelTemplate only imports recordsets.

An ExcelWriter template is a file created in Microsoft Excel that contains data markers. A data marker specifies a database column to import to the spreadsheet column containing the marker. The format of a data marker is,

%%=Recordset.ColumnName

For example, if cell B6 contains the data marker %%=Recordset.OrderID, where Recordset represents the Orders table in the Northwind database, ExcelWriter will import the OrderID column to the spreadsheet's column B.

Template
Spreadsheet Generated from Template

To generate a spreadsheet from a template,

1. In Microsoft Excel, create a template spreadsheet. Include data markers indicating database columns to import to the spreadsheet. For example, to insert the Products table's UnitsInStock Column in Excel column C, first row 3, enter %%=Products.UnitsInStock in cell C3. Optionally, include in the template any values, formulas, formatting, charts, pivot tables, etc.



2. In a server-side script,
   
   
   1. Create an instance of the ExcelTemplate object:
      
      Set objTemplate = Server.CreateObject("SoftArtisans.ExcelTemplate")
   
   
   
   2. 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. Use the ExcelTemplate object's Open method to open the template:
      
      objTemplate.Open "c:\templates\template.xls"
   
   
   
   4. Set the template's datasource(s) to the Recordset(s) returned from the database, using any of the following formats.
      
      '--- Binds to data markers %%=ColumnName, %%=#1.ColumnName
      objTemplate.DataSource = Recordset1
      
      '--- Binds to data marker %%=#2.ColumnName
      objTemplate.DataSource = Recordset2
      
      '--- Binds to data marker %%=Recordset.ColumnName
      objTemplate.DataSource("Recordset") = Recordset3
      
      '--- Binds to data marker %%=RecordsetA.ColumnName, loads n rows maximum
      objTemplate.DataSource("RecordsetA", n) = Recordset4
   
   
   
   5. Use the ExcelTemplate object's Process method to generate the spreadsheet:
      
      objTemplate.Process "c:\template_result.xls", saOpenInExcel

      Process takes two parameters: the name and path of the generated spreadsheet, and an optional process method. Assign the process method parameter by name or number:

      saProcessDefault	0
      saProcessOpenInExcel	1
      saProcessOpenInPlace	2
      saProcessOpenForScripting	3