Need a little help...

[Coleen]

To export data from a datagrid to an excel file. I am using VB.net/ASP.net
v 2003, and not connecting to an SQL database. I have an existing datagrid
that I want to get the data from and download it to an excel file. Mrozu
was kind enough to send me some code (thank you) - unfortunately it does not
work - it gives me an error "Cannot create ActiveX component." which he
says is due to not having Excel installed on my machine. I DO have Excel on
my machine - I use it daily! It is Excel 2000, and here is the code I tried
from Mrozu:

Public Sub send_to_excel()
Dim Excel As Object
Dim intRow As Integer = 0
Dim intColumnValue As Integer = 0
Dim strDir As String = ""
Dim strFilename As String = ""
Dim ds As DataSet
Dim dt As DataTable = idt_final_report

Excel = CreateObject("Excel.application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'To display the column value row-by-row in the excel file
For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.cells(intRow + 1, intColumnValue + 1).value.ToString()
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString()
Next

Next

.activeWorkbook().SaveAs(strDir & strFilename)
.activeWorkbook.close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()

End Sub

I must be missing something - is there an imports library class that I need?
I also tried this example from MS:
http://support.microsoft.com/kb/317719/en-us
but it is written to connect to an sql database and uses frames. Can anyone
help me decipher this for my use?

Thanks in advance,

Coleen

[Coleen]

Well, after much Googling and much trial and error I got this to work -
thanks very much to code by Imar Spaanjaars - here is what I did:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim strwriter As New System.IO.StringWriter
Dim htmlwrite As New System.Web.UI.HtmlTextWriter(strwriter)
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.xls"
Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
Dim dg As New DataGrid
dg.DataSource = dt_stat_report_5
dg.DataBind()
dg.RenderControl(htmlwrite)
Response.Write(strwriter.ToString())
Response.End()
end sub

where dt_stat_report_5 is an existing datatable that is declared publicly.

Hope this helps anyone else trying to download a datagrid to an excel file.
Also, you will only be able to save the file if you are using Excel 2000,
but you can open it directly if you have Excel 2003...

Coleen

"Coleen" <co**********@yahoo.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

To export data from a datagrid to an excel file. I am using

VB.net/ASP.net

v 2003, and not connecting to an SQL database. I have an existing

datagrid

that I want to get the data from and download it to an excel file. Mrozu
was kind enough to send me some code (thank you) - unfortunately it does

not

work - it gives me an error "Cannot create ActiveX component." which he
says is due to not having Excel installed on my machine. I DO have Excel

on

my machine - I use it daily! It is Excel 2000, and here is the code I

tried

from Mrozu:

Public Sub send_to_excel()
Dim Excel As Object
Dim intRow As Integer = 0
Dim intColumnValue As Integer = 0
Dim strDir As String = ""
Dim strFilename As String = ""
Dim ds As DataSet
Dim dt As DataTable = idt_final_report

Excel = CreateObject("Excel.application")

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

'To display the column value row-by-row in the excel file
For intRow = 0 To ds.Tables(0).Rows.Count - 1

For intColumnValue = 0 To ds.Tables(0).Columns.Count - 1
.cells(intRow + 1, intColumnValue + 1).value.ToString()
ds.Tables(0).Rows(intRow).ItemArray(intColumnValue ).ToString()
Next

Next

.activeWorkbook().SaveAs(strDir & strFilename)
.activeWorkbook.close()
End With
Excel.Quit()
Excel = Nothing
GC.Collect()

End Sub

I must be missing something - is there an imports library class that I

need?

I also tried this example from MS:
http://support.microsoft.com/kb/317719/en-us
but it is written to connect to an sql database and uses frames. Can

anyone

help me decipher this for my use?

Thanks in advance,

Coleen