Link to home
Start Free TrialLog in
Avatar of futuremoose
futuremoose

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of vandalesm
vandalesm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of futuremoose
futuremoose

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.