Simple way of generating test data in an Excel file

excel-based-unit-tests.cs

// Dependencies: NUnit
// Remember: copy excel file to output directory

public class ExcelBasedTests
{
   [Test, TestCaseSource(typeof(TestData), nameof(TestData.GetPeriodStartDateCases))]
   public void RegisterUserTest(PeriodStartDateModel input)
   {
      input.ColumnA.ShouldNotBeNullOrEmpty();
      input.ColumnB.ShouldNotBeNullOrEmpty();
      input.ColumnC.ShouldNotBeNullOrEmpty();
   }
}

public class PeriodStartDateModel
{
   public string ColumnA { get; set; }
   public string ColumnB { get; set; }
   public string ColumnC { get; set; }
}

public class TestData
{
   public static IEnumerable<TestCaseData> GetPeriodStartDateCases()
   {
      var relativeFilename = Path.Combine("Util", "period-start-date.xlsx");

      return BuildTestCases(relativeFilename, row =>
      {
         var model = new PeriodStartDateModel();

         model.ColumnA = row["A"].ToString();
         model.ColumnB = row["B"].ToString();
         model.ColumnC = row["C"].ToString();

         return model;
      });
   }

   private static IEnumerable<TestCaseData> BuildTestCases(string relativeFilename, Func<DataRow, object> mapper)
   {
      var path = Path.Combine(TestContext.CurrentContext.TestDirectory, relativeFilename);

      var isBinaryFormat = path
         .ToLower()
         .EndsWith("xls");

      using (var fileStream = File.OpenRead(path))
      {
         var excelDataReader = isBinaryFormat 
            ? ExcelReaderFactory.CreateBinaryReader(fileStream) 
            : ExcelReaderFactory.CreateOpenXmlReader(fileStream);

         using (excelDataReader)
         {
            excelDataReader.IsFirstRowAsColumnNames = true;

            var dataset = excelDataReader.AsDataSet();
            foreach (DataRow row in dataset.Tables[0].Rows)
            {
               var testCase = mapper(row);
               yield return new TestCaseData(new[] { testCase });
            }
         }
      }
   }
}