?
Solved

read an excel spreadsheet from vb.net winforms application

Posted on 2014-02-05
6
Medium Priority
?
1,402 Views
Last Modified: 2014-04-20
What is the best way to connect and read an excel spreadsheet from vb.net winforms application
0
Comment
Question by:TrialUser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 39836926
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39836950
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
 

Author Comment

by:TrialUser
ID: 39837027
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39838036
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39838417
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
 
LVL 4

Expert Comment

by:Mitzs
ID: 39894843
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

Featured Post

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question