SoftArtisans ExcelWriter Guide

Using ExcelWriter From A .NET Application

Installing ExcelWriter for .Net
Page Settings
Using ExcelWriter with ADO.NET
Example 1: ExcelWriter in an .aspx Page
Example 2: ExcelWriter in Microsoft Visual C#.NET
Example 3: ExcelWriter in Microsoft VB.NET
Example 4: Using ExcelTemplate in ASP.NET with Microsoft Visual C#.NET
Example 5: Using ExcelTemplate in ASP.NET with Microsoft VB.NET

Installing ExcelWriter for .Net

SoftArtisans ExcelWriter is currently available only as a traditional COM object and not as managed code. It is available for use in the .NET framework by means of "Interop." This has no performance or functionality implications, but it does make deployment a bit more complicated.

When integrating ExcelWriter into your Visual Studio.NET solution, it will be necessary to first register SAExcel.dll on the server which will execute your code. This can be done by running the ExcelWriter auto-installation program on the server, or by registering the dll using regsvr32. You then have the option of installing the SAExcelDotNet.dll wrapper and the associated SAExcelRCW.dll as part of a single application, or for all applications running on the server (global installation).

To integrate ExcelWriter into a single application that is not a Visual Studio.NET solution:

Create a bin folder as a child of the top level application folder (for example, "ExcelWriterApp\bin").
Locate the provided SAExcelDotNet.dll Primary Interop Assembly (PIA) and the associated SAExcelRCW.dll (both usually located in "C:\Program Files\SoftArtisans\ExcelWriter\DotNet").
Copy the two dll files to the bin folder you created.

To make ExcelWriter globally available to .NET applications:

Locate the provided SAExcelDotNet.dll Primary Interop Assembly (PIA) and the associated SAExcelRCW.dll (both usually located in "C:\Program Files\SoftArtisans\ExcelWriter\DotNet").
Copy the two dll files to C:\WINNT\Assembly.
Open and modify the <assemblies> section on your machine.config file to correctly reference the newly added assemblies:
```
<assemblies>
	<add assembly="mscorlib"/>
	<add assembly="SAExcelDotNet, Version=x.x.x.x, 
		Culture=neutral, PublicKeyToken=f593502af6ee46ae"/>
	<add assembly="SAExcelRCW, Version=x.x.x.x, 
		Culture=neutral, PublicKeyToken=f593502af6ee46ae"/>
	...
	<add assembly="*"/>
</assemblies>
```
Note: The version attribute of the add assembly node must correspond exactly to the version of the assembly (dll file) in C:\WINNT\Assembly. If you upgrade the dlls in C:\WINNT\Assembly, update the dll version attributes in machine.config. To get the exact version of a dll file, right-click the dll and select the Properties tab.

To incorporate ExcelWriter into a Visual Studio.NET project, add references to SAExcelDotNet.dll and SAExcelRCW.dll to your project as follows:

Right-click your project and select Add Reference.
In the Add Reference dialog, make sure that the .NET tab is active.
Highlight both SoftArtisans.ExcelWriterDotNet and SoftArtisans.ExcelWriterRCW, and click Select.
Click Ok to close the Add Reference dialog. VS.NET will add these assemblies to the bin folder under your VS.NET project.

Top

Page Settings

AspCompat
In the aspx page referencing ExcelWriter, the AspCompat page directive must be set to True. At the top of the aspx page, include:
```
	<%@Page language="LanguageName" AspCompat=True%>
```
ExcelWriter Namespace
The ExcelWriter objects are in the SoftArtisans.ExcelWriter namespace. The objects can be referenced as fully qualified names, such as SoftArtisans.ExcelWriter.ExcelApplication. To minimize typing and errors, use an Import directive to import the namespace to the aspx page, and reference objects by name alone, without the namespace prefix.

If you are coding directly in the .aspx page, following the Page directive, include:
```
	<%@Import namespace="SoftArtisans.ExcelWriter"%>
```
If you are coding in the "code behind" page (.aspx.vb or .aspx.cs), include an Imports or Using statement at the top of the "code behind" page:
```
	Imports SoftArtisans.ExcelWriter (In VB.NET)
```
```
	Using SoftArtisans.ExcelWriter (In C#)
```

Top

Using ExcelWriter with ADO.NET

ExcelWriter's principal objects are ExcelApplication and ExcelTemplate. ExcelWriter Version 3 included the .NET wrappers SAExcelApplicationClass and SAExcelTemplateClass for the two principle objects. These wrappers do not support the ADO.NET DataSet object, and cannot accept and ADO.NET DataSet as a parameter. Therefore, in ExcelWriter Version 3, to insert a DataSet into a spreadsheet, it is necessary to pass the DataSet to an array and insert the array values by iterating through the spreadsheet rows and columns. For samples that demonstrate passing an ADO.NET recordset to an array, see ExcelWriter ASP.NET Examples.

