views:

38

answers:

0

I have started using a TDD approach to develop a small app that reads data from Excel files. Using a repository pattern type approach I have come to a hurdle which baffles me.

In order to read the Excel files, I am using the OpenXml-SDK. Now typically reading from an Excel file using the SDK requires several if not more steps to actually get the values you want to read.

The approach I have taken thus far is reflected in the following test and accompanying function.

    [Test]
    public void GetRateData_ShouldReturn_SpreadSheetDocument()
    {
        //Arrange
        var fpBuilder = new Mock<IDirectoryBuilder>();
        fpBuilder.Setup(fp => fp.FullPath()).Returns(It.IsAny<string>());

        var doc = new Mock<IOpenXmlUtilities>();
        doc.Setup(d => d.OpenReadOnlySpreadSheet(It.IsAny<string>()))
             .Returns(Mock.Of<SpreadsheetDocument>());

        swapData = new SwapRatesRepository(fpBuilder.Object, doc.Object);

        //Act
        var result = swapData.GetRateData();

        //Assert
        doc.Verify();
        fpBuilder.Verify();
    }

public class SwapRatesRepository: IRatesRepository<SwapRates>
{
    private const string SWAP_DATA_FILENAME = "DATE_MKT_ZAR_SWAPFRA1.xlsx";
    private IDirectoryBuilder builder;
    private IOpenXmlUtilities openUtils;

    public SwapRatesRepository(IDirectoryBuilder builder)
    {
        // TODO: Complete member initialization
        this.builder = builder;
    }

    public SwapRatesRepository(IDirectoryBuilder builder, 
                                       IOpenXmlUtilities openUtils)
    {
        // TODO: Complete member initialization
        this.builder = builder;
        this.openUtils = openUtils;
    }

    public SwapRates GetRateData()
    {
        // determine the path of the file based on the date
        builder.FileName = SWAP_DATA_FILENAME;
        var path = builder.FullPath();

        // open the excel file
        using(SpreadsheetDocument doc = openUtils.OpenReadOnlySpreadSheet(path))
        {
            //WorkbookPart wkBookPart = doc.WorkbookPart;
            //WorksheetPart wkSheetPart = wkBookPart.WorksheetParts.First();
            //SheetData sheetData = wkSheetPart.Worksheet
            //                                 .GetFirstChild<SheetData>();

        }

        return new SwapRates(); // ignore this class for now, design later 
    }
}

However, the next steps after the spreadsheet is open would be to actually start interrogating the Excel object model to retrieve the values. As noted above, I making use of mocks for anything open xml related. However, in some cases the objects can't be mocked(or I don't know how to mock them since they are static). That gave rise to IOpenXmlUtilities which are merely simple wrapper calls into the OpenXml-SDK.

In terms of design, we know that reading data from excel files is a short term solution (6-8 months), so these tests only affect the repository/data access for the moment.

Obviously I don't want to leave the TDD approach(as tempting as it is), so I am looking for advise and guidance on how to continue my TDD endeavours with the OpenXml SDK. The other aspect relates to mocking - I am confused as to when and how to use mocks in this case. I don't want to unknowingly writes tests that test the OpenXml-SDK.

*Side note: I know that the SOLIDity of my design can be improved but I leaving that for now. I have a set of separate tests that relate to the builder object. The other side effect that may occur is the design of an OpenXML-SDK wrapper library.

Edit: Unbeknown at the time, by creating the OpenXML-SDK wrappers for the OpenXML-SDK, i have used a design pattern similar (or exact) called the Adaptor pattern.