Export excel file to sql server with asp.vb.net

[sanju]

hi guyz .. can anyone send sample code to export a excel file into sql
server table using web interface .
ie programatically read and select some columns of excel and insert into a
table.
please give me an idea on this

thank u very much
san
auckland

 

[Steve C. Orr [MVP, MCSD]]

These articles mostly cover the functionality you described:
http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp
http://www.aspnetpro.com/features/2003/07/asp200307so_f/asp200307so_f.asp

 

[verda]

There is much simpler way

'add this to page load and call this page from any button on any .net form

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sFile As String
sFile = Server.MapPath(Request.ApplicationPath) & "\files\MyExcel.xls"
Dim fs As New FileStream(sFile, FileMode.Create, FileAccess.Write)
Dim s As New StreamWriter(fs)
Dim sql As String = "SELECT * FROM dbo.tbl_course_students"
Me.dsAccounts.SelectCommand = Sql 'you need to define ds accounts as a sql table on the form where the page loads
Dim thisconnection As New SqlConnection("add your connection string here")
Dim thisCommand As New SqlCommand _
(sql, _
thisConnection)


' Open Connection
thisconnection.Open()

Dim br As String
br = "<BR>"
Dim td As String
td = "<TD>"
s.WriteLine(td & "my excel spreadsheet" & br)
' Execute Query
s.WriteLine("<center><TABLE>")
s.WriteLine(td & "First name" & td & "Last name" & td & "Email" & td & "Phone" & "<TR>")
Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()
Dim statusx As String

While thisReader.Read()
s.WriteLine("<TD>" & thisReader(1) & "<Td>" & thisReader(2) & "<TD>" & thisReader(3) & td & thisReader(4) & "<tr>")

End While
s.WriteLine("</table>")

s.Close()
Response.Redirect("/portal/files/MyExcel.xls")