Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1647
  • Last Modified:

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

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
futuremoose
Asked:
futuremoose
3 Solutions
 
vandalesmCommented:
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
 
YZlatCommented:
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
 
futuremooseAuthor Commented:
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
 
adriankohwsCommented:
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
 
futuremooseAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now