Mocking OpenXML SDK SpreadsheetDocument

Question

I would like to use process described here: Automated Testing OpenXML SDK (also touched here: Unit testing an application that talks to microsoft word via OpenXML)

However, what does it take to mock the something like this? I have made the following interface:

public interface IExcelDocument
{
    Row GetRow(uint rowIndex, SheetData sheetData);
    SharedStringTablePart GetSharedStringTablePart(SpreadsheetDocument excelDoc);
    WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName);
    Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart);
    Row InsertRow(WorksheetPart worksheetPart);
    int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart);
}

I would imagine mocking would look something like this:

[TestMethod()]
public void Excel_GetWorkseetPartTest()
{
    Mock<IExcelDocument> mockExcelDocument = new Mock<IExcelDocument>();
    string sheetName = "sheet";
    var excelMock = mockExcelDocument.Object.GetWorksheetPart(MySpreadsheetDocument, sheetName);

    Assert.IsTrue(excelMock != null);
}

GetWorksheetPart method which I want to unit test and resides in the class which implements the interface IExcelDocument looks like this:

public WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName)
{
    Sheet sheet = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>()
        .SingleOrDefault(s => s.Name == sheetName);
    if (sheet == null)
    {
        throw new ArgumentException(
            String.Format("No sheet named {0} found in spreadsheet {1}",
                sheetName, _filePath), "sheetName");
    }
    return (WorksheetPart)excelDoc.WorkbookPart.GetPartById(sheet.Id);
}

I am not able to wrap around MySpreadsheetDocument because I would need to also implement the SpreadsheetDocument.Open method and not sure even if that is reasonable.

Here is how I call GetWorksheetPart:

using (SpreadsheetDocument _excelDoc = SpreadsheetDocument.Open(_filePath, true))
{
    IExcelDocument excelDoc = new ExcelDocument();
    WorksheetPart worksheetPart = excelDoc.GetWorksheetPart(_excelDoc, sheetName);
}

Is your method under test in a class implementation of the interface? Your wording is a little confusing — Nkosi, Jul 27 '16 at 13:21
if that is the case then you are confusing the concept of abstracting your dependencies for your unit test. — Nkosi, Jul 27 '16 at 13:28
Ok, could you please give a small example on the particular case? — Janis S., Jul 27 '16 at 13:32
@Nkosi do you have some private contact which I could use to ask you one SO semi-related question? — Janis S., Jul 05 '17 at 11:38
@Nkosi no problem, just curiosity. Wanted to ask regards your profile and how does this go together with your professional career.. Could not find a private way to contact, thus commented here. — Janis S., Jul 06 '17 at 22:02

Nkosi · Accepted Answer · 2016-07-27T13:49:19.480

You are confusing the concept of abstracting your dependencies for your unit test.

given an example class

public class ExcelDocument {

    public WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName)
    {
        Sheet sheet = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>()
            .SingleOrDefault(s => s.Name == sheetName);
        if (sheet == null)
        {
            throw new ArgumentException(
                String.Format("No sheet named {0} found in spreadsheet {1}",
                    sheetName, _filePath), "sheetName");
        }
        return (WorksheetPart)excelDoc.WorkbookPart.GetPartById(sheet.Id);
    }
}

this method is dependent on external component SpreadsheetDocument

SpreadsheetDocument is what needs to be abstracted in this case.

Looking at the method under test, the method needs to be able to get a Sheet so your abstraction must provide that functionality. it also needs to be able to get WorksheetPart

from this the following interface can be derived

public ISpreadsheetDocument {    
    Sheet GetSheet(string name);
    WorksheetPart GetPartById(string id);
}

This changes the Method under test to this

public WorksheetPart GetWorksheetPart(ISpreadsheetDocument excelDoc, string sheetName)
{
    Sheet sheet = excelDoc.GetSheet(sheetName);
    if (sheet == null)
    {
        throw new ArgumentException(
            String.Format("No sheet named {0} found in spreadsheet {1}",
                sheetName, _filePath), "sheetName");
    }
    return excelDoc.GetPartById(sheet.Id);
}

You can now mock/fack the excelDoc if needed for your unit tests and then your production implementation would wrap the external functionality.

public class SpreadsheetDocumentWrapper : ISpreadsheetDocument {
    private SpreadsheetDocument excelDoc;
    public SpreadsheetDocumentWrapper(SpreadsheetDocument excelDoc) {
        this.excelDock = excelDock;
    }

    public Sheet GetSheet(string name) {
        return excelDoc.WorkbookPart.Workbook.Descendants<Sheet>()
            .SingleOrDefault(s => s.Name == sheetName);
    }

    public WorksheetPart GetPartById(string id) {
        return (WorksheetPart)excelDoc.WorkbookPart.GetPartById(id);
    }
}

So what you need to do is look at your ExcelDocument class, identify its dependencies and abstract those dependencies out into services that you can mock for unit testing.

Thanks Nkosi, well described. I am trying to mock now. I have created the example here: https://dotnetfiddle.net/KZSN5B. Could you please suggest on how would the unit test for the `GetWorksheetPart` look like? — Janis S., Jul 28 '16 at 11:16
Posted this as a new question. As this one is already answered. Thanks again. http://stackoverflow.com/questions/38638048/mocking-openxml-with-moq — Janis S., Jul 28 '16 at 14:54

Mocking OpenXML SDK SpreadsheetDocument

1 Answers1