472,373 Members | 1,558 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,373 software developers and data experts.

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

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
5 2503
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ellen Manning | last post by:
I'm trying to export an Excel2K spreadsheet to A2K. Here is a sample of the Excel spreadsheet: LastName FirstName Hours Location HoursPercent Doe John ...
3
by: nologin | last post by:
Is it possible to export data exposed by DataView to Excel file for example ? Seba
4
by: msnnews.msn.com | last post by:
hi there, i've got a form that populates a datagrid, and a button that calls a function to export to an excel file. All is well with the export, no errors are returned, but the Excel instance...
4
by: Tomek | last post by:
Hi, How could I export data from table or query to excel file in VB.NET? Thanks in advance, Tomek
2
by: Agnes | last post by:
I can export the data to an excel(quit slow , for 5k records, It need 20mins) Now, my problem is how can I join another table from another database ? "select I.invno,I.company,C.telno,C.faxno from...
16
by: alexia.bee | last post by:
Hi all, In some weird reason, excel instance won;t die if i remove the comment from 4 lines of setting values into struct. here is a snipcode public...
0
by: vbvr | last post by:
I need to export a numeric field (TAX ID) from vb.net to Excel. The value gets converted to Numeric in Excel and leading zeroes are lost. I tried to use code posted on this site. But the style...
0
by: sandervanee | last post by:
Hello, I am trying to export several Access 2003 select queries to Excel 2003 using an Access macro. I'm using the macro command "TransferSpreadsheet" to export the queries. This going quit well,...
1
by: Scott M. | last post by:
Many methods return objects when they are called. With Excel these objects are placed in memory and must be destroyed via ReleaseComObject as you have done with your NAR method, but the line: ...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
0
DizelArs
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.