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, need to get 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:
   -  Using code above Microsoft DAO 3.5 Library is needed.
   -  From the Project menu, click References, click Add Reference...
      and then select the Microsoft DAO 3.5 Library to add it.

So, complete code should like below:

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()

Good Luck!!!