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 5 2661
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.
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
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
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?
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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>
|
by: nologin |
last post by:
Is it possible to export data exposed by DataView to Excel file for example
?
Seba
|
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...
|
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
|
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...
| |
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)
|
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?
...
|
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...
|
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...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |