EPPlus, applying formula to non-contiguous range

Question

I'm working in C# with EPPlus v 4.1 and not having luck applying a formula to a non-contiguous block of cells.

I'm able to use the following code to apply a formula to a range of selected cells that are contiguous.

Worksheet.Select(new ExcelAddress("T2:T10"));
Worksheet.SelectedRange.Formula = "=if(A2=\"\",\"empty\",\"not empty\")";

This works great because it changes the formula so that for cell T2 the cell that is checked is A2, and for cell T3 the cell that is checked is A3, etc.

However, when I have a non-contiguous block of cells, the application of the formula appears to fail, and only the first cell in the selected range receives the formula. The following code results in just cell T2 receiving the formula.

Worksheet.Select(new ExcelAddress("T2,T5,T8,T10"));
Worksheet.SelectedRange.Formula = "=if(A2=\"\",\"empty\",\"not empty\")";

The same is true when I use the Cells property.

// awesome
Worksheet.Cells["T2:T10"].Formula = "=if(A2=\"\",\"empty\",\"not empty\")";
// not so awesome
Worksheet.Cells["T2,T5,T8,T10"].Formula = "=if(A2=\"\",\"empty\",\"not empty\")";

Does this sound like a bug (known or unknown), or is this more of an error on my part?

I've also posted this on the codeplex site last week, but that appears to be going away.

Thanks!

Is it better than [Open-XML-SDK](https://www.nuget.org/packages/DocumentFormat.OpenXml/)? — Alex78191, May 31 '17 at 22:23
I haven't used that, so I can't say. But thanks for the suggestion. — Emilio, Jun 01 '17 at 18:36

score 0 · Accepted Answer · answered Jun 01 '17 at 00:33

I think you are are right. If you look at the code at https://epplus.codeplex.com/SourceControl/changeset/view/643d411b032b#EPPlus/ExcelRangeBase.cs it sets the _changePropMethod delegate in SetDelegate() called by the constructor:

private void SetDelegate()
{
    if (_fromRow == -1)
    {
        _changePropMethod = SetUnknown;
    }
    //Single cell
    else if (_fromRow == _toRow && _fromCol == _toCol && Addresses == null)
    {
        _changePropMethod = SetSingle;
    }
    //Range (ex A1:A2)
    else if (Addresses == null)
    {
        _changePropMethod = SetRange;
    }
    //Multi Range (ex A1:A2,C1:C2)
    else
    {
        _changePropMethod = SetMultiRange;
    }
}

which is set to SetMultiRange that contains a loop through all Addresses and calls Set_Value on each. This is eventually used by the setter of the Value property:

else
{
    _changePropMethod(this, _setValueDelegate, value);
}

But NOT by the Formula property setter:

else if (_fromRow == _toRow && _fromCol == _toCol)
{
    Set_Formula(this, value, _fromRow, _fromCol);
}

For some reason it does a direct call to Set_Formula. If this is intentional it would be very strange. More likely it is a bug as you mentioned. Hopefully they will respond. Or if you are feeling brave you could folk the repo and try to fix it yourself :). (hopefully they will move the codeplex repo to github or something since they still seem to be making commits as recently as a week ago).

EPPlus, applying formula to non-contiguous range

1 Answers1