Best way to export to Excel from vb.net

[hyper88]

Hi,

I have a program with a datagrid and a load to excel button.

I have written codes to loop through the datatable and insert the content to excel, but it took 30 secs just to load 700 rows x 5 columns of data.

Are there any faster way to do this? Besides exporting it straight from SQL server.

thanks

[RobDog888]

You could use a ADO.NET connection to a Excel Workbook. Then execute an INSERT INTO sql statment to insert all the data just like you do with a database.

[hyper88]

Hi,

I tried creating an excel table with the following codes but i will get a "You can't modify database, as it is a read-only table"

myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\test\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Try
            Dim conn As New OleDbConnection
            conn.ConnectionString = myConnectionString
            conn.Open()
            Dim cmd1 As New OleDbCommand
            cmd1.Connection = conn
            'cmd1.CommandText = "CREATE TABLE test (sKontraktnummer char(255), sFendertyp char(255), sFendernr char(255), nCykelnummer char(255), dtTime char(255), Id char(255))"
            'cmd1.ExecuteNonQuery()
            conn.Close()
        Catch ex As OleDbException
            MsgBox(ex.Message)
        End Try

Is there something that i am doing wrong?

[hyper88]

ok, i have tried the SELECT INTO sql statement but i am still getting
Specified owner name 'Excel 8.0;Database=C:\test.xls' either does not exist or you do not have permission to use it.

here's the modified code.

' Open a connection to the sample Northwind database.
        Dim conn As New System.Data.OleDb.OleDbConnection( _
              "Provider=sqloledb;Data Source=(local);Initial Catalog=NorthWind;Integrated Security=SSPI;")
 
        conn.Open()
        Dim cmd As New System.Data.OleDb.OleDbCommand()
        cmd.Connection = conn
 
        cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\test.xls].[Products]" & _
                        "FROM [Products]"
        cmd.ExecuteNonQuery()
        conn.Close()
 
    End Sub

The above should create a new worksheet in test.xls and it should contain all the data from the products table in northwind. By the way there is no access restrictions on the excel file.

Anyone? thanks

[RobDog888]

Are you using your Excel code still to connect or automate it at all in your project? That would cause this error.

[hyper88]

thanks for replying,

This are the only codes i am using in the project. I have commented rest of the codes which tries to do the same thing. Anyway the original code is from microsoft here but i also got the same error even following the sample there.

' Open a connection to the sample Northwind database.
        Dim conn As New System.Data.OleDb.OleDbConnection( _
              "Provider=sqloledb;Data Source=(local);Initial Catalog=NorthWind;Integrated Security=SSPI;")
 
        conn.Open()
        Dim cmd As New System.Data.OleDb.OleDbCommand()
        cmd.Connection = conn
 
        cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\test.xls].[Products]" & _
                        "FROM [Products]"
        cmd.ExecuteNonQuery()
        conn.Close()
 
    End Sub

[RobDog888]

Try adding a dollar sign after the sheet name.

.[Products$]

[hyper88]

Still got the same error after adding the $ sign behind the sheet name.

Attached is the error msg.

[hyper88]

up..

Or anyone has any fast way to export a result query to Excel. I have tried looping through datatable but it is pretty slow 30secs for 1000 records and 20 secs after some optimisation. I have also tried copyfromRecordset method but i would prefer to do it in the .net way. Are there any built in functions that does this? thanks