SoftArtisans OfficeWriter

ExcelWriter allows you to generate a spreadsheet from script alone - using the ExcelApplication object - or from a template spreadsheet and a script, using ExcelTemplate for ASP.NET or ExcelTemplate for ASP. You can also use ExcelApplication and ExcelTemplate together. This section shows you how to generate a template spreadsheet with ExcelApplication and pass the spreadsheet to ExcelTemplate which then populates the template's data markers.

To create a template, use the ExcelApplication object to create a workbook and insert data markers in cells:

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
CheckError Err, "SoftArtisans ExcelWriter could not be created."
	
'--- Create a worksheet and get a reference to it.
Set WrkSht = XlwApp.Worksheets(1)
	
'--- Loop through the selected columns.
For i = 1 To ColumnCount
  '--- Form a data marker to be inserted into the workbook
  '--- Data marker syntax: %%=[DataSrcName].[ColName](<modifier>)
  CurMarker = "%%=" & DataSrcName & "." & Columns(i-1)

  '--- Insert (fieldname) data markers in the first row.  
  '--- Stylize the header row with bold faced font.
  WrkSht.Cells(FirstRow, FirstCol+(i-1)).Value = CurMarker & "(fieldname)"
  WrkSht.Cells(FirstRow, FirstCol+(i-1)).Format.Font.Bold = True
  '--- And push down the data markers one row to make room for the headers
  WrkSht.Cells(FirstRow+1, FirstCol+(i-1)).Value = CurMarker
Next

A data marker will be created dynamically for each database column. A data marker is a cell value beginning with %%= or %%=$ that specifies a variable, an array, or - as in this case - a database column, to insert in the spreadsheet column containing the marker. The data marker may be followed by a modifier. To include column headers in the populated data marker column, the data marker should include the (fieldname) modifier (for example, %%=DataSource.EmployeeID(fieldname)).

To return the workbook created with ExcelApplication as an ExcelTemplate object, pass saOpenAsTemplate to ExcelApplication's Save method.

Set XlwTempl = XlwApp.Save("", saOpenAsTemplate)
CheckError Err, "Error opening the workbook as ExcelTemplate."

Next ExcelTemplate will populate the template data markers with records retrieved from the Employees table in the Northwind database (Northwind.mdb), and stream the generated spreadsheet to the client:

'--- Create and open an ADODB Connection.
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")
CheckError Err, "An error occurred opening the database connection."
	
'--- Get a RecordSet of customer information
Set RecSet = Conn.Execute("SELECT " & ColumnList & " FROM Employees")
CheckError Err, "An error occurred querying the database."
	
'--- Bind the RecordSet to the data markers that were inserted 
'--- with ExcelApplication.
XlwTempl.DataSource(DataSrcName) = RecSet
	
'--- Process and stream the populated workbook to the client.
XlwTempl.Process WorkbookName, saProcessOpenInExcel, True
CheckError Err, "An error occurred processing the template."

Passing ExcelApplication to ExcelTemplate