How to mock rows in a Excel VSTO plugin?

Question

I am trying to put a mocked Range (which contains cells with values) inside the rows of a new Range. But when I try to access a specific element from the Range, a exception is thrown.

I've tried everything, does anyone have a idea what I am doing wrong here?

Exception

Message: Test method xxx.MockUtilsTest.MockRowsTest threw exception: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: Cannot apply indexing with [] to an expression of type 'Castle.Proxies.RangeProxy'

Test

[TestMethod]
public void MockRowsTest()
{
    var row1 = MockUtils.MockCells("test_row_1", "test_row_1");
    var row2 = MockUtils.MockCells("test_row_2", "test_row_2");
    var range = MockUtils.MockRows(row1, row2);

    Assert.IsNotNull(range);
    Assert.AreEqual(2, range.Count);
    Assert.IsNotNull(range.Rows);
    Assert.AreEqual(2, range.Rows.Count);
    Assert.AreSame(row1, range.Rows[1].Cells[1]); // exception is thrown here
    Assert.AreSame(row2, range.Rows[2].Cells[1]);
    Assert.AreEqual("test_row_1", range.Rows[1].Cells[1].Value2);
    Assert.AreEqual("test_row_2", range.Rows[2].Cells[1].Value2);
}

MockUtils

public static Range MockCellValue2(Object value)
{
    var cell = new Moq.Mock<Range>();
    cell.Setup(c => c.Value2).Returns(value);

    return cell.Object;
}

public static Range MockCells(params Object[] values)
{
    var cells = new Moq.Mock<Range>();
    for (int i = 0; i < values.Length; i++)
    {
        var cell = MockCellValue2(values[i]);
        cells.SetupGet(c => c[i + 1, Moq.It.IsAny<Object>()]).Returns(cell);
    }

    var row = new Moq.Mock<Range>();
    row.SetupGet(r => r.Cells).Returns(cells.Object);
    row.SetupGet(r => r.Count).Returns(values.Length);

    return row.Object;
}

public static Range MockRows(params Range[] rows)
{
    var mergedRows = MergeRanges(rows);
    var range = new Moq.Mock<Range>();
    range.SetupGet(r => r.Count).Returns(rows.Length);
    range.SetupGet(r => r.Rows).Returns(() => mergedRows);
    range.Setup(r => r.GetEnumerator()).Returns(rows.GetEnumerator());

    return range.Object;
}

public static Range MergeRanges(params Range[] ranges)
{
    var range = new Moq.Mock<Range>();
    for (int i = 0; i < ranges.Length; i++)
    {
        range.SetupGet(r => r[i + 1, Moq.It.IsAny<Object>()]).Returns(ranges[i]);
    }

    range.SetupGet(r => r.Count).Returns(ranges.Length);
    range.Setup(r => r.GetEnumerator()).Returns(ranges.GetEnumerator());

    return range.Object;
}

Answer 1

The indexer of Range returns a dynamic object, this is the source of your problem.

Moq use Castle Dynamic proxy to generate fake objects, Castle.Proxies.RangeProxy is the generated class in your case. Since this object is not a COM object the processing of those the C# Runtime binder is being called. The Runtime binder resolves the type and looking for the indexer method, but he failed to resolve it because the generated class don't have it.

The easiest way to solve your is to return the indexer result to a strict Range local variable:

Then your test will fail because range.Rows[1] is equals to row1...

So change your test code to:

[TestMethod]
public void MockRowsTest()
{
    var row1 = MockUtils.MockCells("test_row_1", "test_row_1");
    var row2 = MockUtils.MockCells("test_row_2", "test_row_2");
    var range = MockUtils.MockRows(row1, row2);

    Assert.IsNotNull(range);
    Assert.AreEqual(2, range.Count);
    Assert.IsNotNull(range.Rows);
    Assert.AreEqual(2, range.Rows.Count);
    Range x = range.Rows[1];
    Range y = range.Rows[2];
    var xCell = x.Cells[1];
    var yCell = y.Cells[1];
    Assert.AreSame(row1, x); 
    Assert.AreSame(row2, y);
    Assert.AreEqual("test_row_1", xCell.Value2);
    Assert.AreEqual("test_row_2", yCell.Value2);
}

The above UT will pass the test. IMO you should to break your aggregation calls to "atomic OPS(multi line) and methods" not because it will pass the test, because it will make your code a debug friendly code.(I call that "the 11TH rule" where your code will be read a least 10 more times from the time it was written... So let the compiler to remove the transitive local variables and make your it a debug friendly code..).

Here you can read a simple and short explanation with links on how dynamic works in c#.

Here you can read more about Castle Dynamic Proxy.

BTW; you also can do:

Range x = range.Rows[1].Cells;
var str = x[1].Value2;

to receive the value