Export sqlserver table data to Excel

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000/2005 to Excel
Create an Excel file named testing having the headers same as that of table columns and use these queries
Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\testing.xls;',
    'SELECT * FROM [SheetName$]') select * from SQLServerTable
 ;)

For details, check this link
Click Here

THANKS FOR UR help GUYS...
i got a better solutions in this problem..
i have to show that code.. it may be helpful upcoming developers( who ever needs use these codings...)



' Creating dynamic HTML table using sqlserver data and export to excel format..


Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs)



Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
Response.Charset = ""


Response.ContentType = "application/vnd.xls"
Dim stringWrite As New System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)



table = tabl() 'calling sub function
' Panel1.RenderControl(htmlWrite)
table.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.[End]()

End Sub
Public Function tabl()

Dim con As SqlConnection
con = New SqlConnection("server=mcamdois1;database=MCWEB;uid=sa;password=sa;")
con.Open()
table1.Border = 1
table1.CellPadding = 2
table1.CellSpacing = 0
table1.BorderColor = "cornflowerblue"
table1.ID = "tab1"
table1.Width = "75%"

Dim row As HtmlTableRow
Dim cell As HtmlTableCell
row = New HtmlTableRow()
row.BgColor = "#b9c9fe"
cell = New HtmlTableCell()
cell.InnerHtml = "ISSUE ID"
row.Cells.Add(cell)
cell = New HtmlTableCell()
cell.InnerHtml = "ACTUAL HOURS"
row.Cells.Add(cell)
table1.Rows.Add(row)


cmd = New SqlCommand("SELECT ISSUEID,ACT_HRS FROM MCISSUES WHERE ISSUEID='" & cmbSheets.SelectedItem.Text & " '", con)
sdr = cmd.ExecuteReader()

While sdr.Read()
row = New HtmlTableRow()
row.BgColor = "#e8edff"
For j As Integer = 0 To 1
cell = New HtmlTableCell()
cell.InnerHtml = sdr(j).ToString()
row.Cells.Add(cell)
Next
table1.Rows.Add(row)

End While
Panel1.Controls.Add(table1)
Return table1
End Function




regards
Sasikumar
MCAMDOIS
Mail: ramuksasi@gmail.com