There are many ways to export data from SQL Server to Excel but I think the one way to do it by real simple coding is to export the data as an XML file and then opening it in Excel.

First we fill the data in a DataSet and then export that data to an XML file. Although the file is an XML one, I give it an extension of ‘.xls’ so that Excel opens it by default and then it can be saved it as the Excel format in some other sheet.

Dim con as SqlConnection
con = New SqlConnection(“Data Source=SERVER; Initial Catalog=dbTest;uid=sa;pwd=123″)
Dim cmd As SqlCommand = New SqlCommand
con.Open()
cmd.CommandText = “spExportData”
cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = con
Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.Fill(ds)
Dim savefileName As String = Application.StartupPath & “\ExportedFile.xls”
ds.WriteXml(savefileName)
con.Close()
cmd.Dispose()
da.Dispose()

Be Sociable, Share!

•