Ok basically what I need to do is the following:
Read FirstExcelDoc (attached)
For every row in that:
Retrieve data from sql server db based on info in the Orange columns of FirstExcelDoc
Let's say I retrieved data elements A, B, C, D, and E from sql server.
Then I need to enter those same data elements A, B, and C in another excel spreadsheet (which has formulas etc)
After inputing A, B, and C in that spreadsheet, read a couple of calculated fields and compare those results to data elements D and E retrieved originally from SQL Server DB.
Finally, output the results in the Blue columns of the FirstExcelDoc spreadsheet for that row.
Loop through and do the same thing for row 2 of the FirstExcelDoc and keep on until you see "END TEST" in the FirstExcelDoc.

I'm looking for a method of doing this efficiently. I have read several articles over the past few days as well as those from Mike R. but just need some direction to get started on this.

To start of I'm looking for whether to read the entire FirstExcelDoc spreadsheet or just read the info for first policy number(2nd row) and process it and write out the blue column info for that row and then loop through and come back and read the second policy number (3rd row) etc... Please attach a code snippet for either way of reading the data.

Thank you so much in advance for your assistance.

Last edited by anandkasotia; 04-20-2010 at 12:11 PM.

Unfortunately, I don't quite understand. Are you looking to use SQL/ADO.NET to read your Excel file or do you wish to use the Excel object model to read the cells and ranges?

Lets us know which way you are looking to go and I'm sure we can help ...

Thank you for welcoming to this forum. I have read so many of your articles about VB.net and Excel in the past couple of days and learnt quite a bit. Now it's time to put it into practice. Please understand that I'm new to Excel automation with VB.net.

I am planning on going down the route of using Excel Object Model.

I was thinking for my FirstExcelDoc (attached in my first post) that I would read the entire contents at one shot and plop it in an array object as from reading your articles, there's a lot of overhead in going cell by cell. Not sure if this is the best route considering that I have to do some heavy processing after reading each row and then ultimately coming back to write the results back to each of those rows (please see my original post for the entire process).

Here's some code I am playing with to read the entire contents of the spreadsheet.

Code:

Private xlApplication As Excel.Application = Nothing
Private xlWorkBooks As Excel.Workbooks = Nothing
Private xlWorkBook As Excel.Workbook = Nothing
Private xlWorkSheet As Excel.Worksheet = Nothing
Private xlRange As Excel.Range = Nothing
Private PolicyFilePath as string = "C:\PL Auto.xls"
If File.Exists(PolicyFilePath) Then
xlApplication = CType(New Excel.Application, Excel.Application)
xlApplication.Visible = True
xlWorkBook = xlApplication.Workbooks.Open(PolicyFilePath)
'xlWorkSheet = CType(xlWorkBook.Worksheets("PolicyInfo"), Excel.Worksheet)
xlWorkSheet = CType(xlWorkBook.Worksheets(Index:=1), Excel.Worksheet)
'Preferred using below MaxRow and MaxCol since even if someone had gone way below
and added some stuff and deleted it later on this will ignore it where as UsedRange picks it up.
Dim MaxRow As Integer = xlWorkSheet.Cells.Find("*", _
xlWorkSheet.Cells(1, 1), _
Excel.XlFindLookIn.xlValues, _
Excel.XlLookAt.xlWhole, _
Excel.XlSearchOrder.xlByRows, _
Excel.XlSearchDirection.xlPrevious).Row
Dim MaxCol As Integer = xlWorkSheet.Cells.Find("*", _
xlWorkSheet.Cells(1, 1), _
Excel.XlFindLookIn.xlValues, _
Excel.XlLookAt.xlWhole, _
Excel.XlSearchOrder.xlByColumns, _
Excel.XlSearchDirection.xlPrevious).Column
Dim rng As Excel.Range
With xlWorkSheet
rng = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol))
End With
Dim rCnt As Integer
Dim cCnt As Integer
Dim DataArray(MaxRow - 1, MaxCol - 1) As Object
For rCnt = 0 To MaxRow - 1
For cCnt = 0 To MaxCol - 1
DataArray(rCnt, cCnt) = rng.Value 'unable to use this array
Next
Next
I was also trying:
Dim rngRowDown As Excel.Range = xlWorkSheet.Range("A1")
rngRowDown = rngRowDown.End(Excel.XlDirection.xlDown)
Dim rngColRight As Excel.Range = xlWorkSheet.Range("A1")
rngColRight = rngColRight.End(Excel.XlDirection.xlToRight)
Dim a As Object = rngColRight.Columns.Address
For rCnt = 1 To rngRowDown.Row
For cCnt = 1 To rngColRight.Column
'Again not sure what to do to retrieve the values of each cell while looping
Next
Next

