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(Appli cation.Sta rtupPath & "\..\..\..\Data\obj_model_ 110311x.xl s")
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True
xlBook.Application.WindowS tate = Excel.XlWindowState.xlMini mized
'------------------------- ---
' 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
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(Appli
xlBook.Application.Visible
xlBook.Windows(1).Visible = True
xlBook.Application.WindowS
'-------------------------
' 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Thanks so much for your example code.
However, the example fails at the line
ReadDataFromExcel = ds.Tables(0)
Error is:
System.IndexOutOfRangeExce
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