FOREACH instead FOR

[RedLeader]

Hi,

Maybe this is stupid question but i can`t sort this out.

This is my code .How can I replace for with Foreach in this case

ArrayList fnames = new ArrayList();

Worksheet wsh = (Worksheet)wb.Sheets[1]; 
Range cell = null;


for( int i = 1; i <= 5163; i++)
{
	cell = (Range)wsh.Cells[i,1];
	
	try
	{
		fnames.Add((string)cell.Value2);
	}
	catch
	{}
}  

Thanks

[mark007]

Something like the following, though I'm a VB rather than C# guy:

 

ArrayList fnames = new ArrayList();

Worksheet wsh = (Worksheet)wb.Sheets[1]; 
Range cell = null;
Range r=wsh.Range(wsh.Range("a1"),wsh.Range("a5162"))

             for each cell in r
{
			
	try
	{
		fnames.Add((string)cell.Value2);
	}
	catch
	{}
} 

 

:)

[RedLeader]

Thank you mark007

I wanna use Foreach coz I don't know count of Excell Rows (5163 is just my example )

Can you tell me how to get this count or how to loop this without specify number of rows in excell?

Regards

[RedLeader]

Problem Solved!

This maybe is silly way but i couldn't found better

int i = 1;
while( true )
{
cell = (Range)wsh.Cells[i,1];
if( cell.Value2 == null )
break;
else
{
	try
	{
	fnames.Add((string)cell.Value2);
	}
	catch
	{}
}
i++;
}  

[mskeel]

My intention is not to trash your code so please do not take it that way, you did a good job. This is more a style difference that I find more readible but it is up to the programmer's personal style. I prefer writing this particular loop this way becuase it removes the infinite loop, break statement, and if statement that are slightly more complex and difficutl to read and replaces them with a loop invariant based on the same logic. You could make a boolean variable false instead of breaking and pull some of the cell assignment stuff up to initialize the loop sort of like this...

 

int i = 1;
cell = (Range)wsh.Cells[i,1];
bool cellsHaveValues = (cell.Value2 != null)  //it might also be more readable to use the if statement.
while (cellsHaveValues)
{
try
{
	     fnames.Add((string)cell.Value2);
}
catch
{
            //catch something here or don't bother try-catching..
        }
i++;

cell = (Range)wsh.Cells[i,1];
cellsHaveValues = (cell.Value2 != null)
}

Another thing that would probably even improve on this code could be a do-while loop but I prefer this style..

[RedLeader]

Hi mskeel,

Thanks for your post your code is more "readable" .I will use it.

Regards,

RL

[mark007]

To get the last row with something in it in column A you can use:

 

lngRow=wsh.Range("a65536").End(xlUp).Row

 

Though the c# code could well be very different as it doesn't interface with the Excel object model too well so has it's own PIA's.

 

:)

[Legend]

Excel also has a WorkSheets.UsedRange property that is useful if you're only interested in cells with data in them.

[mark007]

Sure you could also use:

 

lngRow=wsh.UsedRange.Rows.count

 

It is slightly less reliable in that it doesn't reset as often as it should but most of the time would suffuce fine.

 

:)