Export DataSet to Excel ASP.Net

Question

I need to Export DataSet to Excel, Exporting it is not the main issue, When I am using the Same Exported Excel File using following Connection String CS = "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" + FilePath + ";Extended Properties=Excel 8.0;"

It gives me an error message saying External Table is not in correct format

Help Needed

Answer 1

You can do this in a better way by changing the Response.ContentType "application/vnd.ms-excel", especially since you are using ASP.NET and not winforms.

check this article with full details on converting dataset to excel

Answer 2

When you do Response.ContentType "application/vnd.ms-excel".
You can just generate an html table.
Excel will pick it op an open it and automatically convert the HTML to excel.

Answer 3

Here's a blog post I wrote that illustrates some of the various options for getting data from SQL into Excel: http://www.officewriter.com/blog/2011/09/23/exporting-sql-server-database-data-excel

If you're using ASP.NET your choices are somewhat more limited because you need something that is both programmatic as well as scalable/suitable for server use, like OfficeWriter.

Answer 4

Hi i found a perfect solution here

Just replace 'missing.value' with System.Type.Missing in the code. Also remove

oWB.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing);

and

oXL.Quit();

from the code. Otherwise your excel will get closed automatically as soon as it open.