Cell from ExcelRange

Question

This problem has me completely puzzled.

I have a Excel document which loads in just fine. It has rows, columns and data and I want to iterate through the rows. But EPPLus is odd.

I take the second row:

ExcelRange range1 = worksheet.Cells[2, worksheet.Dimension.Start.Column, 2, worksheet.Dimension.End.Column];

Which gives me {A2:D2} Splendid! so far so good but then I want the first cell of the row:

ExcelRange range2 = range1[1,1];

Which give me {A1} and to make matter worse, the value of range1 has also changed to {A1} instead of the row I selected.

How can I resolve this issue and take a ExcelRange from an ExcelRange?

This has me completely puzzled .... thanks for anyhelp

Answer 1

I did have the same problem, to get the correct start cell:

var range2 = worksheet.Cells[range1.Start.Row, range1.Start.Column];

And the same for the bottom right cell:

var range3 = worksheet.Cells[range1.End.Row, range1.End.Column];

Answer 2

If you look at the code behind the ExcelRange Indexer you will see that the get will actually set the base address (the nested else):

public ExcelRange this[string Address]
{
    get
    {
        if (_worksheet.Names.ContainsKey(Address))
        {
            if (_worksheet.Names[Address].IsName)
            {
                return null;
            }
            else
            {
                base.Address = _worksheet.Names[Address].Address;
            }
        }
        else
        {
            base.Address = Address;
        }
        _rtc = null;
        return this;
    }
}

Why they did it this way I am not sure (I assume there its an implementation detail). But that would explain why referencing another address changes the selected range. So, like Benexx said, have to do a direct reference from the Cells collection of the Worksheet.