How to export a dataset to Excel?

[enigmaos]

I need to export a dataset (datagrid) form a FORM to an EXCEL file. Does anygody have any sample code?

[Gully]

Use ADO and an insert into statement to populate your table

Dim strSQL As String
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\book1.xls].[Sheet1] FROM Customers"
cnSrc.Execute strSQL

Carefull the only some ado type are supported by Excel

If you are using a localised verion of excel you should change [Sheet1] into [Feul1] or whatever

More info at::confused:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;295646
http://support.microsoft.com/default.aspx?scid=kb;EN-US;278973

[Innocent]

If you want more control of Excel, you can make an instance to it by creating a wrapper to Excel (.NET does this automatically). You then have direct access to workbooks, worksheets, cells and more.

The backside of this is that you must bind the application to a minimum version of Excel (i.e. if you use functionality for Excel v/9, you can't use application on Excel v/8).

The advantage is that the application have full control over Excel and it's behavior - you can insert macroes, color cells, insert scripts and so on. You can also at any time detatch the application control by eliminating the object relation - just make shure you leave Excel visible at that time

[Gully]

In an asp environement it has to have Excel running on the server am I wright ...

I heard, it eats up CPU time ...

Have you tried it, I'd like to use it to create list of values on the fly

Adox doesn't allow you to do that.

[enigmaos]

thanks for all the help. :)

I couldn't figure out how to pass a dataset into an excel directly in VB.NET (I have found a lot of code for ASP.NET, but none for VB.NET)

So, I just passed the dataset into an array then pass everything from the array into an excel file.