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.