Import Data from Excel File using VB .NET

The following is the sample code how to query an Excel spreadsheet from an ASP.NET page using VB .NET:

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""
da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

dataGrid1.DataSource = ds.Tables(0).DefaultView
dataGrid1.DataBind()
da.Dispose()

Note:

• The code above is just select data from Sheet1 (Worksheet) only.

If you want to select data from first sheet in the Excel workbook, you need to know the name of the first sheet first.
To get the name of the first sheet in the Excel workbook, refer code below:

Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strFirstSheetName As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(0).Name
da0_DB.Close()

Note:

• Microsoft DAO 3.5 Library needs to add to the project when source code above is used. From the Project menu, click References, click Add Reference… and then select the Microsoft DAO 3.5 Library to add.

So, complete source code should be like this:

Dim strConn As String
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim dao_dbE As dao.DBEngine
Dim dao_DB As DAO.Database
Dim strFirstSheetName As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(0).Name
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

da = New OleDbDataAdapter("SELECT * FROM [" & _
strFirstSheetName & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

dataGrid1.DataSource = ds.Tables(0).DefaultView
dataGrid1.DataBind()

da.Dispose()
da0_DB.Close()