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
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
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.
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.
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.
