473,714 Members | 2,615 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

.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(DIF2X LS.ClassId, DIF2XLS.Interfa ceId, DIF2XLS.EventsI d)> _
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.Offic e.Interop.Excel .Application
Private wkbk As Microsoft.Offic e.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.Direc tory.Exists(str Path) Then Return
"Path not found."

Dim intMyCounter As Short
Dim intRow As Int32, intX As Int32, intY As Integer

xlApp = New Microsoft.Offic e.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(strConten t, vbCrLf)
Dim arrLine As Array
With wkbk.Worksheets ("Sheet1")
For x = 0 To UBound(arrConte nt)
arrLine = Split(arrConten t(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.Releas eComObject(wkbk )
If Not IsNothing(wkbk) Then wkbk = Nothing

System.Runtime. InteropServices .Marshal.Releas eComObject(xlAp p)
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.CreateOb ject("ExportToE xcel.DIF2XLS")
response.write( dll.Create("C:\ Inetpub\FTPRoot \Reports\",
mid(strFileName ,1,len(strfilen ame)-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 2670
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 misunderstandin g, 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.publi c.platformsdk.c omplus_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.Applicati on
Dim wkbks As Excel.Workbooks
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet

Try

xlApp = New Excel.Applicati on
wkbks = xlApp.Workbooks
wkbk = wkbks.Add
wksht = xlApp.ActiveShe et

'
' Worksheet manipulation
'
wksht.SaveAs(st rPath & strFile)

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

GC.Collect()
GC.WaitForPendi ngFinalizers()
End Try

End Function

Public Function Dispose(ByVal o As Object) As String

Try
System.Runtime. InteropServices .Marshal.Releas eComObject(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).D ataType.ToStrin g

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.A utoFit()

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).D ataType.ToStrin g

also you may try to store the dt.Columns(y).D ataType.ToStrin g 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).D ataType.ToStrin g
=============== =============== =============== =============== =============== =
==============
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
2296
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 9 WMC 2.94 VA Med Ctr 265 VA 86.60 32 VA Res Clinic 10.45 <blank row>
3
8023
by: nologin | last post by:
Is it possible to export data exposed by DataView to Excel file for example ? Seba
4
2518
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 doesnt want to terminate properly even when i set excelApp.Quit, and excelApp = nothing. has anyone else experienced a problem like this??? code below:
4
1687
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
1333
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 invoice I,company C where I.company = C.company" Thanks a lot Dim Excel As New Excel.Application Dim intColumn, intRow, intColumnValue As Integer Dim strExcelFile As String Dim strFileName As String
16
2701
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 System.Collections.Generic.List<frmMain.sDBTest> LoadTestSet(string TestSetFile, System.Collections.Generic.List<frmMain.sDBTestDBviewList)
0
1472
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 defined here does not get applied in Excel. Following is the sample code. Any suggestions on how to keep the text "02345" in the same format in Excel? >>>>>>>>>>>>>>>>>>>> Dim Excel As New Excel.Application Dim wb As Excel.Workbook ...
0
2669
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, but I've one problem. The first problem is that in Excel apostrophes ('), carets (^), quotation marks ("), or backslashes (\) appear in the formula bar but not in the cells, like it does when you import in Excel from Lotus. When I use the command...
1
1381
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: objExcel.Workbooks.Open(Page.MapPath("reports\BLCost.xls")) could be the culprit since the Open method returns a reference to a Workbook object that you haven't assigned a variable to. Since you have no variable to explicitly use to destroy the...
0
8798
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8706
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9171
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9013
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7947
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4719
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3156
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2518
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2108
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.