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

Extempore

Why are you trying to get ranges from other ranges in the first place? – Josettejosey

The document contains a data structure with rows of data. Each row had to be validated. There for I take the row and forward it to a method to validate the row – Extempore

Answer 1

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.

Poland

Answer 2

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];

Patten

But I for example want to take the first cell from range2 – Extempore

I guess you can also use var range2 = range1[range1.Start.Row, range1.Start.Column]; – Patten

Answer 3

You can use the Offset method to get a sub-range of an ExcelRange. This give you a new instance of ExcelRange and does not modify the original instance.

https://www.epplussoftware.com/docs/5.5/api/OfficeOpenXml.ExcelRangeBase.html#OfficeOpenXml_ExcelRangeBase_Offset_System_Int32_System_Int32_System_Int32_System_Int32_

Here is an example:

public static void AlternateRowColor(ExcelRange range)
{
    for (var rowOffset = 1; rowOffset < range.Rows; rowOffset += 2)
    {
        using (var rowRange = range.Offset(rowOffset, 0, 1, range.Columns))
        {
            rowRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
            rowRange.Style.Fill.BackgroundColor.SetColor(Color.Cornsilk);
        }
    }
}

Benedetta