How can I export my ASP.NET page to Excel?

Question

How can I export the data in my webapp to an Excel sheet from ASP.NET (VB.NET,SQL 2005)?

Answer 1

change the contenttype of your ASP.Net page

Response.ContentType = "application/ms-excel"

Answer 2

SQL Server Reporting services would be the best way to export data from an application into Excel.

If you dont have access to / dont wan't to use reporting services depending on the data you want to extract / format possibly using a CSV structure instead of Excel may be easiest.

Answer 3

Use the Microsoft.Office.Interop.Excel dlls to create excel files with your data and then provide links to download the files using Hunter Daley's download method...

Answer 4

As a general solution, you may want to consider writing handler (ashx) for exporting -- and pass either the query parameters to recreate the query to generate the data or an identifier to get the data from the cache (if cached). Depending on whether CSV is sufficient for your Excel export you could just format the data and send it back, setting the ContentType as @Hunter suggests or use the primary interop assemblies (which would require Excel on the server) to construct a real Excel spreadsheet and serialize it to the response stream.

Answer 5

I prefer to use a OLEDB connection string.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Answer 6

Not sure about exporting a page but if you just want to export a dataset or datatable

        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
        HttpContext.Current.Response.ContentType = "application/ms-excel"

        Dim sw As StringWriter = New StringWriter
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)


        Dim table As Table = New Table

        table.RenderControl(htw)

        '  render the htmlwriter into the response
        HttpContext.Current.Response.Write(sw.ToString)
        HttpContext.Current.Response.End()

Answer 7

One of my most popular blogs is how to generate an Excel document from .NET code, using the Excel XML markup (this is not OpenXML, it's standard Excel XML) - http://www.aaron-powell.com/blog/september-2008/linq-to-xml-to-excel.aspx

I also link off to an easier way to do it with VB 9.

Although this is .NET 3.5 code it could easily be done in .NET 2.0 using XmlDocument and creating the nodes that way.

Then it's just a matter to set the right response headers and streaming back in the response.

Answer 8

SpreadsheetGear for .NET will do it. You can find a bunch of live ASP.NET samples with C# & VB.NET source on this page.

Disclaimer: I own SpreadsheetGear LLC

Answer 9

I use almost the same exact code as CodeKiwi. I would use that if you have a DataTable and want to stream it to the client browser.

If you want a file, you could also do a simple loop through each row/column, create a CSV file and I guess provide a link to the client - you can use a file extension of CSV or XLS. Or if you stream the resulting file to the client it will prompt them if they want to open or save it to disk.

The interops are (well were last time I tried them) great for small datasets, but didn't scale well - horrifically slow for larger datasets.

Answer 10

If you can display your data in a GridView control, it inherently supports "right-click-->Export to Excel" without having to write any code whatsoever.