ExcelVB.Net

ASP.Net Excel Sampe

Here is a sample to import Sheet1 of a spreadsheet into a DataSet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
' snipped from http://www.developer.com/net/vb/article.php/3107171
Dim strConn As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & lblFileName.Text & ";" _
    & "Extended Properties=Excel 8.0;"

Dim objConn _
    As New System.Data.OleDb.OleDbConnection(strConn)

Try
    objConn.Open()
Catch ex As Exception
    Response.Write("Error trying to open connection to spreadsheet")
    Response.Write(strConn & " ")
    Response.Write(ex.Message)
    Response.End()
End Try

' Create objects ready to grab data
Dim objCmd As New System.Data.OleDb.OleDbCommand( _
    "SELECT * FROM [Sheet1$]", objConn)
Dim objDA As New System.Data.OleDb.OleDbDataAdapter
objDA.SelectCommand = objCmd

' Fill DataSet
Dim DS As New System.Data.DataSet
Try
    objDA.Fill(DS)

Catch ex As Exception
    Response.Write("Error trying to load spreadsheet spreadsheet")
    Response.Write(objDA.SelectCommand.ToString & " ")
    Response.Write(ex.Message)
    Response.End()
End Try

' Clean up and return DataSet
objConn.Close()