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 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.
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
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
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?
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. 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 ...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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.
|
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', {...
| | |