Read / Import data from Excel File Using Vb.net WPF

Most of the application developers need to work with excel processing in their application. use the below method to read data from excel file

Private Function ReadDataFromExcel() As DataSet

Dim ds As New DataSet()
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Source file + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""")
Try
cn.Open()
Catch ex As OleDbException
Messagebox.show(ex.ToString())
Catch ex As Exception
Messagebox.show(ex.ToString())
End Try

' It Represents Excel data table Schema.
Dim dt As New System.Data.DataTable()
dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then
For sheet_count As Integer = 0 To dt.Rows.Count - 1
Try
' Create Query to get Data from sheet.
Dim sheetname As String = dt.Rows(sheet_count)("table_name").ToString()
Dim da As New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
da.Fill(ds, sheetname)
Catch ex As DataException
Console.WriteLine(ex.Message)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Next
End If
cn.Close()
Return ds
End Function

—————-
Notes:
cn – connection string
HDR – “HDR=Yes;” indicates that the first row contains columnnames, not data. “HDR=No;” indicates the opposite.
Data Source – File name(excel)

if any one get ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine error’
Please read my previous post..
https://gkskonline.wordpress.com/2014/11/10/the-microsoft-ace-oledb-12-0%E2%80%B2-provider-is-not-registered-on-the-local-machine/
Cheers…..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s