In the ExcelWriter documentation, the term "template" refers to an Excel file containing data markers, not a Microsoft Excel template (.xlt). However, ExcelWriter can open and generate both .xls and .xlt files. Use an .xlt file the same way you would an .xls file.

If a client submits a non-Latin alphabet string to a server running a language different from the language of the client's HTML page, to display the string correctly in an Excel spreadsheet the string must be converted to Unicode.

The AnsiToUnicode method takes a non-Latin alphabet string and its language's code page and returns a Unicode string. If the client HTML script's charset property is set to the string's language or to UTF-8, and the string is converted to Unicode using AnsiToUnicode, the string will be displayed correctly in the generated spreadsheet.

For a complete list of charset and code page values see, Character Set Recognition.

Example:

The following lines get a Hebrew string from an HTML form, convert the string to Unicode, and insert the string at the FirstName data marker. The example uses a specific language code page; to correctly display strings in any language, use code page 65001.

<% 
Set xlw = Server.CreateObject("Softartisans.ExcelTemplate")
...
HebrewString = xlw.ansitounicode(Request.Form("FirstName"), 1255)
xlw.Datasource("FirstName") = HebrewString
...
%>

Top

Sets or returns the generated spreadsheet's MIME content type. In most browsers, Microsoft Excel files are mapped to the MIME type "application/vnd.ms-excel" and ExcelWriter's ContentType is set to this value by default. However, some browsers map Excel files to a different MIME content type, and the browser will not open a file of type "application/vnd.ms-Excel." In this case, set ContentType to the appropriate MIME content type.

In Internet Explorer, to find out what MIME content type maps to Excel files:

1. Open the Start menu and select Run.
2. Entire regedit to open the Registry Editor.
3. Open HKEY_CLASSES_ROOT\MIME\Database\Content Type. This folder contains a list of MIME content type folders. If you select a folder, you will see the extensions to which the type maps in the right frame.

In Netscape Navigator, to find out what MIME content type maps to Excel files:

1. In the browser window, open the Edit menu and select Preferences...
2. In the Category window select Applications.
3. In the Description window, select an application to display its MIME content type.

Example:

Set objSAXLTmplt = Server.CreateObject("SoftArtisans.ExcelTemplate") 
...
Set Recordset = adoConnect.Execute(sqlText)
objSAXLTmplt.Open Server.MapPath(Application("vroot") &_
		 "templates/simpletemplate.xls")
objSAXLTmplt.DataSource("Recordset") = Recordset
objSAXLTmplt.ContentType = "application/unknown"
objSAXLTmplt.Process "TemplateSample.xls", saProcessOpenInPlace

Top

DataSource sets a data source to bind to template data markers. The data source may be an ADO Recordset, a variable, a one dimensional array or a two dimensional array. A data marker is a string beginning with "%%=" (or "%%=$" for variables and arrays), which is placed in a cell of the template spreadsheet. Each data marker specifies a database field, variable, or array column to insert in the spreadsheet column containing the marker. Every data marker in the template spreadsheet must refer to a DataSource which has been assigned in the ExcelWriter script.

You may assign many DataSources to a single spreadsheet.

DataSources should be set after all data manipulation is complete and before calling the Process method.

DataSource takes two optional parameters:

• Name
  The data source name specified in the data marker (e.g. "Orders" in %%=Orders.OrderID).
  The string passed to the Name parameter must begin with a letter.
  Default value: "" (empty string). If you wish to use binding by order,
  you must leave the name parameter empty.
  



• MaxRows
  The maximum number of rows to return from a database or 2-dimensional array.
  Default value: 65536.

Assign the optional parameters Name and MaxRows, as follows.

'--- Setting Name and MaxRows.
'--- Name must begin with a letter. 
'--- This format cannot be used with binding by order
	obj.DataSource("Name", 50) = MyDataSource
	
'--- Setting Name only. 
'--- Name must begin with a letter 
'--- This format cannot be used with binding by order
	obj.DataSource("Name") = MyDataSource

'--- Setting MaxRows only. Use any of the following formats.
	obj.DataSource (, 50)  
	obj.DataSource (50)   
		
'--- If neither parameter is set, default settings will be used.
	obj.DataSource = MyDataSource

DataSources with different formats and different types of data may be used together the same script.
For example:

obj.DataSource = myVariable
obj.DataSource("Orders", 200) = myRecordset
obj.Datasource("twoD") = myArray
obj.Datasource = anotherRecordset	

Note: ExcelWriter expects data in the order row, column. Therefore all 2-dimensional arrays must be created to take rows for the first dimension and columns for the second dimension. For example, if you want to create an array in VBScript with 4 rows and 2 columns, you should declare it as follows:

dim myArray(3,1) 
'--- VBScript arrays are 0-based
'--- Alternatively you can set the lower and upper bound:
'dim myArray(1 to 4, 1 to 2)
	

Top

Opens an ExcelWriter template* spreadsheet, that is, a file created in Microsoft Excel that contains data markers where values will be inserted. A data marker is a cell value beginning with "%%=" that specifies a database column, variable, or array to insert in the spreadsheet column containing the marker (see DataSource).

The Open method takes one parameter, the file path and name of the template .xls file to open.

Example:

	objTemplate.Open "c:\templates\template.xls"

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.

Top

When an ExcelWriter template data marker is populated with numeric strings, ExcelTemplate will automatically convert the strings to numbers. To preserve a numeric string as a string, set PreserveStrings to True. PreserveStrings is set to False by default.

Example:

	objTemplate.PreserveStrings = True
	

Top

Enters database values in the template and generates a new spreadsheet. The Process method takes three parameters:

Assign the ProcessMethod parameter by name or number:

Example:

	objTemplate.Process "file.xls", saProcessOpenInPlace

Top

Returns the number of spreadsheets ExcelWriter created so far, in a 24 hour period. Note that SpreadsheetsCreatedToday contains the number of spreadsheets created by both ExcelApplication and ExcelTemplate.

The hour count begins when the first spreadsheet is created. The count is reset when the first spreadsheet is created after the previous 24 hour period. For example, if you create your first spreadsheet at 10:00 AM on June 2, the 24 hour period will end at 10:00 AM on June 3, and the next period will begin when you create the first spreadsheet after 10:00 AM on June 3.

Top

To get a non-Latin alphabet string from a spreadsheet, and display the string correctly in the browser from a server running a language different from the language of the client's HTML page, you must:

1. Set the ExcelWriter script's charset property to the string language's charset.
2. Use the UnicodeToAnsi method to convert the string to an Ansi encoded string that can be displayed correctly to the user.

UnicodeToAnsi takes a Unicode string and the string language's code page and returns an Ansi string:

AnsiString = UnicodeToAnsi(UnicodeString, CodePage)

To correctly display strings in only one language, uses a specific language code page. To correctly display strings in any language, use code page 65001. For a complete list of charset and code page values see, Character Set Recognition.

Top