CODEWITHSUNDEEP
c#.netepplus
Patrick Aleman
Patrick Aleman

Reputation: 619

Cell from ExcelRange

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

Upvotes: 9

Views: 9140

Answers (3)

Mark Stafford
Mark Stafford

Reputation: 11

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);
        }
    }
}

Upvotes: 1

Ernie S
Ernie S

Reputation: 14250

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.

Upvotes: 2

Benexx
Benexx

Reputation: 128

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

Upvotes: 4

Related Questions