Hi Boris,
When you reference the excel type library from your code, what happens is a
runtime wrapper is created to enable messaging with the managed .NET code and
the unmanaged excel COM. You have to be exteremely careful if you have to use
Excel as a serverside component, which is not supported nor advised by
Microsoft.
Coming back to your problem, you have to create each object and then
deallocate it. To deallocate, you have to use
Marshal.ReleaseComObject(object). For example when you refer to collections
with;
Dim xlWB As Excel.Workbook = xlApp.Workbooks.Open(sourceFileName)
what you are doing is you are creating an instance of Workbooks but not
setting it to anything. Now it dangles around and there's no way for the
garbage collector to collect it. So the Excel runtime will stay active on the
memory. So instead, create an instance of Workbooks, use then deallocate it.
In anycase, I don't recommend using Excel on server side, unless you have no
other change. I recommend using XSLT to create a transformation for HTML (For
excel versions prior to 2002) or for XML (for excel versions later than
2002). The only disadvantage with this would be, you cannot embed images or
macros.
For the 3rd apps, I will not even mention crystal because it's not meant for
creating excel reports. Now, reporting services is also on table but I don't
know how good it is. But, if your task is only putting a datagrid into an
excel sheet, there's one more option; use Infragistics. Infragistics has an
ExcelConverter component that does exactly this in a very good way. It comes
with Infragistics components, though there's no reference to it through their
web site. (I don't know why.)
Hope this helps,
Ethem Azun
"Boris Condarco" wrote:
Hi gurus,
I'm using excel 2000 to show data that comes from datagrid. The problem is
that for any reason the asp.net application maintains the excel open, even
though, i do close it. Besides, does anyone know any third party compononet
to write excel files without having it installed?
My code looks like:
Public Function toExcel(ByVal fileName As String, ByVal dv As DataView)
As Boolean
Try
Dim sourceFileName As String = Application.StartupPath + "\" +
CTools.getConfigValues("excelTemplate")
If IsNothing(xlApp) Then
xlApp = CreateObject("Excel.Application")
End If
xlApp.Visible = True
Dim xlWB As Excel.Workbook =
xlApp.Workbooks.Open(sourceFileName)
Dim xls As Excel.Worksheet = CType(xlWB.Sheets("Datos"),
Excel.Worksheet)
Dim i As Integer = 4
Dim indexDataView As Integer
dv.Sort = "fechaRequerimiento desc"
For indexDataView = 0 To dv.Count - 1
xls.Range("A" & i.ToString).Value =
dv(indexDataView)("ejecutor")
xls.Range("B" & i.ToString).Value =
dv(indexDataView)("tarea")
xls.Range("C" & i.ToString).Value =
dv(indexDataView)("solicitante")
i += 1
Next
xls.Range("A1").Value = "Detalle Tareas: " +
dv(0)("fechaRequerimiento").ToString + " --- " + dv(dv.Count -
1)("fechaRequerimiento").ToString
xlWB.SaveAs(fileName)
xlApp.Workbooks.Close()
xlApp.Quit()
xls = Nothing
xlWB = Nothing
xlApp = Nothing
GC.Collect()
Return True
Catch ex As Exception
Throw ex
End Try
End Function
Thanx ...
