public bool CompareWithXML(ExcelCell actual)
{
return(CellComparator.CompareCellsXML(Value, actual.Value, Format));
}
public bool CompareExcelReport(string actualPath)
{
Excel.Application app = null;
Excel.Workbook goldenCopy = null;
Excel.Workbook actualWorkbook = null;
var errorList = ExcelComparator.CompareExcelDataTable(goldenCopyPath, actualPath);
try
{
app = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false
};
goldenCopy = app.Workbooks.Open(goldenCopyPath, Notify: false);
commentList = new List <CellComment>();
result = true;
//Open files to compare
actualWorkbook = app.Workbooks.Open(actualPath, Notify: false);
foreach (Excel.Worksheet expectedWS in goldenCopy.Worksheets)
{
// Discard Hidden tabs
if (!expectedWS.Visible.Equals(Excel.XlSheetVisibility.xlSheetVisible))
{
continue;
}
Excel.Worksheet actualWS = null;
try
{
actualWS = actualWorkbook.Worksheets[expectedWS.Name];
var worksheetErrorCells = errorList[expectedWS.Name];
LogHelpers.Write("Starting Review of potential errors sheet: " + expectedWS.Name);
// Find the last real row and column that has a value.
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
//Checks that cells matches
int total = worksheetErrorCells.Count;
int perc = 0;
error = 0;
foreach (var cell in worksheetErrorCells)
{
int i = cell.Item1;
int j = cell.Item2;
ExcelCell expected = new ExcelCell(expectedWS.Cells[i, j]);
ExcelCell actual = new ExcelCell(actualWS.Cells[i, j]);
if (!CellsAreEqual(expected, actual))
{
actualWS.MarkCellAsError(i, j);
ComparatorError(i, j, expected.GetValueWithFormat(), actual.GetValueWithFormat());
}
perc++;
}
// Add error cell comments
foreach (CellComment c in commentList)
{
if (c.text != null)
{
actualWS.Cells[c.row, c.col].AddComment(c.text);
}
}
commentList.Clear();
if (total != 0)
{
LogHelpers.Write("Completed review: " + (perc * 100 / total) + "% " + perc + "/" + total + " Errors found:" + error + " Elapsed Time: " + stopWatch.Elapsed.TotalSeconds);
}
else
{
LogHelpers.Write("Completed review: no potential errors found");
}
}
catch (COMException)
{
// The whole sheet is missing, i add it to the actual result and marked as diff.
LogHelpers.Write("Missing Worksheet " + expectedWS.Name);
Excel.Worksheet missingWS = actualWorkbook.Worksheets.Add();
missingWS.Name = expectedWS.Name;
missingWS.Tab.Color = Excel.XlRgbColor.rgbRed;
missingWS.MarkCellAsError(1, 1);
result = false;
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(expectedWS);
if (actualWS != null)
{
Marshal.ReleaseComObject(actualWS);
}
}
}
//Close workbooks
if (!result)
{
string fileName = string.Format("Result_{0}_{1}", DateTime.Now.Add(default).ToString("MM-dd-yyyy_HHmmss", new CultureInfo("en-US")), actualWorkbook.Name);