Quick Tip: How to work with Excel files in VB.Net (Update)
This is an update for previous post.
Added new function to read entire content of Excel File:
''' <summary>
''' Reads the data from excel file.
''' </summary>
''' <param name="strFilePath">The file path.</param>
''' <returns>DataSet</returns>
Public Shared Function ReadDataFromExcelFile(ByVal strFilePath As String) As DataSet
If strFilePath Is Nothing Then Throw New NullReferenceException("strFilePath")
If Not File.Exists(strFilePath) Then Throw New FileNotFoundException(strFilePath)
' copy source excel file to temp. folder (because the original file can be locked by other process)
Dim tmpFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.InternetCache)
If Not Directory.Exists(tmpFolder) Then tmpFolder = Environment.CurrentDirectory
Dim tmpPath As String = _
Path.Combine(tmpFolder, Path.GetFileNameWithoutExtension(strFilePath) & ".tmp")
File.Copy(strFilePath, tmpPath, True)
strFilePath = tmpPath
' "connect" to excel using jet engine
Dim selectConnection As New OleDbConnection(String.Format( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" & _
"Extended Properties=""Excel 8.0;HDR=YES;""", strFilePath))
Try
selectConnection.Open()
Catch exception1 As Exception
If selectConnection IsNot Nothing Then selectConnection.Dispose()
Throw exception1
End Try
' get db schema from excel file (includes list of excel sheets)
Dim ds As New DataSet
Dim oleDbSchemaTable As DataTable = _
selectConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim enumerator As IEnumerator = Nothing
Try
enumerator = oleDbSchemaTable.Rows.GetEnumerator
Do While enumerator.MoveNext
Dim current As DataRow = DirectCast(enumerator.Current, DataRow)
Dim strTblName As String = current.Item("TABLE_NAME").ToString
Dim adapter As New OleDbDataAdapter( _
String.Format("SELECT * FROM [{0}];", strTblName), selectConnection)
Dim dsTable As New DataSet(strTblName)
Try
adapter.Fill(dsTable)
If dsTable.Tables.Count < 1 Then Continue Do
Dim table2 As DataTable = dsTable.Tables.Item(0)
' format sheet name (replace chars)
table2.TableName = strTblName.Replace("$", String.Empty). _
Replace("#", ".").TrimStart("'").TrimEnd("'").Trim()
ds.Tables.Add(table2.Copy)
Catch exception3 As Exception
If adapter IsNot Nothing Then adapter.Dispose()
If adapter IsNot Nothing Then dsTable.Dispose()
Throw exception3
Finally
If adapter IsNot Nothing Then adapter.Dispose()
If dsTable IsNot Nothing Then dsTable.Dispose()
End Try
Loop
Finally
If enumerator IsNot Nothing Then
If TypeOf enumerator Is IDisposable Then
TryCast(enumerator, IDisposable).Dispose()
End If
End If
End Try
If selectConnection IsNot Nothing Then
selectConnection.Close()
selectConnection.Dispose()
End If
Return ds
End Function
Project sources can be downloaded from here 
.
Enjoy :)
PS
I'm not responsible for any damage that may be caused from using of this post or shared project sources. Blah, blah, blah...
