How to export data from SQL Server 2005 to Excel

[Ehtesham Siddiq]

Hi,
My application is in VS2008 and coded in Vb.net.
I have my database in Sql Server 2005.I have a Database table say Table1 in My Database named Test.
Now i want to export the data in Table1 to a MS-Excel(.xls) file on the click of a button.
An excel file should be generated after the user clicks on the button and the generated Excel file should have the data that is present in Table1.
Can anyone provide me with a link or some helpful code to do this.
My code is in Vb.net
Thanks.

[Pgmer]

If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            Exit Sub
        End If

        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = "C:\test.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True

[Ehtesham Siddiq]

i dont have any datagridview in my App.
The user should click on the Button and the Excel should get generated from the Database.

[Ehtesham Siddiq]

Thanks PG for the help.
frnds here is my code that i tried and its working great.

Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
con=Your DB Connection
 Dim strQueryExcel As String = ""
strQueryExcel="Your Query"
 Dim dscmd As New SqlDataAdapter(strQueryExcel, con)
        Dim ds As New DataSet
        dscmd.Fill(ds)

        For ii As Integer = 1 To ds.Tables(0).Rows.Count
            xlWorkSheet.Cells(1, ii) = ds.Tables(0).Columns(ii - 1).ToString()
            On Error GoTo kk
        Next
kk:
        Dim range As Integer = 2
        For i = 0 To ds.Tables(0).Rows.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1

                xlWorkSheet.Cells(range, j + 1) = ds.Tables(0).Rows(i).Item(j).ToString()

            Next
            range = range + 1
        Next
        xlApp.Columns.AutoFit()
        Dim a As String = "C:\AdmittedStudentsData.xlsx"

        xlWorkSheet.SaveAs(a)
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        con.Close()

[Pgmer]

Cool... :) Then plz mark thread as solved.

[Ehtesham Siddiq]

Hi PG,
If u can see my above code i have got an issue which im not able to Track.
The error is in the line Below.

For ii As Integer = 1 To ds.Tables(0).Rows.Count
            xlWorkSheet.Cells(1, ii) = ds.Tables(0).Columns(ii - 1).ToString()
            On Error GoTo kk
Next

the above code sets the Header of the generating excel,there are 22 columns in my Dataset,it set the header of the excel but after setting the header of the 22 columns it again is looking for the 23 column which actually is not present in the Dataset.
I dont want to use On error Goto Statement.
Please guide its urgent
Thanks.

[Pgmer]

Then give as
For ii As Integer = 1 To 22

[Ehtesham Siddiq]

Thanks PG.

[Rox Vi]

Thanks PG for the help.
frnds here is my code that i tried and its working great.

Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
con=Your DB Connection
 Dim strQueryExcel As String = ""
strQueryExcel="Your Query"
 Dim dscmd As New SqlDataAdapter(strQueryExcel, con)
        Dim ds As New DataSet
        dscmd.Fill(ds)

        For ii As Integer = 1 To ds.Tables(0).Rows.Count
            xlWorkSheet.Cells(1, ii) = ds.Tables(0).Columns(ii - 1).ToString()
            On Error GoTo kk
        Next
kk:
        Dim range As Integer = 2
        For i = 0 To ds.Tables(0).Rows.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1

                xlWorkSheet.Cells(range, j + 1) = ds.Tables(0).Rows(i).Item(j).ToString()

            Next
            range = range + 1
        Next
        xlApp.Columns.AutoFit()
        Dim a As String = "C:\AdmittedStudentsData.xlsx"

        xlWorkSheet.SaveAs(a)
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        con.Close()

Hi, I was encountering an error message for line 25. Error Message - Item is not a member of System.Web.UI.WebControls.GridViewRow. Any idea?

[Pgmer]

ROX Vi,
Please open a new thread for ur problem. do not post in some one else post.