Solved

Using VB.net to read an entire Row at once from Excel

Posted on 2011-03-11
6
1,364 Views
Last Modified: 2013-11-27
I am writing a VB.net 2008 project that needs to retrieve data from an Excel spreadsheet. As an example, let's say I need to retrieve the contents of 30 columns of the first 100 rows.

Using the following code works, but is very slow. I suspect it is because I am having to make 30 calls to retrive one row of data. Is it possible to retrieve an entire row of cell contents at once? Something like specifying a range "1-30" and having the cell contents returned as an array of some sort?

Or any suggestion on a  better, faster way to pull the data from Excel?

    Dim xlApp As Excel.Application = New Excel.Application
    Dim WithEvents xlBook As Excel.Workbook
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim i as integer
        Dim j as integer
        Dim contents as object
        '----------------------------
        ' Connect to the Excel spreadsheet
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\..\..\..\Data\obj_model_110311x.xls")
        xlBook.Application.Visible = True
        xlBook.Windows(1).Visible = True
        xlBook.Application.WindowState = Excel.XlWindowState.xlMinimized
        '----------------------------
        ' Pick the worksheet of interest
        Dim xlSheet As Excel.Worksheet = New Excel.Worksheet
        xlSheet = xlBook.Worksheets(4)
        '----------------------------
        ' Read the contents of the columns 1-30 for the first 100 rows
        for i = 1 to 100
            for j = 1 to 30
                contents = xlSheet.Cells(i,j).value
                '---- do something with the value ----
            next j
        next i
        '-----------------------------
        ' close the Excel app, etc, etc...
        '-----------------------------
    End Sub
0
Comment
Question by:futuremoose
6 Comments
 
LVL 1

Accepted Solution

by:
vandalesm earned 167 total points
ID: 35110393
I suggest you use Jet provider to connect to your Exel workbook. Once you have this you can query your data just like SQL queries.

http://msdn.microsoft.com/en-us/library/ms175866.aspx
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 166 total points
ID: 35111275
you could just call this function to read all data into a datatable and then you can filter it, it will be much faster:


Function ReadDataFromExcel(ByVal path As String) As System.Data.DataTable
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection = Nothing
        Dim sheet As String
        Try

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & path & "; " & _
                  "Extended Properties=Excel 8.0;")
         

            ''get sheet name
            sheet = GetSheetName(path)

            da = New OleDbDataAdapter("SELECT * FROM [" & sheet & "]", conn)

            conn.Open()

            da.Fill(ds)
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        ReadDataFromExcel = ds.Tables(0)
    End Function

Function GetSheetName(ByVal path As String) As String
        Dim dt As New DataTable
        Dim conn As OleDbConnection
        Dim sSheetName As String = ""
        Dim strConn As String
        Try
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & path & ";" & _
                "Extended Properties=""Excel 8.0;"""

            conn = New OleDbConnection(strConn)
            conn.Open()

            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            sSheetName = dt.Rows(0).Item("Table_Name")

        Catch ex As Exception
            Console.Write(ex.Message)
        End Try
        GetSheetName = sSheetName
    End Function

Open in new window

0
 

Author Comment

by:futuremoose
ID: 35124824
YZlat,

Thanks so much for your example code.

However, the example fails at the line
   ReadDataFromExcel = ds.Tables(0)

Error is:
     System.IndexOutOfRangeException was unhandled
     Message="Cannot find table 0."

The Excel file I'm working with had no "tables" defined, so I added one just for testing... still the same error. I defined a table called "test_table" and changed the name in the GetSheetName function.

Does the Excel file in question need to have "tables" defined? And, even if so, not sure why getting the error

Pretty sure the "path" and Excel itself are fine, as I'm already reading from Excel using "Excel.applicatoin"

I can ZIP and upload the exact example, if that would help.

Thanks
0
 
LVL 10

Assisted Solution

by:adriankohws
adriankohws earned 167 total points
ID: 35786601
I have an import routine here that imports any Excel file into a Datagridview, you can then manipulate what you want once it's in a datagridview. I use a OpenFileDialog which allows you to select an Excel File, which for your case, if you already know the filename, just change a bit to get it done.

-----------------------------------------------------------------------------------------------------------------------
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myFileDialog As New OpenFileDialog()

        With myFileDialog
            .Filter = "Excel Files |*.xls"
            .Title = "Open File"
            .ShowDialog()
        End With
        If myFileDialog.FileName.ToString <> "" Then
            Dim ExcelFile As String = myFileDialog.FileName.ToString

            Dim ds As New DataSet
            Dim da As OleDbDataAdapter
            Dim dt As DataTable
            Dim conn As OleDbConnection
            conn = New OleDbConnection( _
                              "provider=Microsoft.Jet.OLEDB.4.0; " & _
                              "data source=" & ExcelFile & "; " & _
                              "Extended Properties=Excel 8.0;")
            Try
                da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn)

                conn.Open()
                da.Fill(ds, "MyData")
                dt = ds.Tables("MyData")
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
            End Try

            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "MyData"
        End If

    End Sub
0
 

Author Closing Comment

by:futuremoose
ID: 36122175
Sorry so long to close out this question... I will try the solutions offered although I still don't know why YZlat's proposal fails.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now