469,616 Members | 1,599 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

Need a little help...

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
Sep 8 '06 #1
1 1131
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


Sep 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Marc | last post: by
7 posts views Thread by Keyed4U | last post: by
17 posts views Thread by EkteGjetost | last post: by
5 posts views Thread by Jintty | last post: by
7 posts views Thread by Galen Somerville | last post: by
12 posts views Thread by nephish | last post: by
reply views Thread by U S Contractors Offering Service A Non-profit | last post: by
5 posts views Thread by bean330 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.