Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to load excel file data into datagrid.
Download Source Code : 1448_GenerateCaptcha.zip

This code snippet shows you how to open an excel formatted file and then read the contents of the file into Dataset and then to DataGrid.

System.Data.OleDb.OleDbConnection class opens the specified excel file.
System.Data.OleDb.OleDbDataAdapter class fills the Dataset.

  1. Dim MyConnection As System.Data.OleDb.OleDbConnection
  2. Dim myPath As String = "Path of the File"
  3. Try
  4. ''''''' Fetch Data from Excel
  5. Dim DtSet As System.Data.DataSet
  6. Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
  7. MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")
  8. ' Select the data from Sheet of the workbook.
  9. MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)
  10. DtSet = New System.Data.DataSet
  11. MyCommand.Fill(DtSet)
  12. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  13. dgViewSample.DataSource = DtSet
  14. dgViewSample.DataBind()
  15. MyConnection.Close()
  16. Catch ex As Exception
  17. Dim er As String
  18. MyConnection.Close()
End Try

Note:  The excel file whose contents are to be read must be closed else exception would be generated.      

Article Comments
it's working good

Posted on 7/21/2006 10:20:10 AM by arnie

retrieve data from excel sheet excel sheet

Posted on 12/11/2006 6:42:27 AM by viren

The Microsoft Jet database engine cannot open the file 'Path of the File'. It is already opened exclusively by another user, or you need permission to view its data.

Thanks

Angela

Posted on 4/20/2007 4:37:08 PM by Angela

Worked great, but i like to map the dtSet.tables(0) to a dataview first and then map the dataview to the datagrid source so that it grid opens up with the xcel data with having to click the + to open it up

Posted on 4/26/2007 9:39:18 AM by tony

Message "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."

Posted on 1/24/2008 4:43:09 AM by Vamsi

what is DataBind() ?

Posted on 6/16/2008 6:16:46 AM by sumi

It works, but my excel files will have different name for SHEET1. So how can I tell to select from SHEET1, where the actual name for SHEET1 is something else, like, say "Any_Name_Sheet"

It will be so great some one can show how to overcome this issue. Thanks in advance.

Posted on 8/29/2008 4:32:53 PM by Mehdi Anis

plzzzzzzzzzzz help meeeeeeeeeee

it's not working
error in databind. so what is databind?

Posted on 12/24/2009 9:13:11 PM by aniruddh

Hi aniruddh,

Please check your file path and query also, and we can define databind

Data binding is the process that establishes a connection between the application UI and business logic. If the binding has the correct settings and the data provides the proper notifications, then, when the data changes its value, the elements that are bound to the data reflect changes automatically. Data binding can also mean that if an outer representation of the data in an element changes, then the underlying data can be automatically updated to reflect the change. For example, if the user edits the value in a TextBox element, the underlying data value is automatically updated to reflect that change.

Thanks.

Posted on 12/29/2009 5:49:32 AM by zunnair

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create, move and delete directory and validate existence of directory using System.IO namespace in VB.Net.

Disclaimer - Privacy
© 2002-2017 DevASP.net