Read excel file row by row, cell by cell C#

Question

I want (as title states) to programmatically read values from an Excel file. Row by row and then cell by cell, to have the freedom of creating custom collections out of cell's data.

This questions helped me.

But I need more flexible code. Can I for example write (* is just for all columns)

            Range range1 = worksheet.get_Range("*1", Missing.Value)

            foreach (Range r in range1)
            {
                string user = r.Text;
                string value = r.Value2;
            }

And iterate all cells in row 1 as long as there is next. There must be some elegant way to iterate through rows and cells in C#.

possible duplicate of [read excel data line by line with c# .net](http://stackoverflow.com/questions/16160676/read-excel-data-line-by-line-with-c-sharp-net) — mcy, Mar 01 '14 at 20:37
@varocarbas should it be interop? If yes then you may be correct — mcy, Mar 01 '14 at 20:40
@YairNevet this question does not expect an answer based on a generic C# code, but on the corresponding interop classes. — varocarbas, Mar 01 '14 at 20:40
@mcy get_Range and Range and Office (Excel) Interop classes. Also the link the OP refers relies on Office Interop. — varocarbas, Mar 01 '14 at 20:41
@varocarbas true. Still the question I mentioned has an interop answer as well with 4 upvotes. — mcy, Mar 01 '14 at 20:43
@mcy You are right. But this answer does not address exactly what the OP is asking (as you can see in my answer): he wants to iterate through all the rows in a given column, for example. The answer there shows a loop going through all the cells. In any case, to consider a question a duplicate, I understand that it has to be a better match (the question should also be similar enough to the one here). — varocarbas, Mar 01 '14 at 20:47

varocarbas · Accepted Answer · 2014-03-01T20:43:36.467

6

You can rely on the Rows/Columns properties and then iterate through all the contained ranges (Cells). Sample code:

Range range1 = worksheet.Rows[1]; //For all columns in row 1
//Range range1 = worksheet.Columns[1]; //for all rows in column 1

foreach (Range r in range1.Cells) //range1.Cells represents all the columns/rows
{
    // r is the range of the corresponding cell
}

edited Mar 01 '14 at 20:43

answered Mar 01 '14 at 20:38

varocarbas

12,354
4
26
37

should I check if r.Text is empty string for a break? – eomeroff Mar 01 '14 at 20:46
@eomeroff r is a normal range associated with a cell (e.g., "A1") and thus you can do with it anything you want. – varocarbas Mar 01 '14 at 20:49
when would foreach stop in this case? – eomeroff Mar 01 '14 at 20:51
1

@eomeroff Ah sorry (didn't understood your question). This loop would go though all the cells (if range1 is a row; through all the columns). You can set a break whenever you want. Also you might create a different set of cells to iterate through (instead of a whole column/row); this code just shows you how to proceed with iterations for certain rows/columns. – varocarbas Mar 01 '14 at 20:53

score 1 · Answer 2 · answered Mar 01 '14 at 20:59

1

Try this:

 Excel.Range r = worksheet.get_Range("*1", Missing.Value);
 for (int j = 0; j < r.Rows.Count; j++) {
    Excel.Range currentCell = r.Rows[j + 1] as Excel.Range;
 }

answered Mar 01 '14 at 20:59

Mitja Bezenšek

2,503
1
14
17

1

Have you tried this code before posting it? "*1" does not mean column 1 in Excel. I have tried it and crashes. – varocarbas Mar 05 '14 at 14:55

Read excel file row by row, cell by cell C#

2 Answers2