public void LogInfo(int iRow, int iCol, string sValue)
{
MyExcel _excelLog = new MyExcel(sLogFile, Config.bExcelVisible);
_excelLog.OpenExcelFile(sSheet);
_excelLog.setOneCellValue(iRow, iCol, sValue);
_excelLog.SaveExcel();
_excelLog.CloseExcelApplication();
}
public void LogInfo(int iCol, List <string> lsValue)
{
MyExcel _excelLog = new MyExcel(sLogFile, Config.bExcelVisible);
_excelLog.OpenExcelFile(sSheet);
for (int i = 0; i < lsValue.Count; i++)
{
_excelLog.setOneCellValue(i + 1, iCol, lsValue[i]);
}
_excelLog.SaveExcel();
_excelLog.CloseExcelApplication();
}
public void LogFail(int iRow, string sValue = "")
{
MyExcel _excelLog = new MyExcel(sLogFile, Config.bExcelVisible);
_excelLog.OpenExcelFile(sSheet);
if (sValue != "")
{
_excelLog.setOneCellValue(iRow, iCol, sValue);
}
_excelLog.setOneCellColor_Yellow(iRow, this.iCol);
_excelLog.SaveExcel();
_excelLog.CloseExcelApplication();
}
public void LogInfo(int iRow, string sValue)
{
MyExcel _excelLog = new MyExcel(sLogFile, Config.bExcelVisible);
_excelLog.OpenExcelFile(sSheet);
Boolean bSuccess = _excelLog.setOneCellValue(iRow, sCol, sValue);
_excelLog.SaveExcel();
_excelLog.CloseExcelApplication();
if (!bSuccess)
{
GenericLib gLib = new GenericLib();
gLib._MsgBoxYesNo("Continue Testing? ", "Fail to find Column Name: <" + sCol + "> Please Check! ");
}
}
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");
}
