SoftArtisans OfficeWriter

The following example generates an Excel spreadsheet from the template ArrayBindingTemplate.xls. The template contains several data markers that bind to array data sources in the ASP code.

<%
'--- Declarations
Dim SimpleArray(6)	'--- 1-D array
Dim TwoDimArray(2,2)	'--- 2-D array, 3 rows
Dim TwoDimArray2(0,2)	'--- 2-D array, 1 row
Dim XlwTemplate

'--- Populate a one dimensional array with values.
SimpleArray(0) = "Sunday"
SimpleArray(1) = "Monday"
SimpleArray(2) = "Tuesday"
SimpleArray(3) = "Wednesday"
SimpleArray(4) = "Thursday"
SimpleArray(5) = "Friday"
SimpleArray(6) = "Saturday"

'--- Populate a two-dimensional array with values.
TwoDimArray(0,0) = "Nancy Davolio"
TwoDimArray(0,1) = "Sales Manager"
TwoDimArray(0,2) = "Northwind Sales Dept."
TwoDimArray(1,0) = "Andrew Suyama"
TwoDimArray(1,1) = "Analyst"
TwoDimArray(1,2) = "Northwind Finance Dept."
TwoDimArray(2,0) = "Yorba Von Schnitzel"
TwoDimArray(2,1) = "Manager"
TwoDimArray(2,2) = "IS Support"

'--- Create a two-dimensional array with one row.
TwoDimArray2(0,0) = "2005"
TwoDimArray2(0,1) = "2006"
TwoDimArray2(0,2) = "2007"

'--- Create an instance of ExcelTemplate.
Set XlwTemplate = Server.CreateObject("SoftArtisans.ExcelTemplate")

'--- Open the template workbook
XlwTemplate.Open(C:\Templates\ArrayBindingTemplateASP.xls")
CheckError Err, "There was an error opening the template workbook."

'--- Bind SimpleArray to the data marker 
'--- %%=$SimpleArray.
XlwTemplate.DataSource("SimpleArray") = SimpleArray

'--- Bind TwoDimArray to the set of 
'--- %%=$TwoDimArray data markers.
XlwTemplate.DataSource("TwoDimArray") = TwoDimArray

'--- Bind TwoDimArray2 to the set of 
'--- %%=$TwoDimArray2 data markers.
XlwTemplate.DataSource("TwoDimArray2") = TwoDimArray2

'--- Process the template and stream it to the client.
'--- "EmployeeOrders.xls": File name that will appear in the SaveAs dialog
'--- saProcessOpenInExcel: Option to open the workbook in Microsoft Excel
'--- True: Exclude any macros in the template from the generated workbook
XlwTemplate.Process "EmployeeOrders.xls", saProcessOpenInExcel, True
CheckError Err, "An error occurred processing the template."

'--- Close the ExcelTemplate instance
XlwTemplate.Close 
%>

The first data marker in the template is %%=$SimpleArray. %%=$ indicates that the data source is a simple variable, a 1-dimensional array, or a 2-dimensional array. In the ASP code, ExcelTemplate's DataSource property sets the data source for %%=$SimpleArray to a 1-dimensional array:

The DataSource property's parameter - "SimpleArray" - specifies the name of the template data marker that the data source should populate.

There are three "TwoDimArray" data markers in the template; their fields are specified by ordinal (#1, #2, and #3). The SetDataSource property sets the data source for the three data markers to a 2-dimensional array:

The columns of values in the data source bind to the data markers by order. That is, the first column of values will populate %%=$TwoDimArray.#1, the second will populate %%=$TwoDimArray.#2, and the third %%=$TwoDimArray.#3.

The last set of data markers in the template includes %%=$TwoDimArray2.#1, %%=$TwoDimArray2.#2, and %%=$TwoDimArray2.#3. The SetDataSource property sets the data source for the three data markers to a 2-dimensional array::

Data Marker Code

%%=$SimpleArray

Dim SimpleArray(6)
...
SimpleArray(0) = "Sunday"
SimpleArray(1) = "Monday"
SimpleArray(2) = "Tuesday"
SimpleArray(3) = "Wednesday"
SimpleArray(4) = "Thursday"
SimpleArray(5) = "Friday"
SimpleArray(6) = "Saturday"
...
XlwTemplate.DataSource("SimpleArray") = SimpleArray

%%=$TwoDimArray.#1
%%=$TwoDimArray.#2
%%=$TwoDimArray.#3

Dim TwoDimArray(1,2)	'--- 2-D array
...
TwoDimArray(0,0) = "Nancy Davolio"
TwoDimArray(0,1) = "Sales Manager"
TwoDimArray(0,2) = "Northwind Sales Dept."
TwoDimArray(1,0) = "Andrew Suyama"
TwoDimArray(1,1) = "Analyst"
TwoDimArray(1,2) = "Northwind Finance Dept."
...
XlwTemplate.DataSource("TwoDimArray") = TwoDimArray

%%=$TwoDimArray2.#1
%%=$TwoDimArray2.#2
%%=$TwoDimArray2.#3

Dim TwoDimArray2(0,2)	'--- 2-D array, 1 row		
...
TwoDimArray2(0,0) = "2001"
TwoDimArray2(0,1) = "2002"
TwoDimArray2(0,2) = "2003"
...
XlwTemplate.DataSource("TwoDimArray2") = TwoDimArray2

Using an Array as a Data Source