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, 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 assign the string to cell E7. The example uses a specific language code page; to correctly display strings in any language, use code page 65001.

<% 
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
...
HebrewString = xlw.ansitounicode(Request.Form("FirstName"), 1255)
ws.cells("E7").value = 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.

Set xlw = CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)
Set cells = ws.cells
cells.Cell("A1") = "Default content type is '" + xlw.ContentType + "'"
xlw.ContentType = "application/unknown"
cells.Cell("A2") = "New content type is set to '" + xlw.ContentType + "'"
xlw.Save "file.xls", saOpenInPlace
	

Top

Use CreateFont to set a Font object. The Font object represents a font style that you can use when creating a reusable Style object, or a single-cell Format. To define a font, first set the CreateFont object, then assign values to any or all of its properties, for example,

	Set myfont = xlw.CreateFont
	myfont.name = "Arial"
	myfont.size = 10
	myfont.color = rgb(100,100,0)
	

Top

Use CreateStyle to set a Style object. The Style object represents a common style for a group of cells. To define a style, first set a Style object, and assign values to any or all of its properties, for example,

	Set mystyle = xlw.CreateStyle
	mystyle.Font.Name = "Arial"
	mystyle.Font.Bold = True
	mystyle.HorizontalAlignment = haRight
	

Then, assign your style to individual cells, for example,

	ws.cells("A1").style = mystyle
	

If you change a defined Style object, all cells with that style will be updated.

Top

This method is available when using the .NET wrapper SAExcelApplicationDotNet.

GetDataFromDataSet imports an ADO.NET DataSet into a range of cells, fills in field names automatically, and returns a Range object:

Range = cells.GetDataFromDataSet(Worksheet, DataSet, 
	ShowFieldNames (True/False), FirstRow, FirstColumn,
	MaxRows, MaxColumns, FieldList, 
	FieldListInclude (saxlsExclude/saxlsInclude))
	

Note: The FieldList parameter is a comma delimited string specifying DataSet fields to include or exclude. They may be referenced by field name or position, or both. The order in which fields are specified in the FieldList parameter is the order in which they will be returned.

Top

Takes a range's name and returns the range object.

The following returns the number of areas within the "Headings" range.

	NumHeadingsAreas = xlw.NamedRange("Headings").areacount
	

Top

Opens an existing spreadsheet. The Open method allows you to use preset spreadsheet formats. For example, you could create an order form spreadsheet that includes headings for item, quantity, price, billing information, etc. Then, to generate an order, you would open the spreadsheet, assign specific values to the appropriate cells, and save the file with a new file name.

Open takes two parameters:

	Dim objExcelApp As SAExcelApplication
	Set objExcelApp = CreateObject("SoftArtisans.ExcelWriter")
	objExcelApp.Open "c:\XLWFormats\order.xls", True
	...
	'--- Assign values to cells
	...
	objExcelApp.Save "c:\orders\order00275.xls"
	Set objExcelApp = Nothing
 	

ExcelApplication.Open cannot open a file containing data markers (that is, a template).

If you open a BIFF7 (Excel 95) format spreadsheet, you cannot apply BIFF8 features to it, and ExcelWriter will save the spreadsheet in BIFF7 format.

Top

The Save method generates a new spreadsheet and opens or saves the file. Save takes three optional parameters:

Assign the SaveMethod parameter by name or number:

Assign the FileFormat parameter by name or number:

Examples:

	'--- Save the spreadsheet with a path and file name
	xlw.Save "c:\spreadsheets\workbook1.xls"
   	

	'--- Allow the user to either save the file to disk, 
	'--- or open it from its current location
	Response.ContentType = "application/vnd.ms-excel"
	Response.BinaryWrite(xlw.Save)

See also, Writing Results.

Top

When set to True (default value), the generated spreadsheet will contain a horizontal scroll bar. Set ShowHScrollBar to False to hide the horizontal scroll bar.

Example:

	xlw.ShowHScrollBar = False
	

Top

When set to True (default value), the generated spreadsheet will contain a vertical scroll bar. Set ShowVScrollBar to False to hide the vertical scroll bar.

Example:

	xlw.ShowVScrollBar = False
	

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.

	ws.cells("a7").value = "Spreadsheets Created Today: " &_
	    xlw.SpreadsheetsCreatedToday
	

Top

Returns a built-in style. Use Style to assign one of ExcelWriter's built-in styles to a cell. ExcelWriter includes the following styles:

• "Normal"
• "Comma"
• "Comma0"
• "Currency"
• "Currency0"
• "Percent"
• "Time"
• "Date"
• "DateTime"
• "Float"
• "Hyperlink"

Example: Create a hyperlink in cell (7,1), and assign the "Hyperlink" style to the cell.

	Set Cells = ws.Cells
	Cells.AddHyperlink 7,1,2,2,"http://www.yahoo.com"
	Cells(7,1).Value = "Yahoo!"
	Cells(7,1).Style = xlw.style("Hyperlink")
	

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)

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

Example:

Cell E7 contains a Hebrew string represented in Unicode. The following lines convert the string from Unicode to Ansi, and display the converted string in the browser. The Hebrew characters will be displayed correctly. The example uses a specific language code page and charset; to correctly display strings in any language, use code page 65001 and charset UTF-8.

<META HTTP-EQUIV="content-type" CONTENT="text/html;charset=windows-1255">
<% 
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
...
response.write xlw.unicodetoansi(order.cells("E7").value, 1255)

Top

Use Version to determine which version, or edition of ExcelWriter is currently installed. For a description of the different editions of ExcelWriter, see Edition Differences. Version has two possible values:

ExcelWriter (full edition, no restrictions)	0
ExcelWriterLE (limited functionality)	1

	If xlw.Version = 0 Then 
	    ws.Cells("E6").Style.Name = "MyCustomStyle" 
	    'MyCustomStyle is generated by calling CreateStyle
	End If
	

Top

Returns ExcelWriter's edition and version number.

Example:

	ws.cells("a1") = "Spreadsheet generated by " & xlw.versionex
	

Top

Use the Worksheets property to create an instance of the Worksheets object. Worksheets represents a collection of Excel worksheets in a single workbook. The first worksheet is WorkSheets(1), the second, WorkSheets(2), and so on. To create the first worksheet in a workbook, use,

	Set ws = xlw.Worksheets(1)

Top