Reading an Excel file as a DataTable

This method open a Excel file and retun it as a datatable.
It uses oleDB, wich is very powerful.

If your columns contains headers, they will be read as datacolumn names. This way, you can access datarows with theese names as indexers, ex: value = myRow("myColumnName").



Protected Function GetDatatableFromExcel(ByVal fileName As String, ByVal sheetName As String) As DataTable
Dim conn As System.data.oledb.OleDbConnection
Dim dataResult As New DataTable
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
+ fileName + ";Extended Properties=Excel 8.0;")
conn.Open()

Dim command As New System.Data.OleDb.OleDbCommand(" SELECT * FROM [" + sheetName + "$]")
command.Connection = conn
Dim adaperForExcelBook As New OleDbDataAdapter
adaperForExcelBook.SelectCommand = command
adaperForExcelBook.Fill(dataResult)
conn.Close()

Catch err As Exception
Throw New Exception("Error reading file: " + fileName)
End Try

Return dataResult
End Function

2 comments:

Mirra said...

People should read this.

Anonymous said...

I know you posted this year ago, but I just found it today. Very useful. Thanks!