.Net Com class object for use in ASP to export Excel, Excel won't quit!

Tim Frawley

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

Nov 21 '05 #1

Peter Huang [MSFT]

Hi

Here is a link you may take a look.
Because when you use the syntax as below.
wkbk.Worksheets("Sheet1")
We will have an imply reference to the Worksheet, which is not released.
So I suggest you break the code line into more to ensure every interface is
released.

Office application does not quit after automation from Visual Studio .NET
client
http://support.microsoft.com/default...B;EN-US;317109

Also from your description, it seems that you have your com object running
under COM+.
If I have any misunderstanding, please feel free to post here.
I think you may try to check your config of COM+ to see the com+ Object
unload time, because we all know that COM+ working as a Object Pool will
cache the object in the COM+, so that we will not need to recreate it at
later using.

For detailed information about COM+ config, you may try to post in the
newsgroup below.
microsoft.public.platformsdk.complus_mts

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Nov 21 '05 #2

Tim Frawley

Peter,

Once again you have helped me greatly!

The microsoft article was exactly what I needed. Excel is now released
properly. My code (if anyone is interested) is as follows:

Public Function Create() As String

Dim x As Integer, y As Integer
Dim xlApp As Excel.Application
Dim wkbks As Excel.Workbooks
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet

Try

xlApp = New Excel.Application
wkbks = xlApp.Workbooks
wkbk = wkbks.Add
wksht = xlApp.ActiveSheet

'
' Worksheet manipulation
'
wksht.SaveAs(strPath & strFile)

Catch ex As Exception
Return ex.ToString
Finally
Dispose(wksht)
wkbk.Close()
Dispose(wkbk)
Dispose(wkbks)
xlApp.Quit()
Dispose(xlApp)

GC.Collect()
GC.WaitForPendingFinalizers()
End Try

End Function

Public Function Dispose(ByVal o As Object) As String

Try
System.Runtime.InteropServices.Marshal.ReleaseComO bject(o)
Catch ex As Exception
Finally
o = Nothing
End Try

End Function
I ran the process a dozen or so times. The dllhost service grows by 4k
per hit but after a few minutes it returns to it's original memory
allocation so I am not leaking any memory.

Thank you again Peter!

My next question concerns setting the datatype of an excel column using
..NET but I will post that in the microsoft excel newsgroup.

Sincerely,

Tim Frawley

Nov 21 '05 #3

Tim Frawley

Peter,

Once again I am beating my head against the wall.

Now that I can get the component to clear excel I decided to expand the
capabilities to create a dataset using passed in SQL and a connection
string.

This still works fine, I was able to determine all the objects that are
getting created and dispose them properly, all that is, with one
exception.

Using the dataset I figured I could return the schema from the
datatable to determine datatype and using the Excel Range property set
the datatype of a column, this prevents leading zeros from being lost
on our tag codes, release id codes, etc. by setting the numberformat to
"@" and other uses. Again, this all works fine and I am still
disposing properly. The issue arises in my detection of the datatype
itself.

When I use this code: ( I removed all other code as I commented it in
testing this issue ).

strTV = dt.Columns(y).DataType.ToString

I tried using a datacolumn object set to dt.Columns(y) then testing the
datatype but it still fails to close the Excel instance.

If I comment this one line of code then it works fine, the Excel
instance is closed when it is done.

This line is not producing an error, I tested that too. What is the
deal?

Hopefully you have another good suggestion for me. :)

Tim

Nov 21 '05 #4

Tim Frawley

This is amazingly picky.

I cant even do simple things like:

wksht.Columns.AutoFit()

or

..Range("A1:X1").Font.Bold = True
What is wrong with Excel or .NET?

Nov 21 '05 #5

Peter Huang [MSFT]

Hi

We did not recommend automation Office product at server side(e.g. ASP
page, DCOM....), because Office Product is designed as a desktop product
which is targeted at User Interactive operation.

INFO: Considerations for Server-Side Automation of Office (257757)
http://support.microsoft.com/default...B;EN-US;257757

For your scenario as below, can you post more code about how you achieve
the behavior below.
Because all the variable below seems to have nothing to do with the any
object in Excel application.
So I think you may try to check if there is any Excel Object interact with
the variable below.
Also you may try to isolate the problem one by one.

e.g.
strTV = dt.ToString
..
strTV = dt.Columns(y)
.....

strTV = dt.Columns(y).DataType.ToString

also you may try to store the dt.Columns(y).DataType.ToString date before
your automation Excel.

================================================== ==========================
=============
Using the dataset I figured I could return the schema from the
datatable to determine datatype and using the Excel Range property set
the datatype of a column, this prevents leading zeros from being lost
on our tag codes, release id codes, etc. by setting the numberformat to
"@" and other uses. Again, this all works fine and I am still
disposing properly. The issue arises in my detection of the datatype
itself.

When I use this code: ( I removed all other code as I commented it in
testing this issue ).

strTV = dt.Columns(y).DataType.ToString
================================================== ==========================
==============
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Nov 21 '05 #6