Unit testing method designed to find specific worksheets in spreadsheet

Question

In an application that is designed to read a specific spreadsheet, assumed to have certain worksheets, there is a method designed to return these worksheets. This is using the Epplus library:

public ExcelWorksheet findExcelSheet(ExcelPackage spreadsheet, string v);
{
    foreach (var sheet in spreadsheet.Workbook.Worksheets)
    {
        if ((sheet.Name).CompareTo(v)==0)
        {
            // matching sheet found
            return sheet;
        }
    }

    // at this point, the sheet has not been found
    // we are assuming the user has supplied the correct spreadsheet, with the required worksheets
    // if not, the program cannot continue, as it is totally dependent on this. It will not work with any old spreadsheet
    throw new Exception("Could not find required Excel worksheet: " + v);
}

As is commented in the code, its purpose is to check the worksheets with the required names are there, and return them as ExcelWorksheet objects. They are called three times, as there are three required worksheets.

This method needs to be unit-tested with Microsoft.VisualStudio.TestTools.UnitTesting

public void findExcelSheet_Test()
{
    // arrange
    ExcelPackage testSpreadsheet = new ExcelPackage();
    ExcelWorksheet testWsFPS = testSpreadsheet.Workbook.Worksheets.Add("FPS");
    ExcelWorksheet testWsDRS = testSpreadsheet.Workbook.Worksheets.Add("DRS");
    ExcelWorksheet testWsDPC = testSpreadsheet.Workbook.Worksheets.Add("DPC");

    // act

    // assert
}

The test method above is a starting point. What would be the best way to go about this?

Answer 1

You are pretty much there. Just need to throw the exception. But, since you are using Microsoft's Testing tools, you will have to add the attribute to the unit test on the expected exceptions (other tests suites like nunit or xunit have Assert.Throws...):

[TestMethod]
[ExpectedException(typeof(Exception))]
public void findExcelSheet_Test()
{
    // arrange
    ExcelPackage testSpreadsheet = new ExcelPackage();
    ExcelWorksheet testWsFPS = testSpreadsheet.Workbook.Worksheets.Add("FPS");
    ExcelWorksheet testWsDRS = testSpreadsheet.Workbook.Worksheets.Add("DRS");
    ExcelWorksheet testWsDPC = testSpreadsheet.Workbook.Worksheets.Add("DPC");

    // act
    findExcelSheet(testSpreadsheet, Path.GetRandomFileName()); //or some other random string

    // assert

}