Click here to Skip to main content
14,699,886 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,
I am trying to export sqlserver 2005 table values into excel view. I've tried many ways but no success. How do I export the table values to Excel?

regards,

Sasikumar
MCAMDOIS
Posted
Updated 17-Oct-10 5:48am
v2

See here[^].
   
Comments
Dalek Dave 18-Oct-10 7:34am
   
Good Link
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
   
v3
Comments
sweeneel 21-Oct-10 0:33am
   
Thanx buddy
Jipin 21-Oct-10 2:55am
   
Nice one Boka Maharaj
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900