Solved

read an excel spreadsheet from vb.net winforms application

Posted on 2014-02-05
6
1,356 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 27

Accepted Solution

by:
MacroShadow earned 500 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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