asked on

ado.net to excel in vb.net (urgent)

I was trying to show the results of a query in excel file. I was trying to bind the search results to excel file but some how i could not do that. In the following code, to the datatable i assigned ds.Tables.Item("QueryResults"), then i was trying to put the contents of datatable in excel file with the following lines:
For Each row In dt.Rows
For n = 1 To dt.Columns.Count
oSheet.Cells(cellnumber, n).value = row.Item(n - 1)
Next
cellnumber += 1
Next

Because of too many rows it is taking long time to write to the excel file. Is there a simple way of directly binding the data in datatable in excel file. Thanks

CODE:

dim sqlconnection as new sqlconnection(strconnstring)
Dim da = New SqlClient.SqlDataAdapter
ds = New DataSet
SqlConnection.Open()
With da
.SelectCommand = New SqlClient.SqlCommand(QueryStmt, SqlConnection)
.Fill(ds, "QueryResults")
End With
SqlConnection.Close()
dt = ds.Tables.Item("QueryResults")
Dim EXL As New Excel.Application
If EXL Is Nothing Then
MsgBox("Could not start excel")
Exit Sub
End If
Dim wSheet As New Excel.Worksheet
Dim oExcel As Object
Dim oBook As Object

'Dim oSheet As Object
Dim oSheet As Excel.Worksheet

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
Dim n As Int32
For n = 1 To dt.Columns.Count
oSheet.Cells(1, n).Value = dt.Columns.Item(n - 1).ColumnName
Next

'oSheet.Range("A2").CopyFromRecordset(dt)
Dim row As DataRow
Dim cellnumber As Integer = 2
If dt.Rows.Count > 0 Then
For Each row In dt.Rows
For n = 1 To dt.Columns.Count
oSheet.Cells(cellnumber, n).value = row.Item(n - 1)
Next
cellnumber += 1
Next

oBook.SaveAs(System.Windows.Forms.Application.StartupPath & "\" & Format(Now, "yyyyMMddHHmmss") & ".xls")

End If

ASKER CERTIFIED SOLUTION

RonaldBiemans

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

raghu11

ASKER

I am dynamically building the excel sheet. I mean i give the option for the users to select the fields from list box and based on the fields select query is executed and a dataset is created. I implemented the following code but some how it creates excel file with column headings but no data in the excel. can someone help me
Thanks

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & System.Windows.Forms.Application.StartupPath & _
"\Book7.xls;Extended Properties=Excel 8.0;"
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim objCmd As New System.Data.OleDb.OleDbCommand
objCmd.Connection = objConn
objCmd.CommandText = "create table test ("

For n = 1 To dt.Columns.Count
If n = dt.Columns.Count Then
objCmd.CommandText = objCmd.CommandText & "[" & dt.Columns.Item(n - 1).ColumnName & "] varchar(255)" & ")"
Else
objCmd.CommandText = objCmd.CommandText & "[" & dt.Columns.Item(n - 1).ColumnName & "] varchar(255)" & ","
End If

Next
objCmd.ExecuteNonQuery()
Dim parametersstring As String
For n = 0 To dt.Columns.Count - 1
If n = dt.Columns.Count - 1 Then
parametersstring = parametersstring & "?"
Else
parametersstring = parametersstring & "?,"
End If
Next

objCmd.CommandText = "Insert into test values(" & parametersstring & ")"
With objCmd.Parameters
For n = 0 To dt.Columns.Count - 1
.Add("@" & dt.Columns.Item(n).ColumnName, OleDb.OleDbType.VarChar, 255, dt.Columns.Item(n).ColumnName)
Next
End With
Dim daole As New System.Data.OleDb.OleDbDataAdapter
daole.InsertCommand = objCmd
daole.Update(dt)
objConn.Close()

raghu11

ASKER

I used daole.update(ds) but it was throwing exception because of which i used datatable dt

raghu11

ASKER

adding this statement helped in solving the issue. da.AcceptChangesDuringFill = False
tHANKS