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 didnt 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 didnt 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 cant 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, Im sure you get the idea.)

Making this worse, if the Range is a multi-area range, then youll 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

 

[ThienZ]

wow.. thx mike...

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

 

[ThienZ]

ive 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