Above is what I was trying but please advice looking at the bigger picture of what I need to accomplish.

Thanks so much!

Last edited by anandkasotia; 04-20-2010 at 05:26 PM.

Ok i think i got the array part figured out by reading Mike's other article.

Code:

Dim objArray As Object(,)
objArray = CType(rng.Value, Object(,)) ' <-- This is the key!
For r As Integer = 1 To MaxRow
For c As Integer = 1 To MaxCol
Dim cellText As String = CType(objArray(r, c), String)
If cellText = "END TEST" Then
MessageBox.Show(cellText)
End If
Next c
Next r

But I'd still like to know in my second post how can i derive what i've marked in red in both cases especially where i use Xldirection and then how do i loop through to get the values of each cell.

Mike - I'd still like you to shed some light on whether I'm doing it the right away from an efficiency and performance point of view regarding the first step of my task. Please see the entire task below:

Code:

Read FirstExcelDoc (attached)
For every row in that:

Retrieve data from sql server db based on info in the Orange columns of FirstExcelDoc

Let's say I retrieved data elements A, B, C, D, and E from sql server.

Then I need to enter those same data elements A, B, and C in another excel spreadsheet (which has formulas etc)

After inputing A, B, and C in that spreadsheet,

Read a couple of calculated fields and compare those results to data elements D and E retrieved originally from SQL Server DB.

Finally, output the results in the Blue columns of the FirstExcelDoc spreadsheet for that row.

Loop through and do the same thing for row 2 of the FirstExcelDoc
and keep looping until you see "END TEST" in the FirstExcelDoc.

Last edited by anandkasotia; 04-20-2010 at 05:33 PM.

Ok i think i got the array part figured out by reading Mike's other article.

Code:

Dim objArray As Object(,)
objArray = CType(rng.Value, Object(,)) ' <-- This is the key!

Ah, well done, yes, this is exactly correct.

Quote:

But I'd still like to know in my second post how can i derive what i've marked in red in both cases especially where i use Xldirection and then how do i loop through to get the values of each cell.

The goal is to copy all the range values into a two dimensional array and then loop the values within the array. This is much faster than looping the cells within the range individually.

For example, to double the value in each cell of your rang, and using the naming conventions that you are using in your code, the code could look something like this:

Code:

' Find the bottom cell:
Dim rngRowDown As Excel.Range = xlWorksheet.Range("A1")
rngRowDown = rngRowDown.End(Excel.XlDirection.xlDown)
' Find the cell furthest right:
Dim rngColRight As Excel.Range = xlWorksheet.Range("A1")
rngColRight = rngColRight.End(Excel.XlDirection.xlToRight)
' Compose the Range using the bottom and rightmost cells:
Dim rng As Excel.Range = xlWorksheet.Range(rngRowDown, rngColRight)
' Assign the Range values to a 2D array:
Dim objArray As Object(,) = CType(rng.Value, Object(,))
' Iterate all the values in the array, doubling numeric values:
For row As Integer = objArray.GetLowerBound(0) To objArray.GetUpperBound(0)
For column As Integer = objArray.GetLowerBound(1) To objArray.GetUpperBound(1)
' Double the value in the array only if it's a number:
Dim value As Object = objArray(row, column)
If TypeOf value Is Double Then
objArray(Row, Column) = CDbl(value) * 2
End If
Next Column
Next Row
' Assign the values in the 2D array back to the range:
rng.value = objArray

