public static void ValidateWorksheet(FileInfo newFile, string sheetName, IEnumerable <ExpectedCellValue> expectedCellValues)
{
using (var excelPackage = new ExcelPackage(newFile))
{
TestHelperUtility.ValidateWorksheet(excelPackage, sheetName, expectedCellValues);
}
}
public static void ValidateWorkbook(FileInfo newFile, Dictionary <string, IEnumerable <ExpectedCellValue> > expectedSheetInfo)
{
using (var excelPackage = new ExcelPackage(newFile))
{
Assert.AreEqual(expectedSheetInfo.Count, excelPackage.Workbook.Worksheets.Count);
foreach (var sheetName in expectedSheetInfo.Keys)
{
TestHelperUtility.ValidateWorksheet(excelPackage, sheetName, expectedSheetInfo[sheetName]);
}
}
}
private static void ValidateWorksheet(ExcelPackage package, string sheetName, IEnumerable <ExpectedCellValue> expectedCellValues)
{
var worksheet = package.Workbook.Worksheets[sheetName];
Assert.IsNotNull(worksheet, $"The sheet {sheetName} was not found in the workbook.");
foreach (ExpectedCellValue cell in expectedCellValues)
{
var range = worksheet.Cells[cell.Row, cell.Column];
TestHelperUtility.ValidateRange(cell, range.Value);
if (cell.Formula != null)
{
Assert.AreEqual(cell.Formula, range.Formula);
}
}
}
private static void ValidateRange(ExpectedCellValue expectedCell, object value)
{
Assert.IsNotNull(expectedCell);
if (TestHelperUtility.ValueIsNumeric(value))
{
Assert.IsTrue(TestHelperUtility.ValueIsNumeric(expectedCell.Value), $"Expected non-numeric cell value at address [{expectedCell.Sheet},{expectedCell.Row},{expectedCell.Column}]. Actual value: {value}.");
TestHelperUtility.AssertNumericValuesAreEqual(expectedCell.Value, value, expectedCell.Sheet, expectedCell.Row, expectedCell.Column);
}
else if (value is ExcelErrorValue errorValue)
{
Assert.IsTrue(ExcelErrorValue.Values.TryGetErrorType(expectedCell.Value?.ToString(), out eErrorType errorType),
$"Expected {expectedCell.Value ?? "null"}, actual {value} at [{expectedCell.Sheet},{expectedCell.Row},{expectedCell.Column}]");
Assert.AreEqual(errorType, errorValue.Type, $"Expected {errorType}, actual {errorValue.Type} at [{expectedCell.Sheet},{expectedCell.Row},{expectedCell.Column}]");
}
else
{
Assert.AreEqual(expectedCell.Value, value, $"Cells at address [{expectedCell.Sheet},{expectedCell.Row},{expectedCell.Column}] do not match.");
}
}