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 — mcy
– mcy, Commented Mar 1, 2014 at 20:37
@varocarbas should it be interop? If yes then you may be correct — mcy
– mcy, Commented Mar 1, 2014 at 20:40
@YairNevet this question does not expect an answer based on a generic C# code, but on the corresponding interop classes. — user2480047
– user2480047, Commented Mar 1, 2014 at 20:40

score 6 · Accepted Answer · 2014-03-01 20:43:36Z

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 1, 2014 at 20:43

answered Mar 1, 2014 at 20:38

user2480047

Sign up to request clarification or add additional context in comments.

4 Comments

eomeroff Over a year ago

should I check if r.Text is empty string for a break?

user2480047 Over a year ago

@eomeroff r is a normal range associated with a cell (e.g., "A1") and thus you can do with it anything you want.

eomeroff Over a year ago

when would foreach stop in this case?

user2480047 Over a year ago

@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.

Mitja Bezenšek · Accepted Answer · 2014-03-01 20:59:38Z

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 1, 2014 at 20:59

Mitja Bezenšek

2,5331 gold badge16 silver badges17 bronze badges

1 Comment

user2480047 Over a year ago

Have you tried this code before posting it? "*1" does not mean column 1 in Excel. I have tried it and crashes.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related