Export to Excel from ASP.NET app

[mikefitz]

Hi,
I need to export data from a DataSet Element to Excel. Does anyone know the easiest way to export the data from the DataSet to an Excel file on the client's machine?

Thanks,
Mike

[sedgey]

The simplest way is to export the file as a csv (comma seperated values) with a .xls extension which will open in excel although it's technically not an excel document, the alternative is to use the office interop for excel, only trouble with that is that office will need to be installed on the web server plus license. There are some components which do this kind of export e.g. aspose excel but obviously you would need to pay for it. Obviously in both cases the client would be prompted to save the file to their disk as the documents will be streamed from the webserver, having the application automatically save the document on the clients machine would be a lot more complex.

Heres a good article about the csv method:
http://www.dotnetspider.com/kb/Article963.aspx

[mikefitz]

Does anyone have a code example using VB.NET?

[sedgey]

This does code translation from C# to VB and vice versa:

http://www.carlosag.net/Tools/CodeTranslator/Default.aspx

[Shoobs]

Does anyone have a code example using VB.NET?

I have found a really good example from microsoft at "http://support.microsoft.com/kb/317719". The author has added more than you need... I have used a datagrid not gridview on a new web form (copy their Bottom.aspx form only). I changed the code so you can send the page some SQL which will then be used to query the database and bind with the datagrid.
If you want to give the Excel form a name use this line: [Response.AddHeader("Content-Disposition", "attachment; filename=Exported.xls""")].
Then simply put a Response.Redirect at the end of the code to return you to the page you requested the function from. It appears instantaneously and you dont look like you navigate away from the page at all.

Hope this helps!

[sedgey]

http://support.microsoft.com/kb/317719: good article that

[kapil.goyal]

i m providing a code made necessary change at ur end.

Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=MyExcel.xls")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
dgdfiles.RenderControl(htw)
Response.Write(sw.ToString())
Response.End()

dgdfiles is the datagrid control id.