public string CompareExcel_Exact(string sReportName, int iRowStart, int iRowEnd, int iColStart, int iColEnd, int[,] Cells_Skip, string[] Sheets_Skip, string[] Sheets_Compare, Boolean bPrintCurrentRpt = false) { if (bPrintCurrentRpt) { this._PrintCurrentReport(sReportName); } int iTotalSheets = 0; string sCellBaseline = ""; string sCellCompare = ""; Boolean bMatch = true; string sLogContent = ""; int iMaxNumOfErrorLogged = 20; //// Baseline string tempreportname = sReportName; if (!_gLib._FileExists(_sBaselineDir + tempreportname, false)) { tempreportname = sReportName.Replace(".xlsx", ".xls"); if (!_gLib._FileExists(_sBaselineDir + tempreportname, false)) { sLogContent = "File NOT Exist: " + _sBaselineDir + tempreportname + Environment.NewLine; this._Report(_PassFailStep.Fail, sReportName, sLogContent); return("File_NOT_Exist"); } } MyExcel _excelBaseline = new MyExcel(_sBaselineDir + tempreportname, Config.bExcelVisible); //// QA tempreportname = sReportName; if (!_gLib._FileExists(_sCompareDir + sReportName, false)) { tempreportname = sReportName.Replace(".xlsx", ".xls"); if (!_gLib._FileExists(_sCompareDir + tempreportname, false)) { sLogContent = "File NOT Exist: " + _sCompareDir + tempreportname + Environment.NewLine; this._Report(_PassFailStep.Fail, sReportName, sLogContent); return("File_NOT_Exist"); } } MyExcel _excelCompare = new MyExcel(_sCompareDir + tempreportname, Config.bExcelVisible); if (!_excelBaseline.OpenExcelFile(1)) { this._Report(_PassFailStep.Fail, sReportName, "Fail to open excel baseline file: " + _sBaselineDir + sReportName); return("Fail to open excel baseline file"); } if (!_excelCompare.OpenExcelFile(1)) { this._Report(_PassFailStep.Fail, sReportName, "Fail to open excel compare file: " + _sCompareDir + sReportName); return("Fail to open excel compare file"); } if (_excelBaseline.ReturnNumOfSheets() != _excelCompare.ReturnNumOfSheets()) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); ////_gLib._MsgBoxYesNo("Error", "Number of Excel sheets NOT match for report <" + sReportName + ">. Please manual check!"); this._Report(_PassFailStep.Fail, sReportName, "Excel sheet number NOT match"); return("SheetNum_Not_Match"); } iTotalSheets = _excelCompare.ReturnNumOfSheets(); if (iTotalSheets == 0) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to get excel sheet number from compare rpt"); return("Fail to get excel sheet number from compare rpt"); } sLogContent = sLogContent + _sBaselineDir + sReportName + Environment.NewLine; sLogContent = sLogContent + _sCompareDir + sReportName + Environment.NewLine; for (int iSheet = 1; iSheet <= iTotalSheets; iSheet++) { _excelBaseline.OpenExcelFile(iSheet); _excelCompare.OpenExcelFile(iSheet); string sSheetName_Baseline = ""; string sSheetName_Compare = ""; sSheetName_Baseline = _excelBaseline.getActiveSheetName(); if (sSheetName_Baseline.Equals("")) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to get ActiveSheetName from: " + _sBaselineDir + sReportName); return("Fail to get ActiveSheetName"); } sSheetName_Compare = _excelCompare.getActiveSheetName(); if (sSheetName_Compare.Equals("")) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to get ActiveSheetName from: " + _sCompareDir + sReportName); return("Fail to get ActiveSheetName"); } if (sSheetName_Baseline != sSheetName_Compare) { ////////_gLib._MsgBoxYesNo("Error", "Sheet Name NOT match for report <" + sReportName + "> => sheet No. <" + iSheet.ToString() + "> Please manual check!"); this._Report(_PassFailStep.Fail, sReportName, "Excel sheet name NOT match"); _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); return("SheetName_Not_Match"); } int iTotalRow = 0; int iTotalCol = 0; int iRowStart_CurrentSheet = 0; int iColStart_CurrentSheet = 0; int iRowEND_CurrentSheet = 0; int iColEND_CurrentSheet = 0; int iTotalRow_Baseline = _excelBaseline.getTotalRowCount(); int iTotalCol_Baseline = _excelBaseline.getTotalColumnCount(); int iTotalRow_Compare = _excelCompare.getTotalRowCount(); int iTotalCol_Compare = _excelCompare.getTotalColumnCount(); //////if (iTotalRow_Baseline != iTotalRow_Compare) ////// _gLib._MsgBoxYesNo("Error", "Total Row number NOT match for report <" + sReportName + "> => sheet <" + sSheetName_Baseline + "> Please manual check!"); //////if (iTotalCol_Baseline != iTotalCol_Compare) ////// _gLib._MsgBoxYesNo("Error", "Total Column number NOT match for report <" + sReportName + "> => sheet <" + sSheetName_Baseline + "> Please manual check!"); Boolean bSkipThisSheet = false; for (int i = 0; i < Sheets_Skip.Length; i++) { if (sSheetName_Compare.ToUpper().Equals(Sheets_Skip[i].ToUpper())) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); sLogContent = sLogContent + "Sheet <" + sSheetName_Compare + "> skipped based on User setting. " + Environment.NewLine; bSkipThisSheet = true; break; } } if (bSkipThisSheet) { continue; } for (int i = 0; i < Sheets_Compare.Length; i++) { if (!sSheetName_Compare.ToUpper().Equals(Sheets_Compare[i].ToUpper())) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); bSkipThisSheet = true; break; } } if (bSkipThisSheet) { continue; } if (iTotalRow_Baseline >= iTotalRow_Compare) { iTotalRow = iTotalRow_Baseline; } else { iTotalRow = iTotalRow_Compare; } if (iTotalCol_Baseline >= iTotalCol_Compare) { iTotalCol = iTotalCol_Baseline; } else { iTotalCol = iTotalCol_Compare; } if (iRowStart == 0) { iRowStart_CurrentSheet = 1; } else { iRowStart_CurrentSheet = iRowStart; } if (iColStart == 0) { iColStart_CurrentSheet = 1; } else { iColStart_CurrentSheet = iColStart; } if (iRowEnd == 0) { iRowEND_CurrentSheet = iTotalRow; } else { iRowEND_CurrentSheet = iRowEnd; } if (iColEnd == 0) { iColEND_CurrentSheet = iTotalCol; } else { iColEND_CurrentSheet = iColEnd; } int iTotalErrorCellNumber = 0; if (!_excelBaseline.expandAllLevels(iRowEND_CurrentSheet, iColEND_CurrentSheet)) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to Expand All Levels from: " + _sBaselineDir + sReportName); return("Fail to Expand All Levels"); } if (!_excelCompare.expandAllLevels(iRowEND_CurrentSheet, iColEND_CurrentSheet)) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to Expand All Levels from: " + _sCompareDir + sReportName); return("Fail to Expand All Levels"); } for (int i = iRowStart_CurrentSheet; i <= iRowEND_CurrentSheet; i++) { if (sReportName.Contains("LiabilitySetforGlobeExport")) { if ((i == 6) || (i == 7) || (i == 8) || (i == 17) || (i == 18)) { continue; } } int iSikpAnswer = 100; Boolean bSkip = false; for (int j = iColStart_CurrentSheet; j <= iColEND_CurrentSheet; j++) { bSkip = false; iSikpAnswer = this._SkipAnswer(i, j, Cells_Skip); switch (iSikpAnswer) { case 0: //// 0 - Not skip break; case 1: //// 1 - skip this row bSkip = true; break; case 2: //// 2 - skip this column bSkip = true; break; case 3: //// 3 - skip this cell bSkip = true; break; default: break; } if (bSkip) { continue; } sCellBaseline = _excelBaseline.getOneCellValue(i, j); sCellCompare = _excelCompare.getOneCellValue(i, j); if (!sCellBaseline.Equals(sCellCompare)) { iTotalErrorCellNumber++; bMatch = false; double valueBaseline, valueCompare; if (double.TryParse(sCellBaseline, out valueBaseline) && double.TryParse(sCellCompare, out valueCompare) && (!this._dTolerancePercent.Equals(0.0))) { if (Math.Abs((valueBaseline - valueCompare) / valueBaseline) < (this._dTolerancePercent / 100)) { _excelCompare.setOneCellColor_Yellow(i, j); if (iTotalErrorCellNumber < iMaxNumOfErrorLogged) { sLogContent = sLogContent + "In Tolerance: " + this._dTolerancePercent + "% " + sSheetName_Baseline + " => Cell(" + i + " ," + j + ")" + " Baseline: " + sCellBaseline + " <=> Compare: " + sCellCompare + Environment.NewLine; } } else { _excelCompare.setOneCellColor_Red(i, j); if (iTotalErrorCellNumber < iMaxNumOfErrorLogged) { sLogContent = sLogContent + "Out Tolerance: " + this._dTolerancePercent + "% " + sSheetName_Baseline + " => Cell(" + i + " ," + j + ")" + " Baseline: " + sCellBaseline + " <=> Compare: " + sCellCompare + Environment.NewLine; } } } else { _excelCompare.setOneCellColor_Red(i, j); if (iTotalErrorCellNumber < iMaxNumOfErrorLogged) { sLogContent = sLogContent + "Not Match: " + sSheetName_Baseline + " => Cell(" + i + " ," + j + ")" + " Baseline: " + sCellBaseline + " <=> Compare: " + sCellCompare + Environment.NewLine; } } if (iTotalErrorCellNumber == iMaxNumOfErrorLogged) { sLogContent = sLogContent + "********** More error cells (exceeds " + iMaxNumOfErrorLogged + " ) are NOT logged here, pleae refer to its Excel file! ********** " + Environment.NewLine; } } } } _excelBaseline.SaveExcel(); _excelBaseline.CloseExcelApplication(); _excelCompare.SaveExcel(); _excelCompare.CloseExcelApplication(); } _gLib._KillProcessByName("notepad"); if (!bMatch) { this._Report(_PassFailStep.Fail, sReportName, sLogContent); return("Rpt_Not_Match"); } else { this._Report(_PassFailStep.Pass, sReportName, sLogContent); return("Rpt_Match"); } //////////////////////////////////_excelBaseline.SaveExcel(); //////////////////////////////////_excelBaseline.CloseExcelApplication(); //////////////////////////////////_excelCompare.SaveExcel(); //////////////////////////////////_excelCompare.CloseExcelApplication(); }
public void CompareExcel_Exact_BySheetName(string sReportName, string sSheetName, int iRowStart, int iRowEnd, int iColStart, int iColEnd, int[,] Cells_Skip, Boolean bPrintCurrentRpt = false) { if (bPrintCurrentRpt) { this._PrintCurrentReport(sReportName); } int iTotalSheets = 0; string sCellBaseline = ""; string sCellCompare = ""; Boolean bMatch = true; string sLogContent = ""; int iMaxNumOfErrorLogged = 20; if (!_gLib._FileExists(_sBaselineDir + sReportName, false)) { sLogContent = "File NOT Exist: " + _sBaselineDir + sReportName + Environment.NewLine; this._Report(_PassFailStep.Fail, sReportName, sLogContent); return; } if (!_gLib._FileExists(_sCompareDir + sReportName, false)) { sLogContent = "File NOT Exist: " + _sCompareDir + sReportName + Environment.NewLine; this._Report(_PassFailStep.Fail, sReportName, sLogContent); return; } MyExcel _excelBaseline = new MyExcel(_sBaselineDir + sReportName, Config.bExcelVisible); MyExcel _excelCompare = new MyExcel(_sCompareDir + sReportName, Config.bExcelVisible); sLogContent = sLogContent + _sBaselineDir + sReportName + Environment.NewLine; sLogContent = sLogContent + _sCompareDir + sReportName + Environment.NewLine; _excelBaseline.OpenExcelFile(sSheetName); _excelCompare.OpenExcelFile(sSheetName); int iTotalRow = 0; int iTotalCol = 0; int iRowStart_CurrentSheet = 0; int iColStart_CurrentSheet = 0; int iRowEND_CurrentSheet = 0; int iColEND_CurrentSheet = 0; int iTotalRow_Baseline = _excelBaseline.getTotalRowCount(); int iTotalCol_Baseline = _excelBaseline.getTotalColumnCount(); int iTotalRow_Compare = _excelCompare.getTotalRowCount(); int iTotalCol_Compare = _excelCompare.getTotalColumnCount(); if (iTotalRow_Baseline >= iTotalRow_Compare) { iTotalRow = iTotalRow_Baseline; } else { iTotalRow = iTotalRow_Compare; } if (iTotalCol_Baseline >= iTotalCol_Compare) { iTotalCol = iTotalCol_Baseline; } else { iTotalCol = iTotalCol_Compare; } if (iRowStart == 0) { iRowStart_CurrentSheet = 1; } else { iRowStart_CurrentSheet = iRowStart; } if (iColStart == 0) { iColStart_CurrentSheet = 1; } else { iColStart_CurrentSheet = iColStart; } if (iRowEnd == 0) { iRowEND_CurrentSheet = iTotalRow; } else { iRowEND_CurrentSheet = iRowEnd; } if (iColEnd == 0) { iColEND_CurrentSheet = iTotalCol; } else { iColEND_CurrentSheet = iColEnd; } int iTotalErrorCellNumber = 0; if (!_excelBaseline.expandAllLevels(iRowEND_CurrentSheet, iColEND_CurrentSheet)) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to Expand All Levels from: " + _sBaselineDir + sReportName); return; } if (!_excelCompare.expandAllLevels(iRowEND_CurrentSheet, iColEND_CurrentSheet)) { _excelBaseline.CloseExcelApplication(); _excelCompare.CloseExcelApplication(); this._Report(_PassFailStep.Fail, sReportName, "Fail to Expand All Levels from: " + _sCompareDir + sReportName); return; } for (int i = iRowStart_CurrentSheet; i <= iRowEND_CurrentSheet; i++) { if (sReportName.Contains("LiabilitySetforGlobeExport")) { if ((i == 6) || (i == 7) || (i == 8) || (i == 17) || (i == 18)) { continue; } } int iSikpAnswer = 100; Boolean bSkip = false; for (int j = iColStart_CurrentSheet; j <= iColEND_CurrentSheet; j++) { bSkip = false; iSikpAnswer = this._SkipAnswer(i, j, Cells_Skip); switch (iSikpAnswer) { case 0: //// 0 - Not skip break; case 1: //// 1 - skip this row bSkip = true; break; case 2: //// 2 - skip this column bSkip = true; break; case 3: //// 3 - skip this cell bSkip = true; break; default: break; } if (bSkip) { continue; } sCellBaseline = _excelBaseline.getOneCellValue(i, j); sCellCompare = _excelCompare.getOneCellValue(i, j); if (!sCellBaseline.Equals(sCellCompare)) { iTotalErrorCellNumber++; bMatch = false; double valueBaseline, valueCompare; if (double.TryParse(sCellBaseline, out valueBaseline) && double.TryParse(sCellCompare, out valueCompare) && (!this._dTolerancePercent.Equals(0.0))) { if (Math.Abs((valueBaseline - valueCompare) / valueBaseline) < (this._dTolerancePercent / 100)) { _excelCompare.setOneCellColor_Yellow(i, j); if (iTotalErrorCellNumber < iMaxNumOfErrorLogged) { sLogContent = sLogContent + "In Tolerance: " + this._dTolerancePercent + "% " + sSheetName + " => Cell(" + i + " ," + j + ")" + " Baseline: " + sCellBaseline + " <=> Compare: " + sCellCompare + Environment.NewLine; } } else { _excelCompare.setOneCellColor_Red(i, j); if (iTotalErrorCellNumber < iMaxNumOfErrorLogged) { sLogContent = sLogContent + "Out Tolerance: " + this._dTolerancePercent + "% " + sSheetName + " => Cell(" + i + " ," + j + ")" + " Baseline: " + sCellBaseline + " <=> Compare: " + sCellCompare + Environment.NewLine; } } } else { _excelCompare.setOneCellColor_Red(i, j); if (iTotalErrorCellNumber < iMaxNumOfErrorLogged) { sLogContent = sLogContent + "Not Match: " + sSheetName + " => Cell(" + i + " ," + j + ")" + " Baseline: " + sCellBaseline + " <=> Compare: " + sCellCompare + Environment.NewLine; } } if (iTotalErrorCellNumber == iMaxNumOfErrorLogged) { sLogContent = sLogContent + "********** More error cells (exceeds " + iMaxNumOfErrorLogged + " ) are NOT logged here, pleae refer to its Excel file! ********** " + Environment.NewLine; } } } } _excelBaseline.SaveExcel(); _excelBaseline.CloseExcelApplication(); _excelCompare.SaveExcel(); _excelCompare.CloseExcelApplication(); if (!bMatch) { this._Report(_PassFailStep.Fail, sReportName + " - " + sSheetName, sLogContent); } else { this._Report(_PassFailStep.Pass, sReportName + " - " + sSheetName, sLogContent); } _gLib._KillProcessByName("notepad"); }