You should give EPPLUS a try. I use it in almost all my projects to read excel files.
With only a few lines of code you can read excel files.
Download the EPPLUS.dll and store it in your project folder.
Add it to the reference via Add Reference menu
In you module/class/form write:
Imports OfficeOpenXml
Create a sub to Read File - pass the file name
Private Sub ReadFile(txt_filepath as string)
Try
Using pck As New ExcelPackage()
'Open the Excel file and load it to the ExcelPackage'
Using stream = File.OpenRead(txt_filepath)
pck.Load(stream)
End Using
Dim ws As ExcelWorksheet = pck.Workbook.Worksheets(1)
Dim myDT As DataTable = WorksheetToDataTable(ws, True)
End Using
Catch ex As Exception
MessageBox.Show("Could not read data. Original error: " + ex.Message)
End Try
end sub
'This function reads the file and converts it into a DataTable which you can use or bind to your datagrid directly
Public Function WorksheetToDataTable(ByVal ws As ExcelWorksheet, Optional ByVal hasHeader As Boolean = True) As DataTable
Dim dt As New DataTable(ws.Name)
Dim totalCols As Integer = ws.Dimension.[End].Column
Dim totalRows As Integer = ws.Dimension.[End].Row
Dim startRow As Integer = If(hasHeader, 2, 1)
Dim wsRow As ExcelRange
Dim dr As DataRow
For Each firstRowCell As Object In ws.Cells(1, 1, 1, totalCols)
dt.Columns.Add(If(hasHeader, firstRowCell.Text, String.Format("Column {0}", firstRowCell.Start.Column)))
Next
For rowNum As Integer = startRow To totalRows
wsRow = ws.Cells(rowNum, 1, rowNum, totalCols)
dr = dt.NewRow()
For Each cell As Object In wsRow
dr(cell.Start.Column - 1) = cell.Text
Next
dt.Rows.Add(dr)
Next
Return dt
End Function
There are more samples our there - but this one gets the job done
One downside is that it can only read Excel files formatted as HTML (xlsx). It would not be able to read XLS file without considerable work.
Hope this helps.
Submit an article or tip