Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

read an excel spreadsheet from vb.net winforms application

Posted on 2014-02-05
6
Medium Priority
?
1,432 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 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