DI and ExcelDNA.Testing

[schebotar]

Hello.
I'm working on pet-project Excel add-in.
I implemented DI like this

public sealed class AddIn: IExcelAddIn
{
    public static Application Excel { get; private set; }
    public static ServiceProvider ServiceProvider { get; private set; }

    public void AutoOpen()
    {
        IServiceCollection Services = new ServiceCollection();

        Services.
            .AddSingleton((Application)ExcelDnaUtil.Application)
            .AddTransient<IExcelReader, RhExcelReader>()
            .AddTransient<IExcelWriter, RhExcelWriter>();

        ServiceProvider = Services.BuildServiceProvider();
        Excel = ServiceProvider.GetService<Application>();
    }

    public void AutoClose()
    {
    }
}

IExcelReader implementation have to found some patterns (Products) on worksheet or in range and count them.

public interface IExcelReader
{
    public Dictionary<Product, double> ReadProducts(Range range);
    public List<Dictionary<Product, double>> ReadProducts(Worksheet worksheet);
} 

All seems working in standard way.

Now I'm trying to test this project with ExcelDNA.Testing
Looks like XUnit is kinda restricted in injections. I found the way to get it worked, but it looks not good and I have a feeling there is better way.

[ExcelTestSettings(OutOfProcess = true)]
public class CanReadProducts : IDisposable
{
    private AddIn _addIn;
    private IExcelReader _reader;
    private Workbook _testWorkbook;

    public CanReadProducts()
    {
        _addIn = new();
        _testWorkbook = Util.Application.Workbooks.Add();
        _addIn.AutoOpen();
        _reader = AddIn.ServiceProvider.GetRequiredService<IExcelReader>();
    }

    [ExcelFact]
    public void CanReadRange()
    {
        Worksheet worksheet = _testWorkbook.Sheets[1];
        /// Add some test cells
        Range testRange = worksheet.Range["A1:B6"];
        var products = _reader.ReadProducts(testRange);

        Assert.NotNull(products);
        Assert.NotEmpty(products);
        ///Assertions
    }

    [ExcelFact(Workbook = @"Test.xlsx")]
    public void CanReadWorkbook()
    {
        Worksheet worksheet = Util.Workbook.Worksheets[1];
        var products = _reader.ReadProducts(worksheet);

        Assert.NotNull(products);
        Assert.NotEmpty(products);
        /// Other assertions
    }

    public void Dispose()
    {
        _addIn.AutoClose();
        Util.Application.ActiveWindow.Close(SaveChanges: false);
    }

Any tips on this?

[SKYC829]

hi @schebotar
i'm working on a unit test for my project too, i think you can try to opeartion excel by only use Microsoft.Office.Interop.Excel like a black-box test. xUnit seems to be a good choice here.
Here's how I did it:

1. create a class for test class life-time control

public sealed class TestContext : IDisposable
{

        public Application ExcelApp { get; private set; }

        public Workbook CurrentBook => ExcelApp?.ActiveWorkbook;

        public Worksheet CurrentSheet => (Worksheet)CurrentBook?.ActiveSheet;

        public Range CurrentCell => ExcelApp?.ActiveCell;
        public TestContext()
        {
            // Create and launch an excel instance
            ExcelApp = new Application();
            ExcelApp.Visible = true;
            ExcelApp.Workbooks.Add();
            // Active or add your addin to excel
            _yourAddIn = ExcelApp.AddIns.Cast<AddIn>().FirstOrDefault(p => p.FullName.Equals(AddInDir));
            if (_dogsAddIn == null)
            {
                _yourAddIn = ExcelApp.AddIns.Add(AddInDir, true);
            }
            Thread.Sleep(TimeSpan.FromSeconds(2));
            _yourAddIn .Installed = false;
            Thread.Sleep(TimeSpan.FromSeconds(1));
            _yourAddIn .Installed = true;
            Assert.NotNull(_yourAddIn );
            Assert.True(_yourAddIn .Installed, "Add-in Install failure!");
        }

        // .... other methods
}

2. create your unit test case class

    public class YourClassName: IClassFixture<TestContext>
    {
        private readonly TestContext _context;
        public OptionsTest(TestContext context)
        {
            _context = context;
        }

        // .... your other test case methods
    }

and then you can use Microsoft.Office.Interop.Excel.Range to test your udf/rtd methods and use Microsoft.WinAppDriver.Appium.WebDriver for UI test (e.g: ribbon tabs)