Imports System.Data.SqlClient
Imports System.Data.OleDb
Module modExcel
Public Sub TransferProductsToExcel()
' Transfer data from SQL Server to Excel
Try
Dim data As DataSet = GetProductData()
Dim file As String = "C:\Output.xls"
TransferToExcel(data, file)
MsgBox("Product data has been transferred to Excel.")
Catch ex As Exception
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Public Function GetProductData() As DataSet
Dim ret As New DataSet
Dim cnString As New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)")
Dim sql As String = "SELECT ProductID, ProductName FROM Products"
Dim da As New SqlDataAdapter(sql, cnString)
da.AcceptChangesDuringFill = False ' Mark all rows as new
da.Fill(ret)
Return ret
End Function
Public Sub TransferToExcel(ByVal source As DataSet, ByVal fileName As String)
Dim cn As New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1}Excel 8.0;HDR=Yes{1}", fileName, ControlChars.Quote))
Dim cmd As New OleDbCommand(String.Empty, cn)
Try
' Create the worksheet
If IO.File.Exists(fileName) Then IO.File.Delete(fileName)
cn.Open()
cmd.CommandText = "CREATE TABLE [Products] ([ID] INT, [Name] VARCHAR(255))"
cmd.ExecuteNonQuery()
' Transfer the data to the worksheet
cmd.CommandText = "INSERT INTO [Products] ([ID],[Name]) VALUES (?,?)"
With cmd.Parameters
.Add("@id", OleDbType.Integer, 0, "ProductID")
.Add("@name", OleDbType.VarChar, 255, "ProductName")
End With
Dim da As New OleDbDataAdapter
da.InsertCommand = cmd
da.Update(source)
Finally
cn.Close()
cmd.Dispose()
End Try
End Sub
End Module