Search code examples
c#excel-interop

How to iterate throgh a specific row in Excel table via Interop?


So, I'm writing a program that is reads table data and puts cells values in a List. I made it, but there is one problem – UsedRange takes all cells on sheet so there is more items then I need and also, when I specify range by ["A:A", Type.Missng] it gives me an exception:

System.ArgumentException: "HRESULT: 0x80070057 (E_INVALIDARG))"

So my question is how to make it correctly?

Code is:

foreach (Excel.Range row in usedRange)
{

    for(int i=0; i<lastCell.Row; i++) 
    {
        if (row.Cells[4, i + 1].Value2 != null)
        {
            personlist.Add(Convert.ToString(row.Cells[4, i + 1].Value2));
        }
        else { i++; }   
    }

    foreach(var person in personlist) {
        Console.WriteLine(person);
    }
}

UPD: I need a last used row, that's why I'm using UsedRange. So if there is any alternatives, like, checking if(!=null)? I will gladly try it

Tried to give it specific range, some tries to made a code like here C# - How do I iterate all the rows in Excel._Worksheet? and here https://overcoder.net/q/236542/программно-получить-последнюю-заполненную-строку-excel-с-помощью-c but maybe I'm a dumb one, 'cause there is literally more than one articles about it and non of it works with me


Solution

  • The problem is 'used range' can include empty range (who knows how excel decides that magic number - if you type a letter on some arbitrary row and then delete it Excel can decide that cell is still part of your used range). You want your own custom definition of what a 'usedRange' is, which presumably is the range of non-blank rows. There's two straightforward ways of implementing this yourself (which gives you added control over it should you want to customize it).

    You can just filter the list after the fact removing all blank entries. Or you can process the list in reverse, skipping rows till you find one matching your criteria

    bool startProcessing = false;
    for(int i=lastCell.Row-1; i>=0; i--) 
    {
        if(!startProcessing){//bool is in case you want blank rows in the middle of the file, otherwise check valid row always
            //check if valid row
            //continue; if not, set startProcessing to true if yes
        }
        if (row.Cells[4, i + 1].Value2 != null)
        {
            personlist.Add(Convert.ToString(row.Cells[4, i + 1].Value2));
        }
        //else { i++; } //this is a bug, will cause a line skip
    }
    

    Also, as an aside - when you call i++; in the body of your for loop, it then calls it again in the header of your for loop and i += 2 skipping a row. Use continue; or just remove the else block altogether.

    There's probably a way to get a cellRange matching your criteria, but imo doing it yourself can be better - you can ensure it does exactly what you want.