Cannot loop through rows in C#

[Guest]

Though not new to .Net development, I am new to programming against the Office object model, so this may be a simple question. However, it's driving me crazy, so thanks again for your help.

I have an Excel file that I need to load into the object model and process it through a command processing engine that we have written. I had initially tried to accomplish this via the OleDb driver for Excel, but because the data must be written back to the Excel file (which has ~300 columns and +2000 rows) and because formatting must be preserved, I am having to use the Office PIA method. Therefore, after looking into how Excel uses Ranges for virtually everything, I wrote a small test function to iterate through my worksheet and write out the first column -- unfortunately, this does not work and I'm at my wits end trying to figure out what I'm doing wrong. The code is posted below -- thanks.

_howard

ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = true;
_Workbook wbook = app.Workbooks.Open(this.txtExcelFile.Text, 0, false, 5, string.Empty,
string.Empty, true, XlPlatform.xlWindows, string.Empty, false, false, 0, true, false,
false);
try
{
//get first worksheet
Sheets sheets = wbook.Sheets;
Worksheet firstSheet = (Worksheet)wbook.ActiveSheet;

Range rngUsed = firstSheet.UsedRange;

//write total number of rows and cols
Debug.WriteLine("Rows: " + rngUsed.Rows.Count.ToString());
Debug.WriteLine("Cols: " + rngUsed.Columns.Count.ToString());

foreach(Range currentRow in rngUsed.Rows) //FAILS HERE!!!
{
Debug.WriteLine(((Range)currentRow[1,1]).Value2.ToString());
}

 

[jacob]

Hi,
I do not know anything about .NET but looking at your
code it looks like you are tryig to use a for each
construct to get the value for each cell in rngUsed. The
way I would do this in VBA is:

Dim cl as Range, rngUsed as Range

Set rngUsed = firstSheet.UsedRange

For Each cl in rngUsed 'loop through each cell in rngUsed
Debug.Print cl.Value2
Next

This way you get values one cell at a time. Do not think
there is a simple way to do it a whole row at a time, you
would have to concatenate cell values keeping track of
where you are in the range.

Hope this helps.

ojv

-----Original Message-----
Though not new to .Net development, I am new to

programming against the Office object model, so this may
be a simple question. However, it's driving me crazy, so
thanks again for your help.

I have an Excel file that I need to load into the object

model and process it through a command processing engine
that we have written. I had initially tried to
accomplish this via the OleDb driver for Excel, but
because the data must be written back to the Excel file
(which has ~300 columns and +2000 rows) and because
formatting must be preserved, I am having to use the
Office PIA method. Therefore, after looking into how
Excel uses Ranges for virtually everything, I wrote a
small test function to iterate through my worksheet and
write out the first column -- unfortunately, this does
not work and I'm at my wits end trying to figure out what
I'm doing wrong. The code is posted below -- thanks.

_howard

ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = true;
_Workbook wbook = app.Workbooks.Open

(this.txtExcelFile.Text, 0, false, 5, string.Empty,

string.Empty, true, XlPlatform.xlWindows,

string.Empty, false, false, 0, true, false,

false);
try
{
//get first worksheet
Sheets sheets = wbook.Sheets;
Worksheet firstSheet = (Worksheet) wbook.ActiveSheet;

Range rngUsed = firstSheet.UsedRange;

//write total number of rows and cols
Debug.WriteLine("Rows: " + rngUsed.Rows.Count.ToString());
Debug.WriteLine("Cols: " + rngUsed.Columns.Count.ToString());

foreach(Range currentRow in

rngUsed.Rows) //FAILS HERE!!!

{
Debug.WriteLine(((Range)currentRow [1,1]).Value2.ToString());
}
.

 

[onedaywhen]

According to MS, this is a 'problem':

PRB: "Member Not Found" Error Message When You Use a For Each
Statement on an Excel Collection with Visual Basic .NET or Visual C#
..NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;328347

So here's a workaround:

Excel.Range currentRow;
for(int i=1; i<=(int)rngUsed.Rows.Count; i++)
{
currentRow = (Excel.Range)rngUsed[i,1];
MessageBox.Show(currentRow.Value2.ToString());
}

--

Though not new to .Net development, I am new to programming against the Office object model, so this may be a simple question. However, it's driving me crazy, so thanks again for your help.

I have an Excel file that I need to load into the object model and

process it through a command processing engine that we have written.
I had initially tried to accomplish this via the OleDb driver for
Excel, but because the data must be written back to the Excel file
(which has ~300 columns and +2000 rows) and because formatting must be
preserved, I am having to use the Office PIA method. Therefore, after
looking into how Excel uses Ranges for virtually everything, I wrote a
small test function to iterate through my worksheet and write out the
first column -- unfortunately, this does not work and I'm at my wits
end trying to figure out what I'm doing wrong. The code is posted
below -- thanks.

_howard

ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = true;
_Workbook wbook = app.Workbooks.Open(this.txtExcelFile.Text, 0, false, 5, string.Empty,
string.Empty, true, XlPlatform.xlWindows, string.Empty, false, false, 0, true, false,
false);
try
{
//get first worksheet
Sheets sheets = wbook.Sheets;
Worksheet firstSheet = (Worksheet)wbook.ActiveSheet;

Range rngUsed = firstSheet.UsedRange;

//write total number of rows and cols
Debug.WriteLine("Rows: " + rngUsed.Rows.Count.ToString());
Debug.WriteLine("Cols: " + rngUsed.Columns.Count.ToString());

foreach(Range currentRow in rngUsed.Rows) //FAILS HERE!!!
{
Debug.WriteLine(((Range)currentRow[1,1]).Value2.ToString());
}