writting excel files

[Boris Condarco]

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 ...

 

[Guest]

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]

Hi Ethem,

First of all, thanks a lot for helping me to understand what happens when
the dot net works with unmanaged code.

I spent some time searching a component in order to work with excel files
without having installed it. The following site offers a good one.

http://www.aspose.com/

Boris

 

[Don]

Hi gurus,
Besides, does anyone know any third party compononet
to write excel files without having it installed?

Hi Boris,

Our product, Spread for Web Forms, is a server side spreadsheet
component that reads and writes Excel files, among many other things.
Excel need not be present.

You are welcome to get more info on it here and to download an
evaluation version to see if it meets your needs:
http://www.fpoint.com/netproducts/spreadweb/spread.aspx

Here's also a link to a review done on the product by asp.netPRO
magazine:
http://www.aspnetpro.com/Productreviews/2003/07/asp200307mr_p/asp200307mr_p.asp

- Donald
FarPoint Technologies