V4 In addition to SAExcelApplicationClass and SAExcelTemplateClass, ExcelWriter Version 4 includes two new .NET wrappers that provide ADO.NET support: SAExcelApplicationDotNet and SAExcelTemplateDotNet. Using the new wrappers, you can pass an ADO.NET DataSet containing a single DataTable directly to ExcelApplication's GetDataFromDataSet method or ExcelTemplate's DataSource property.

Top

Example 1: ExcelWriter in an .aspx Page

Note: Some ExcelWriter parameters are optional in a COM environment (such as ASP or Visual Basic) and are defined as optional in the Programmer's Reference. In .NET, all parameters must be present.

<%@ Page language="C#" aspcompat=true %>
<script runat=server>

	void Page_Load(object sender, EventArgs args)
	{
		SoftArtisans.ExcelWriter.SAExcelApplicationClass xlw = 
			new SoftArtisans.ExcelWriter.SAExcelApplicationClass();
		SoftArtisans.ExcelWriter.SAWorksheet sheet = xlw.Worksheets[1];
		sheet.Cells[1, 1].Value = 20;
		xlw.Save(@"workbook.xls", 
			SoftArtisans.ExcelWriter.SASaveMethod.saOpenInPlace, 
			SoftArtisans.ExcelWriter.SAFileFormat.saFileFormatExcel2000);
	}

</script>

Top

Example 2: ExcelWriter in Microsoft Visual C#.NET

Note: Some ExcelWriter parameters are optional in a COM environment (such as ASP or Visual Basic) and are defined as optional in the Programmer's Reference. In .NET, all parameters must be present.

using System;
using SoftArtisans.ExcelWriter;

namespace SoftArtisans.Samples
{
	class ExcelWriterSamples
	{
		[STAThread]
		static void Main(string[] args)
		{
			SAExcelApplication xlw = new SAExcelApplication();
			SAWorksheet sheet = xlw.Worksheets[1];
			sheet.Cells[1, 1].Value = 20;
			xlw.Save(@"workbook.xls", SASaveMethod.saOpenInExcel, 
				SAFileFormat.saFileFormatExcel2000);
		}
	}
}

Top

Example 3: ExcelWriter in Microsoft VB.NET

Imports SoftArtisans.ExcelWriter

Namespace SoftArtisans.Samples
    Public Class ExcelWriterSamples
         Shared Sub Main()
			Dim xlw As New SAExcelApplicationClass()
			Dim sheet As SAWorksheet
            sheet = xlw.Worksheets(1)
            sheet.Cells(1, 1).Value = 20
            xlw.Save("workbook.xls", &_
		SASaveMethod.saOpenInExcel, &_
		SAFileFormat.saFileFormatExcel2000)
        End Sub
    End Class
End Namespace

Top

Example 4: Using ExcelTemplate in ASP.NET with Microsoft Visual C#.NET

<%@ Page language="C#" %>
<%@ Import namespace="SoftArtisans.ExcelWriterDotNet" %>
<script runat=server>

	void Page_Load(object sender, EventArgs args)
	{
		SAExcelTemplateClass xlw = new SAExcelTemplateClass();
		xlw.Open (Server.MapPath("Template.xls"));
		object [,] arDoors = { 
			{"Morrison", "Jim"},
			{"Manzarek", "Ray"},
			{"Krieger", "Robby"},
			{"Densmore", "John"} };
		xlw.set_Datasource("Doors", 65536, arDoors);
		xlw.Process("TheDoors.xls", SAProcessMethod.saProcessDefault, 1);
		
		System.Runtime.InteropServices.Marshal.ReleaseComObject(xlw);⁴
	}

</script>

Top

Example 5: Using ExcelTemplate in ASP.NET with Microsoft VB.NET

Imports SoftArtisans.ExcelWriter

Namespace SoftArtisans.Samples
    Public Class ExcelWriterSamples
         Shared Sub Main()
            Dim xlw As New SAExcelTemplateClass()
            xlw.Open("Template.xls")
            Dim arDoors(,) As Object = _
                {{"Morrison", "Jim"}, _
                {"Manzarek", "Ray"}, _
                {"Krieger", "Robby"}, _
                {"Densmore", "John"}}

            xlw.DataSource("Doors") = arDoors
            xlw.Process("TheDoors.xls", SAProcessMethod.saProcessDefault)
            
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlw)⁴
        End Sub
    End Class
End Namespace

Top