コード例 #1
0
 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);