Import Data From MS Excel to DataSet without using COM Objects

In my last blog I demonstrated how to export data from a DataSet/DataTable to MS Excel file. In this blog I will show you the process reversed - import data from excel to a Dataset without using the COM objects.

Now let's start with an code example.

Code Example

Public Class DataImporter

Public Shared Function ImportExcelToDataSet(ByVal excelFullFileName As String, ByVal targetTable As String) As DataSet
Dim oledbConnection As OleDbConnection = GetOledbConnection(excelFullFileName)

Dim commandText As String = "select * from [Sheet1$]"

Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(commandText, oledbConnection)

Dim ds As DataSet = New DataSet()

Try
oledbConnection.Open()
dataAdapter.TableMappings.Add("Table", targetTable)
dataAdapter.Fill(ds)

Catch ex As Exception
Throw
Finally
If oledbConnection.State = ConnectionState.Open Then
oledbConnection.Close()
End If

End Try

Return ds

End Function

Private Shared Function GetFileExtension(ByVal fullFileName As String) As String
Return fullFileName.Substring(fullFileName.LastIndexOf(".") + 1)
End Function

Private Shared Function GetOledbConnection(ByVal excelFullFileName As String) As OleDbConnection
Dim excelConnectionString As String
Dim excelExtension As String = GetFileExtension(excelFullFileName)

'' default for excel 97-2003
'excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & excelFullFileName & ";" & _
' "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;"""

'' for excel 2007 and above
'If excelExtension.ToLower.Equals("xlsx") Then
' excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
' "Data Source=" & excelFullFileName & ";" & _
' "Extended Properties=""'Excel 12.0;HDR=YES;IMEX=1;"""
'End If

' both excel 2003 and 2007 works right under this setting
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & excelFullFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

Return New OleDbConnection(excelConnectionString)
End Function

End Class

Important Notes

1. This code example works fine for both Excel 2007 and Excel 97~2003.

2. In order to run this code example, you must install 2007 Office System Driver: Data Connectivity Components ("AccessDatabaseEngine.exe") on your target machine. You can download it from the following url.

3. This code examplse has tested on Windows2003, Windows 7, Excel 2003 and Excel 2007.

References

[@more@]