I created a .NET Com Class object for use in ASP reports to export
database results directly to Excel. I have it all working just find
but I cannot get the Excel process to go away after the job is done.
I am using the following .NET code in my Com Class object:
<ComClass(DIF2XLS.ClassId, DIF2XLS.InterfaceId, DIF2XLS.EventsId)> _
Public Class DIF2XLS
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String =
"E6D75840-A4BB-4DA0-B094-A40A6B2D2DD9"
Public Const InterfaceId As String =
"2949D69B-71C6-490A-8B20-8D3D8F92A1F1"
Public Const EventsId As String =
"650FA644-8964-4169-872D-AED73054882D"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Private xlApp As Microsoft.Office.Interop.Excel.Application
Private wkbk As Microsoft.Office.Interop.Excel.Workbook
Public Function Create(ByVal strPath As String, ByVal strFile As
String, ByVal strContent As String, ByVal intColCount As Integer) As
String
Dim x As Integer, y As Integer
Try
If strContent = "" Then Return "Nothing to write to file."
If Not System.IO.Directory.Exists(strPath) Then Return
"Path not found."
Dim intMyCounter As Short
Dim intRow As Int32, intX As Int32, intY As Integer
xlApp = New Microsoft.Office.Interop.Excel.Application
wkbk = xlApp.Workbooks.Add
'
' Excel columns held in an array for looping columns.
'
Dim strCols As String, arrCol As Array
strCols =
"A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y ,Z"
If intColCount > 25 Then
arrCol = Split(strCols, ",")
For x = 0 To 25
For y = 0 To 25
strCols &= "," & arrCol(x) & arrCol(y)
Next
Next
End If
arrCol = Split(strCols, ",")
Dim arrContent As Array = Split(strContent, vbCrLf)
Dim arrLine As Array
With wkbk.Worksheets("Sheet1")
For x = 0 To UBound(arrContent)
arrLine = Split(arrContent(x), vbTab)
For y = 0 To UBound(arrLine)
.Cells(x + 1, arrCol(y)) = arrLine(y)
Next
Next
End With
If Mid(strPath, Len(strPath), 1) <> "\" Then strPath &= "\"
wkbk.Close(True, strPath & strFile, False)
xlApp.Quit()
If System.IO.File.Exists(strPath & strFile) Then
Return strPath & strFile
Else
Return "File not found after creation."
End If
Catch ex As Exception
Return ex.ToString
Finally
'
' Clear excel to prevent memory loss/leak
'
System.Runtime.InteropServices.Marshal.ReleaseComO bject(wkbk)
If Not IsNothing(wkbk) Then wkbk = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComO bject(xlApp)
If Not IsNothing(xlApp) Then xlApp = Nothing
GC.Collect()
End Try
End Function
End Class
In the ASP code I create the object and call the function like so:
DIM dll
Set dll = Server.CreateObject("ExportToExcel.DIF2XLS")
response.write(dll.Create("C:\Inetpub\FTPRoot\Repo rts\",
mid(strFileName,1,len(strfilename)-3) & "xls", strFileContent,
intColumnCount))
Set dll = nothing
It creates the file no problem but I am left with a 10MB Excel process
in Task Manager and dllhost is holding 34MB hostage.
Does anyone have any suggestions for cleaning up the excel process that
I have overlooked? I have searched the newsgroups in vain...
Tim Frawley
