SoftArtisans OfficeWriter

The following example demonstrates binding data sources to data markers in the template EmployeeOrdersTemplate.xls. The generated spreadsheet is a sales report for a selected employee. The template contains the following data markers: %%=Employee.Name, %%=Employee.Title, %%=Orders.OrderID, %%=Orders.Customer, %%=Orders.OrderDate, and %%=Orders.OrderTotal. The two sets of data markers - %%=Employee.* and %%=Orders.* will be populated by two ADO RecordSets.

For example, the following lines connect to the database and execute SQL statements to return RecSet and EmpRecSet, the RecordSets that will bind to the %%=Orders.* and %%=Employee.* data markers in the template:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")
CheckError Err, "Could not open database connection."
'--- Fill a recordset with orders for the selected employee
SQL = "SELECT Orders.OrderID, Customers.CompanyName As Customer, " & _
	"Orders.OrderDate, " & _
	"([Order Details].UnitPrice * " & _
	"[Order Details].Quantity) As [OrderTotal] " & _
	"FROM Orders, [Order Details], Customers " & _
	"WHERE Orders.OrderID=[Order Details].OrderID AND "	& _
	"Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID=" 
	& EmployeeId
Set RecSet = Conn.Execute(SQL)
CheckError Err, "Could not query database for sales info."
'--- Fill a recordset with information about the selected employee
Set EmpRecSet = Conn.Execute("SELECT FirstName + ' ' + LastName As Name," & _
	" Title  FROM Employees WHERE EmployeeID=" & EmployeeID)
CheckError Err, "Could not query database for employee info."
...

To bind a RecordSet to a template data marker, set ExcelTemplate's SetDataSource property to the RecordSet. Pass the data marker name to SetDataSource as a parameter to let ExcelWriter know which marker the RecordSet should bind to. For example, the following sets the data source for the data markers %%=Orders.OrderID, %%=Orders.Customer, %%=Orders.OrderDate, and %%=Orders.OrderTotal to the Recordset RecSet, and the data source for the data markers %%=Employee.Name and %%=Employee.Title to EmpRecSet:

Set XlwTemplate = Server.CreateObject("SoftArtisans.ExcelTemplate")
XlwTemplate.Open Application("templatepath") & _
	"\DataBinding\EmployeeOrdersTemplate.xls"
CheckError Err, "There was an error opening the template workbook."
'--- Bind the sales data recordset to the template datamarkers %%=Orders.*
XlwTemplate.DataSource("Orders") = RecSet
'--- Bind the employee data recordset to the template datamarkers %%=Employee.*
XlwTemplate.DataSource("Employee") = EmpRecSet

Using a Database as a Data Source