?
Solved

read an excel spreadsheet from vb.net winforms application

Posted on 2014-02-05
6
Medium Priority
?
1,469 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
6 Comments
 
LVL 28

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 64

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 64

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

839 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