Quote:

Mike - I'd still like you to shed some light on whether I'm doing it the right away from an efficiency and performance point of view regarding the first step of my task. Please see the entire task below...

Thanks so much for your response. I don't understand the GetLowerBound(0) to GetUpperBound(0) on rows but you used 1 for columns?

Also, can you shed some light on the fact that vb.net arrays are 0-based but going to excel it's 1-based. Can you explain that in more detail as far as moving data between 0-based and 1-based?

Thanks so much for your response. I don't understand the GetLowerBound(0) to GetUpperBound(0) on rows but you used 1 for columns?

The Array.GetLowerBound and Array.GetUpperBound methods take an integer indicating which dimension of the array is to checked. The dimension index is zero-based, so use 0 for the first dimension (rows) and 1 for the second dimension (columns).

Quote:

Also, can you shed some light on the fact that vb.net arrays are 0-based but going to excel it's 1-based. Can you explain that in more detail as far as moving data between 0-based and 1-based?

Arrays in .NET are 0 based by default; however a little-known fact is that 2D .NET arrays can be made with an arbitrary base. Using an array base using anything but zero is generally a very bad idea, so always use zero. But keep in mind that Microsoft Excel takes advantage of this capability to have the arrays returned from the Range.Value property be base 1. This is done in order to match how cells are indexed within a range, which is also base 1.

Therefore, you *could* simply assume that arrays returned from Excel are base 1 and that arrays returned from any other source are base 0. If you do this you should be fine... Unless you forget which is which or don't realize that a given array might have originated from Excel several calls previously.

So it is safest to always make use of the GetLowerBound and GetUpperBound methods when working with Excel in order to always be certain of the bounds of the array.

That made perfect sense. Thanks so much for explaining in such great detail. It's much appreciated.

So Mike I should do the following right?

Code:

For r As Integer = objArray.GetLowerBound(0) To objArray.GetUpperBound(0)
For c As Integer = objArray.GetLowerBound(1) To objArray.GetUpperBound(1)
Dim cellText As String = CType(objArray(r, c), String)
If cellText = "END TEST" Then
MessageBox.Show(cellText)
End If
Next c
Next r
instead of:
For r As Integer = 1 To MaxRow
For c As Integer = 1 To MaxCol
Dim cellText As String = CType(objArray(r, c), String)
If cellText = "END TEST" Then
MessageBox.Show(cellText)
End If
Next c
Next r

What about keeping the formatting of each cell in the array? How do we go about storing that information? such as font, font color, size, borders etc...

Quote:

' Assign the values in the 2D array back to the range:
rng.value = objArray

Let's say after having populated the array, I close down my excel and after doing some further processing I'd like to plop the contents of that array back into excel. So I would re-open that same excel spreadsheet but rng (from the above quote) is no longer valid so how do i re-establish that same range.

Thanks!

Last edited by anandkasotia; 04-21-2010 at 11:21 AM.

What about keeping the formatting of each cell in the array? How do we go about storing that information? such as font, font color, size, borders etc...

You can only return an array of Values or Formulas from the Range in one shot. For all other cell properties, such as formatting, you will have to loop through each cell one by one. It's slower, but there is no other way.

Quote:

Let's say after having populated the array, I close down my excel and after doing some further processing I'd like to plop the contents of that array back into excel. So I would re-open that same excel spreadsheet but rng (from the above quote) is no longer valid so how do i re-establish that same range.

I think the easiest way would be to save the range address (passing in the 'External' argument as 'true'), save this string value within your program (or within the workbook), and then later set the range to this string address via the 'Excel.Application.Range' property. Or if you know which worksheet the range is on, then you can just use the local range address and make use of the 'Excel.Worksheet.Range' property.

I think the easiest way would be to save the range address (passing in the 'External' argument as 'true'), save this string value within your program (or within the workbook), and then later set the range to this string address via the 'Excel.Application.Range' property. Or if you know which worksheet the range is on, then you can just use the local range address and make use of the 'Excel.Worksheet.Range' property.

Can you please give me an example/code snippet of how to save of the range which I will be storing in the program for later use to write out to excel?Basically the answer to the bolded text in the quote above.

The idea is simple: save the full-path address to your range as a string. Later, you can use the Excel.Application.Range method to to re-create your range.

For example:

Code:

Dim excelApp As New Excel.Application With {.Visible = True}
Dim workbook As Excel.Workbook = excelApp.Workbooks.Add()
Dim worksheet As Excel.Worksheet = workbook.Worksheets.Add()
Dim range1 As Excel.Range = worksheet.Range("A1")
' Save the range address for 'range1' as a string'
Dim range1Address As String = range1.Address(External:=True)
' Later use that string to re-create your original range:
Dim range2 As Excel.Range = excelApp.Range(range1Address)
' Prove that the newly-created range is for the same exact location as the original:
Dim areEqual As Boolean = range1.Address(External:=True) = range2.Address(External:=True)
MessageBox.Show(areEqual.ToSTring()) ' True
MessageBox.Show("Are you ready to clean up?")
' Cleanup:
GC.Collect()
GC.WaitForPendingFinalizers()
Marshal.FinalReleaseComObject(range1)
Marshal.FinalReleaseComObject(range2)
Marshal.FinalReleaseComObject(worksheet)
workbook.Close(SaveChanges:=False)
Marshal.FinalReleaseComObject(workbook)
excelApp.Quit()
Marshal.FinalReleaseComObject(excelApp)

Thanks Mike for the explanation on saving the range and using it later.

Another question related to finding data in a cell or grab data from a cell:

Let's just say I have headings for columns going across (ex. B1, C1, D1 etc) and headings for rows going down (A2, A3, A4 etc). So let's say I'm after a piece of data to grab or input in cell D4 because it falls in the correct heading of the row and column. But let's say tomorrow someone inserts a new column ahead of D and inserts a new row ahead of 4 and now that piece of data I was after happens to be in E5. My question is without having to hard code the cell address and restricting the user from inserting new columns and rows, how can i dynamically grab the cell I want based on the headings of the columns and headings of the rows.

I would advice that you not allow your users to insert rows and/or add data like this in an ad-hoc manner. If you save your range address as a string value and a user later inserts a row while your program is not running, you will indeed be referencing the wrong location when your program is later run again.

Instead, i would suggest that you make use of column and row headers, and save the data location by those values instead of the absolute range addresses. This way, if a user insert a row, the row header would move with the row's data, so you would always be able to retrieve the correct data point.

That's exactly my question to you. The saving of range and using it later was on another spreadsheet. The following procedure is happening on a totally different spreadsheet. Here's what I'm trying to do: Please see attachment and follow below

Let's just say I have headings for columns going across (ex. Heading is Student 1 is in cell B1, Student 2 is in cell C1, Student 3 is in cell D1, Student 4 is in cell E1, etc) and headings for rows going down (Name is in cell A2, Address is in cell A3, Phone is in cell A4, Email is in cell A5, etc). So let's say I'm after Student 3's Email which happens to be in cell D5. But let's say tomorrow someone inserts a new column ahead of D and inserts a new row ahead of 5 and now that piece of data I was after happens to be in E6. My question is without having to hard code the cell address(D5) and restricting the user from inserting new columns and rows, how can i dynamically grab the cell I want based on the headings of the columns and headings of the rows
So in my example, I would first store the entire range of data in the array in one shot and upon iterating through the array, how do I figure out that Student 3 column name and Email Row name is equivalent to CELL D5? But maybe the next time it could be cell E6 but I should be able to figure that out based on the user-defined column and row headings.

Please let me know if that didn't make sense.

So my question is how do I and what's the easiest way to associate a user-defined column and row headings with Excel's column and row headings (such as A, B, C, D for columns AND 1, 2, 3, 4 for rows)
Thanks.

All you need to do now is, instead of saving the cell address for the data, save the row and column heading. This way, the next time you need to check the spreadsheet you can find your cell via the row and column headings -- which will move with your data.

If you need to figure out which row your cell is in, check the 'Range.Row' property. To know which column your cell is in, use the 'Range.Column' property. Remember, however, that this is Excel, so the row and column numbers are base one. That is, for cell "A1" the Row is 1 and the Column is 1, not zero (0).

I'm still not 100% sure of how to do this. Let's just look at my attachment.

1. Should I be storing the entire range in an array or just iterate through the range without storing in an array?

2. If I do store the range in the array and I iterate through it, I'm not sure where you store off the row and column headings? I'm populating the spreadsheet from a vb class. And let's say, I want to populate all the Student 1 data first and then move on to Student 2 , 3, 4 etc. So my first thing to find out is which Column is Student 1 in then based on each row heading populate the correct information in the appropriate row such as name, address, phone and email for student 1 and then move on to Student 2. I'm just not sure based on looping through the array how i would store off the row headings or do I need to store off? In what context would Range.Row and Range.Column come in use?

Can you provide a snippet of code so it makes sense as far as how i would go about populating this spreadsheet from the information above?

Here is my code:

Code:

If File.Exists(PolicyFilePath) Then
xlApplication = CType(New Excel.Application, Excel.Application)
xlApplication.Visible = True
xlWorkBook = xlApplication.Workbooks.Open(PolicyFilePath)
xlWorkSheet = CType(xlWorkBook.Worksheets(Index:=1), Excel.Worksheet)
Dim MaxRow As Integer = xlWorkSheet.Cells.Find("*", xlWorkSheet.Cells(1, 1), Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious).Row
Dim MaxCol As Integer = xlWorkSheet.Cells.Find("*", xlWorkSheet.Cells(1, 1), Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious).Column
Dim rng As Excel.Range
With xlWorkSheet
rng = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol))
End With
Dim objArray As Object(,)
objArray = CType(rng.Value, Object(,))
For r As Integer = objArray.GetLowerBound(0) To objArray.GetUpperBound(0)
For c As Integer = objArray.GetLowerBound(1) To objArray.GetUpperBound(1)
'Dim ObjDate As Object = objArray(r, c)
Next c
Next r

It looks like you have the approach for iterating the values of the array just fine. From here I'm not sure what you want to do. If you wish to save these values along with the data location, then you might want to have your array include the values in the first row and the first column so that you can access these values.

E.g., to save the phone number for the first student, "Student 1" is in the second column and "Phone" is in the fourth row. But don't save the values 2 and 4, instead save the values "Student 1" and "Phone" so that the next time you access this workbook, you can still find the correct data location even if another user has inserted new rows or columns of data in the mean time.

Thanks for replying to all of my questions on this thread. I really do appreciate your time. However, on this one question, we've gone back and forth and I'm still not able to comprehend how to do this.

You may or may not remember my example of Column Headings in the 1st row and row headings going down in the 1st Column. We discussed and decided to plop the entire range in the array. My question to you was if I want data from Student 4 (Column Heading in column E) and Phone (Row Heading in row 5), how do i access that data in E5. I understand that it's always better to go my row and column headings than to go by E5.

But my question is when I iterate through my array and let's say the outer loop is row and the inner loop is columns, I would know all the column headings since my row would be at 1 AND columns would keep changing until there are no more columns for Row 1 and then the Row would change to 2 and so on and so forth but let's say my data that I'm after happens to be in Row 78 and I'm iterating through row 78 how do I know what column number means what column heading?

If I were to switch the inner and outer loops, I would still have the same issue.

I'm not sure that I understand the problem. If your array includes the column and row headers, then row 1 (remember, array's returned from Excel are base 1) would hold your column headers, and column 1 would hold your row ids.

So in the case of row 78, the data would be held in in the columns of row 78, starting with column 2 (because column 1 holds your row ids) while the column headers would be held within the first row, again, beginning with column 2.

So, your data point at, say, myArray(78,2), that is, row 78, column 2, would have it's row id info held in myArray(78, 1) and the column header would be located at myArray(1, 2). So if myArray(78, 1) held "Student 78" and myArray(1, 2) held "Name", then the data held within array location myArray(78,2) would represent the "Name" for "Student 78".

The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET. subscribe