CODEWITHSUNDEEP
c#excel
CodeMonkey
CodeMonkey

Reputation: 12424

C# how to iterate over excel columns

I want to get a specific column of an excel sheet and then iterate through it's cells. I want it to look something like this:

Excel.Workbook workbook = app.Workbooks.Open(svDropPath);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)workbook.Sheets["Sheet Name"];
var col = xlWorkSheet.UsedRange.Columns["C:C", Type.Missing]; // I want the 3rd column
foreach(Cell c in col)
....

How do I actually make this foreach loop?

Upvotes: 4

Views: 21283

Answers (2)

jwg
jwg

Reputation: 5827

I believe there is no nice way of doing it other than to loop through the indices in question and use either Cells or Rows:

for (int i = 1; i <= max; i++)
{
  Range cell = col.Cells[i, 1];
  // or
  Range cell = col.Rows[i];
}

However, note that if you are reading and/or writing all the cells, you are much better off reading/writing the whole column to/from an array of object, and then looping through the array items, as outlined in my answer https://stackoverflow.com/a/18058144/1737957 . Not only is this much faster, you can also use nicer language constructs for looping since you are now dealing with a straightforward C# array.

The only reason you would have to loop rather than do this AFAIK is if you were accessing something like conditional formats etc., rather than just cell contents, and you couldn't write a whole range of them in one statement. However there may be ways of doing these too using arrays.

Upvotes: 5

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Your loop will looks as follow:

    foreach (Excel.Range item in col.Cells)
    {
        //whatever you want to do with your cells, here- msgbox of cells value
        MessageBox.Show(Convert.ToString(item.Value));
    }

Upvotes: 8

Related Questions