Read / Import data from Excel File Using 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;""")
Catch ex As OleDbException
Catch ex As Exception
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
' 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
Catch ex As Exception
End Try
End If
Return ds
End Function

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


Leave a Reply

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

You are commenting using your 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