read an excel spreadsheet from vb.net winforms application

What is the best way to connect and read an excel spreadsheet from vb.net winforms application
TrialUserAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Add a reference to Microsoft Excel Object Library. To do this, follow these steps:

    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel Object Library, and then click Select.
    3. Click OK in the Add References dialog box to accept your selections.

       Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range

        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

Open in new window


Then use ti at you would Excel.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Hi TrialUser;

You can use a package called Linq to Excel and can be installed into your project using NuGet package manager in Visual Studio. Documentation can be found on GitHub.
0
TrialUserAuthor Commented:
MacroShadow:
So how do I read a spreadsheet into a datatable. For eg. if I hvae a spreadsheet c:\test.xlsx with 2 columns, how do I read this into a datatable in code. Thanks
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Fernando SotoRetiredCommented:
Hi TrialUser;

Just for completeness assume that you have the following Excel worksheet.

Excel WorkSheetFirst add Linq to Excel through NuGet package management.
Then create a class to hold the data coming from the Excel worksheet As shown here for the above Excel worksheet.
Public Class EmployeeInfo
    Public Property ID As Integer
    Public Property HireDate As DateTime
    Public Property FirstName As String
    Public Property LastName As String
    Public Property DeptNo As Integer
End Class

Open in new window

Then in you application you can query the Excel worksheet as follows.
' Create the DataContext
Dim excel = New ExcelQueryFactory("Employees.xlsx")
' Configure the data context to use the Ace engine, Jet engine does not work.
excel.DatabaseEngine = Domain.DatabaseEngine.Ace
' The next four lines are only needed if the headder names in the worksheet differ from the code. In this case these columns names have a space in them and therefore need to be mapped. Note that the column ID does not need to be mapped
excel.AddMapping("HireDate", "Hire Date")
excel.AddMapping("FirstName", "First Name")
excel.AddMapping("LastName", "Last Name")
excel.AddMapping("DeptNo", "Dept No")

' This will query the Excel worksheet to return all rows
' Note that I am querying Sheet1, change this to what ever the sheet name is.
Dim empList As List(Of EmployeeInfo) = (From emp In excel.Worksheet(Of EmployeeInfo)("Sheet1")
                                        Select emp).ToList()
' Because there is not nice function to convert data into a DataTable I am using code I have found on the internet, http://www.chinhdo.com/20090402/convert-list-to-datatable/ and is the code below converted to Visual Basic.
Dim dt As DataTable = ToDataTable(empList)

Open in new window

' Code to convert a List(Of T) to a DataTable
''' <summary>
''' Convert a List{T} to a DataTable.
''' </summary>
Public Function ToDataTable(ByVal items As List(Of T)) As DataTable

    Dim tb As New DataTable(GetType(T).Name)

    Dim props As PropertyInfo() = GetType(T).GetProperties(BindingFlags.Public Or BindingFlags.Instance)

    For Each prop As PropertyInfo In props
        Dim tt As Type = GetCoreType(prop.PropertyType)
        tb.Columns.Add(prop.Name, tt)
    Next

    For Each item As T In items
        Dim values(props.Length) As Object

        For i As Integer = 0 To i < props.Length
            values(i) = props(i).GetValue(item, Nothing)
        Next

        tb.Rows.Add(values)
    Next

    Return tb
End Function

''' <summary>
''' Determine of specified type is nullable
''' </summary>
Public Shared Function IsNullable(ByVal tt As Type) As Boolean
    Return Not tt.IsValueType Or (tt.IsGenericType And tt.GetGenericTypeDefinition() = GetType(Nullable()))
End Function

''' <summary>
''' Return underlying type if type is Nullable otherwise return the type
''' </summary>
Public Shared Function GetCoreType(ByVal tt As Type) As Type

    If (tt IsNot Nothing And IsNullable(tt)) Then
        If (Not tt.IsValueType) Then
            Return tt
        Else
            Return Nullable.GetUnderlyingType(tt)
        End If
    Else
        Return tt
    End If
End Function

Open in new window

0
CodeCruiserCommented:
Another way, with builtin functionality, is to use SQL queries. Example here

http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB

http://blog.lab49.com/archives/196
0
MitzsCommented:
Depending on the version of excel, you can double check here
http://www.connectionstrings.com/excel-2010/


You can try following :

      
  Try
      Dim MyConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
      Dim DtTable As New DataTable
      Dim strShetname As String
      Dim DtSet As System.Data.DataSet
      Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
      MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""")

      Dim oApp As New Excel.Application
      Dim oBooks As Excel.Workbooks = oApp.Workbooks
      Dim oBook As Excel.Workbook = oBooks.Add(PrmPathExcelFile)
      Dim oSheet As Excel.Worksheet = oApp.ActiveSheet
      strShetname = oSheet.Name

      MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & strShetname & "$]", MyConnection)

      MyCommand.TableMappings.Add("Table", "Fusion")

      DtSet = New System.Data.DataSet

      MyCommand.Fill(DtSet)

      MyConnection.Close()

      DtTable = DtSet.Tables(0)
      Dim AddaRow As DataRow = DtTable.NewRow

      DtTable.Rows.InsertAt(AddaRow, 0)


      ImportFromExcel = DtTable
  Catch ex As Exception
      MsgBox(ex.Message)
      MyConnection.Close()
      ImportFromExcel = Nothing
  End Try

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.