Wednesday, October 24, 2007

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!!!

No comments: