Excel with c#.net: loop through range?

[ThienZ]

if i have a range :

Excel.Range S = (Excel.Range) oApp.Selection;

which has one columns and more than rows, how can i loop through each cell?

In VBA it was just like this :

For Each mycell In S 
  '...
Next mycell

 

Thx

[PlausiblyDamp]

C# has a foreach operator that behaves the same as For ... Each in VB have you looked into using that?

[ThienZ]

actually i tried this before :

foreach(Excel.Range cell in S.Cells) 

 

but this didn't work, it said :

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll

Additional information: Member not found.

 

i tried to look for the members of S (Selection) but didn't fond any that would fit....

 

Can anyone help me plz?

 

Thx in advance.

[Mike_R]

For reasons that make no sense whatsoever, the For..Each enumeration is not available on many Excel objects that do have them in VBA or VB6 Automation. The Range.Cells collection is the most commonly used one that one will hit.

 

I believe that this is due to a flaw in the PIAs, but I can't say for sure. Perhaps COM [_Enum] For..Each enumeration is not supported through the Interop?? (Somehow I find that very hard to believe though.) Excel has other quirks such as Excel.Application events being private (that is, it is unavailable in .Net) unless you modify the CIL code yourself by hand.

 

Anyway, you will have to enumerate your Ranges as:

For r = 1 to xlRng.Rows.Count
   for c = 1 to xlRng.Columns.Count
       myValue = xlRng(r,c).Value
   Next c
Next r

(Please forgive the VB-code, I'm sure you get the idea.)

 

Making this worse, if the Range is a multi-area range, then you'll need to enumerate the areas as well. (Again, I think For..Each is not avail. for the Areas collection from .Net again):

For a = 1 to xlRng.Areas.Count
   Dim area As Range = xlRng.Areas(a)
   For r = 1 to area.Rows.Count
       for c = 1 to area.Columns.Count
           myValue = area(r,c).Value
       Next c
   Next r
Next a

Note too that when Range.Areas(1).Cells.Count is > 1 then xlRng.Value will return an array consisting of all the values within Areas(1).Cells(). This array can then can be enumerated vastly faster than iterating through the Range.Cells(), which is notoriously slow. See this thread for more info: Why is this running so slow?.

 

Hope this helps...

Mike

Edited May 20, 2005 by Mike_R

[ThienZ]

wow.. thx mike...

 

and thx too that you reminded me that the range can be multi area range too... :)

[ThienZ]

i've just read the other thread about "why is it running so slow", and.... should i do this :

for(int a=1; a<=xlRng.Areas.Count; a++) 
{
Excel.Range area = xlRng.Areas[a];
for(int r = 1; r<=area.Rows.Count; r++) 
{
	for(int c = 1; c<=area.Columns.Count; c++) 
	{
		datarow = dtBNr.NewRow();
		datarow[Constants.strcolNr] = ((Excel.Range) area[r,c]).Value2;
		dtBNr.Rows.Add(datarow);
	}
}
}

or better like this :

for(int a=1; a<=xlRng.Areas.Count; a++) 
{
Excel.Range area = xlRng.Areas[a];
string [,] str = new string[area.Rows.Count,area.Columns.Count];
str = area.??? //somehow copy the range into a 2d-array
for(int r = 0; r<area.Rows.Count; r++) 
{
	for(int c = 0; c<area.Columns.Count; c++) 
	{
		datarow = dtBNr.NewRow();
		datarow[Constants.strcolNr] = str[r,c];
		dtBNr.Rows.Add(datarow);
	}
}
}

or do both need the same time to process the code?

 

thx in advance :)