/// <summary>
/// This procedure will insert summary details and format the sheet.
/// </summary>
public static void InsertSummaryDetailsAndFormat()
{
var applicationClass = new ApplicationClass();
Workbook workbook = null;
try
{
workbook = WorkBookUtility.OpenWorkBook(applicationClass, FilePath);
dynamic tcworksheet = (Worksheet)workbook.Sheets["TestIterations"];
//// Set as active sheet
tcworksheet.Activate();
//// Set summary details
((Range)tcworksheet.Cells[1, 2]).Value = Timing.TotalStartTime.ToString("MM/dd/yyyy hh:mm:ss tt");
((Range)tcworksheet.Cells[2, 2]).Value = Timing.TotalEndTime.ToString("MM/dd/yyyy hh:mm:ss tt");
((Range)tcworksheet.Cells[3, 2]).Value = Timing.Totalduration.ToString();
//// Set style for results, column 4 from row 6
var row = 6;
dynamic result = (Range)tcworksheet.Cells[row, 4];
while (!string.IsNullOrEmpty(result.Value))
{
//// Set style for result
if (result.Value == Entities.Constants.Pass)
{
var tmp = workbook.Styles;
result.Style = GetStyle(ReportingStyles.StylePassed, ref tmp);
((Range)tcworksheet.Cells[row, 1]).EntireRow.AutoFit();
}
else if (result.Value == Entities.Constants.Fail)
{
var tmp = workbook.Styles;
result.Style = GetStyle(ReportingStyles.StyleFailed, ref tmp);
}
((Range)tcworksheet.Cells[row, 1]).EntireRow.WrapText = false;
((Range)tcworksheet.Cells[row, 2]).EntireRow.WrapText = false;
((Range)tcworksheet.Cells[row, 3]).EntireRow.WrapText = false;
((Range)tcworksheet.Cells[row, 4]).EntireRow.WrapText = false;
((Range)tcworksheet.Cells[row, 5]).EntireRow.WrapText = false;
((Range)tcworksheet.Cells[row, 6]).EntireRow.WrapText = false;
row += 1;
result = (Range)tcworksheet.Cells[row, 4];
}
//// Autofit all columns
((Range)tcworksheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)tcworksheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)tcworksheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)tcworksheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)tcworksheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)tcworksheet.Cells[1, 6]).EntireColumn.TextToColumns();
((Range)tcworksheet.Cells[1, 6]).EntireColumn.AutoFit();
}
catch (Exception ex)
{
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Reporting, MethodBase.GetCurrentMethod().Name);
throw;
}
finally
{
var b = workbook;
//// Close objects
if (b != null)
{
workbook.Save();
WorkBookUtility.CloseWorkBook(workbook, true);
}
WorkBookUtility.CloseExcel(applicationClass);
}
}
/// <summary>
/// This function creates a Excel sheet named as the current Test case name in Excel report file.
/// </summary>
/// <param name="errorMessage">Error message set when error in creating excel.</param>
/// <returns>True if able to create excel successfully, false otherwise.</returns>
public bool InsertTestStepResult(ref string errorMessage)
{
var applicationClass = new ApplicationClass();
Workbook workbook = null;
try
{
workbook = WorkBookUtility.OpenWorkBook(applicationClass, FilePath);
if (workbook.Worksheets == null)
{
errorMessage = string.Format(Entities.Constants.Messages.WorkSheetNotExist, TestCase.Name);
return(false);
}
var workSheet = (Worksheet)workbook.Worksheets[TestCase.Name];
workSheet.Name = TestCase.Name;
var row = 4;
var testDataIterationNr = 0;
var styles = workbook.Styles;
foreach (var resultObj in Result.TestStepsResultsCollection)
{
if (testDataIterationNr < Convert.ToInt32(resultObj.TestDataIterationNr))
{
row += 1;
//// add row to get a blank row
testDataIterationNr = Convert.ToInt32(resultObj.TestDataIterationNr);
//// Get style header
dynamic styleHeader = GetStyle(ReportingStyles.StyleHeader, ref styles);
//// Test data iteration header
((Range)workSheet.Cells[row, 1]).Value = Entities.Constants.TestResult.Iteration;
((Range)workSheet.Cells[row, 1]).Style = styleHeader;
((Range)workSheet.Cells[row, 2]).Value = resultObj.TestDataIterationNr;
//// Test steps header
row += 1;
((Range)workSheet.Cells[row, 1]).Value = Entities.Constants.TestResult.StepNumber;
((Range)workSheet.Cells[row, 1]).Style = styleHeader;
((Range)workSheet.Cells[row, 2]).Value = Entities.Constants.TestResult.Description;
((Range)workSheet.Cells[row, 2]).Style = styleHeader;
((Range)workSheet.Cells[row, 3]).Value = Entities.Constants.TestResult.Result;
((Range)workSheet.Cells[row, 3]).Style = styleHeader;
((Range)workSheet.Cells[row, 4]).Value = Entities.Constants.TestResult.Comment;
((Range)workSheet.Cells[row, 4]).Style = styleHeader;
((Range)workSheet.Cells[row, 5]).Value = Entities.Constants.TestResult.Remarks;
((Range)workSheet.Cells[row, 5]).Style = styleHeader;
row++;
}
((Range)workSheet.Cells[row, 1]).Value = resultObj.StepNr;
((Range)workSheet.Cells[row, 2]).Value = resultObj.Description;
((Range)workSheet.Cells[row, 3]).Value = resultObj.Result;
styles = workbook.Styles;
((Range)workSheet.Cells[row, 3]).Style = resultObj.Result == Entities.Constants.Pass
? GetStyle(ReportingStyles.StylePassed, ref styles)
: GetStyle(ReportingStyles.StyleFailed, ref styles);
((Range)workSheet.Cells[row, 4]).Value = resultObj.Comment.Length > 255
? resultObj.Comment.Substring(1, 255)
: resultObj.Comment;
((Range)workSheet.Cells[row, 5]).Value = resultObj.Remarks;
row += 1;
}
//// Autofit
((Range)workSheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)workSheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)workSheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)workSheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)workSheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)workSheet.Cells[1, 6]).EntireColumn.AutoFit();
}
catch (Exception ex)
{
errorMessage = ex.Message;
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Reporting, MethodBase.GetCurrentMethod().Name);
return(false);
}
finally
{
var b = workbook;
if (b != null)
{
workbook.Save();
}
WorkBookUtility.CloseWorkBook(workbook, true);
WorkBookUtility.CloseExcel(applicationClass);
}
return(true);
}
/// <summary>
/// This function creates a Excel sheet named as the current Test case name in Excel report file.
/// </summary>
/// <param name="errorMessage">Error message set when error in creating excel.</param>
/// <returns>True if able to create excel successfully, false otherwise.</returns>
public static bool CreateExcelSheet(ref string errorMessage)
{
var applicationClass = new ApplicationClass();
Workbook workbook = null;
try
{
workbook = WorkBookUtility.OpenWorkBook(applicationClass, FilePath);
var style = workbook.Styles;
//// Check length of TestCase.Name
if (TestCase.Name.Length > 31)
{
//// Add message to reporting file, sheet "TestIterations"
dynamic workSheet = workbook.Worksheets[1];
((Range)workSheet.Cells[6, 2]).Value = Entities.Constants.Messages.TestCaseNameLimit;
((Range)workSheet.Cells[6, 4]).Value = Entities.Constants.Fail;
style = workbook.Styles;
((Range)workSheet.Cells[6, 4]).Style = GetStyle(ReportingStyles.StyleFailed, ref style);
throw new Exception(Entities.Constants.Messages.TestCaseNameLimit);
}
//// Check if TestCase.Name already exist as a sheet because then the same testcase may be added several times in TestIterations.xlsx which is not allowed
foreach (Worksheet workSheet in workbook.Worksheets)
{
if (workSheet.Name == TestCase.Name)
{
dynamic workSheetNext = workbook.Worksheets[1];
((Range)workSheetNext.Cells[6, 2]).Value = string.Format(Entities.Constants.Messages.ReportAlreadyExist, FilePath, TestCase.Name);
((Range)workSheetNext.Cells[6, 4]).Value = Entities.Constants.Fail;
style = workbook.Styles;
((Range)workSheetNext.Cells[6, 4]).Style = GetStyle(ReportingStyles.StyleFailed, ref style);
throw new Exception(string.Format(Entities.Constants.Messages.ReportAlreadyExist, FilePath, TestCase.Name));
}
}
workbook.Worksheets.Add(After: workbook.ActiveSheet);
var testCaseWorkSheet = (Worksheet)workbook.ActiveSheet;
testCaseWorkSheet.Name = TestCase.Name;
dynamic styleHeader = GetStyle(ReportingStyles.StyleHeader, ref style);
((Range)testCaseWorkSheet.Cells[1, 1]).Value = Entities.Constants.TestIteration.Application;
((Range)testCaseWorkSheet.Cells[1, 1]).Style = styleHeader;
((Range)testCaseWorkSheet.Cells[1, 2]).Value = TestCase.Application;
((Range)testCaseWorkSheet.Cells[2, 1]).Value = Entities.Constants.TestIteration.TestCaseName;
((Range)testCaseWorkSheet.Cells[2, 1]).Style = styleHeader;
((Range)testCaseWorkSheet.Cells[2, 2]).Value = TestCase.Name;
((Range)testCaseWorkSheet.Cells[3, 1]).Value = Entities.Constants.TestIteration.Description;
((Range)testCaseWorkSheet.Cells[3, 1]).Style = styleHeader;
((Range)testCaseWorkSheet.Cells[3, 2]).Value = TestCase.Description;
return(true);
}
catch (Exception ex)
{
errorMessage = ex.Message;
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Reporting, MethodBase.GetCurrentMethod().Name);
return(false);
}
finally
{
var b = workbook;
if (b != null)
{
workbook.Save();
WorkBookUtility.CloseWorkBook(workbook);
}
WorkBookUtility.CloseExcel(applicationClass);
}
}
/// <summary>
/// This function creates initialize test case and test data.
/// </summary>
/// <param name="applicationClass">Application class.</param>
/// <value>Application class value.</value>
public static void LoadUiControls(ApplicationClass applicationClass)
{
try
{
var workbook = WorkBookUtility.OpenWorkBook(applicationClass, TestCase.RootFilePath + ConfigurationManager.AppSettings.Get(Entities.Constants.AppSetting.UiControlFile));
dynamic worksheet = workbook.Worksheets[1];
var rowsCount = worksheet.UsedRange.Rows.Count;
var cellCount = worksheet.UsedRange.Columns.Count + 1;
for (var rowIndex = 2; rowIndex <= rowsCount; rowIndex++)
{
if (string.IsNullOrEmpty(worksheet.Cells[rowIndex, 1].value))
{
break; // reading the sheet untill the first empty row
}
var objUiControl = new UiControl();
for (var cellIndex = 1; cellIndex <= cellCount; cellIndex++)
{
string headerValue = Convert.ToString(worksheet.Cells[1, cellIndex].value);
string dataValue = Convert.ToString(worksheet.Cells[rowIndex, cellIndex].value);
if (string.IsNullOrEmpty(headerValue))
{
TestCase.UiControls.Add(objUiControl);
break;
}
switch (headerValue.ToUpper())
{
case Entities.Constants.UiControls.UiControlId:
objUiControl.UiControlId = dataValue;
break;
case Entities.Constants.UiControls.UiControlType:
objUiControl.UiControlType = dataValue;
break;
case Entities.Constants.UiControls.UiControlSearchProperty:
objUiControl.UiControlSearchProperty = dataValue;
break;
case Entities.Constants.UiControls.UiControlSearchValue:
objUiControl.UiControlSearchValue = dataValue;
break;
default:
if (headerValue.Length > 0)
{
throw new Exception(string.Format(Entities.Constants.Messages.UiControlSheetError, headerValue));
}
TestCase.UiControls.Add(objUiControl);
break;
}
}
}
WorkBookUtility.CloseWorkBook(workbook);
}
catch (Exception ex)
{
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Data, MethodBase.GetCurrentMethod().Name);
throw;
}
}
/// <summary>
/// Load Test Configurations.
/// </summary>
/// <param name="applicationClass">Name of the Application under test.</param>
private static void LoadTestConfigurations(ApplicationClass applicationClass)
{
var testdatakeyConfig = 1;
try
{
var workbook = WorkBookUtility.OpenWorkBook(applicationClass, TestCase.RootFilePath + ConfigurationManager.AppSettings.Get(Entities.Constants.AppSetting.TestConfigurationFile));
dynamic worksheet = (Worksheet)workbook.Worksheets[1];
var rowsCount = worksheet.UsedRange.Rows.Count;
var cellCount = worksheet.UsedRange.Columns.Count + 1;
for (var rowindex = 2; rowindex <= rowsCount; rowindex++)
{
if (string.IsNullOrEmpty(worksheet.Cells[rowindex, 1].value))
{
break; //// reading the sheet untill the first empty row
}
var configStep = new ConfigStep();
for (var cellindex = 1; cellindex <= cellCount; cellindex++)
{
string dataValue = Convert.ToString(worksheet.Cells[rowindex, cellindex].value);
string headerValue = Convert.ToString(worksheet.Cells[1, cellindex].value);
if (string.IsNullOrEmpty(headerValue))
{
ConfigStep.TestConfigNames.Add(configStep);
break; //// reading the sheet untill the first empty column
}
switch (headerValue.ToUpper())
{
case Entities.Constants.TestConfiguration.SNo:
configStep.TestStepNo = dataValue;
testdatakeyConfig = 1;
break;
case Entities.Constants.TestConfiguration.Datatype:
configStep.TestDataType = dataValue;
break;
case Entities.Constants.TestConfiguration.VariableName:
configStep.TestVariableName = dataValue;
break;
case Entities.Constants.TestConfiguration.TestData:
if (testdatakeyConfig > ConfigStep.TestDataConfigCount)
{
ConfigStep.TestDataConfigCount += 1;
}
testdatakeyConfig += 1;
configStep.TestDataValue = dataValue;
break;
default:
if (headerValue.Length > 0)
{
throw new Exception(string.Format(Entities.Constants.Messages.TestConfigurationError, headerValue));
}
ConfigStep.TestConfigNames.Add(configStep);
break;
}
}
}
WorkBookUtility.CloseWorkBook(workbook);
}
catch (Exception ex)
{
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Data, MethodBase.GetCurrentMethod().Name);
throw;
}
}
/// <summary>
/// Load Test Class.
/// </summary>
/// <param name="applicationClass">Name of the Application under test.</param>
public static void LoadTestCases(ApplicationClass applicationClass)
{
var testdatakeyConfig = 1;
try
{
var workbook = WorkBookUtility.OpenWorkBook(applicationClass, TestCase.RootFilePath + new StringBuilder().Append(ConfigurationManager.AppSettings.Get(Entities.Constants.AppSetting.TestCaseFolderName)).Append(Entities.Constants.DoubleBackslash).Append(TestCase.FileName));
dynamic worksheet = (Worksheet)workbook.Worksheets[1];
var rowsCount = worksheet.UsedRange.Rows.Count;
var cellCount = worksheet.UsedRange.Columns.Count + 1;
for (var rowindex = 2; rowindex <= rowsCount; rowindex++)
{
if (string.IsNullOrEmpty(Convert.ToString(worksheet.Cells[rowindex, 1].value)))
{
break; //// reading the sheet untill the first empty row
}
var testStep = new TestStep();
for (var cellindex = 1; cellindex <= cellCount; cellindex++)
{
string headerValue = worksheet.Cells[1, cellindex].value;
string dataValue = Convert.ToString(worksheet.Cells[rowindex, cellindex].value);
if (string.IsNullOrEmpty(headerValue))
{
TestCase.TestStepList.Add(testStep);
break;
}
switch (headerValue)
{
case Entities.Constants.TestStep.TestStepNumber:
testStep.TestStepNumber = dataValue;
testStep.TestData = new Dictionary <int, string>();
testdatakeyConfig = 1;
break;
case Entities.Constants.TestStep.Action:
testStep.Action = dataValue;
break;
case Entities.Constants.TestStep.UiControlId:
testStep.UiControl = TestCase.UiControls.Find(f => f.UiControlId == dataValue);
break;
case Entities.Constants.TestStep.VerificationId:
if (dataValue != null)
{
testStep.Verification = TestCase.Verifications.Find(f => f.VerificationId == dataValue);
}
break;
case Entities.Constants.TestStep.TestData:
if (dataValue != null && TestCase.TestDataSavedValues.ContainsKey(dataValue))
{
var value = TestCase.TestDataSavedValues[dataValue];
testStep.TestData.Add(testdatakeyConfig, value);
}
else
{
testStep.TestData.Add(testdatakeyConfig, dataValue);
}
if (testdatakeyConfig > TestCase.TestDataCount)
{
TestCase.TestDataCount += 1;
}
testdatakeyConfig += 1;
break;
case Entities.Constants.TestStep.Remarks:
testStep.Remarks = dataValue;
break;
default:
if (headerValue.Length > 0)
{
throw new Exception(string.Format(Entities.Constants.Messages.TestCaseError, headerValue, TestCase.RootFilePath, TestCase.FileName));
}
TestCase.TestStepList.Add(testStep);
break;
}
}
}
WorkBookUtility.CloseWorkBook(workbook);
}
catch (Exception ex)
{
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Data, MethodBase.GetCurrentMethod().Name);
throw;
}
}
/// <summary>
/// Load all Verifications.
/// </summary>
/// <param name="applicationClass">Name of the Application under test.</param>
/// <value>Application class value.</value>
public static void LoadVerifications(ApplicationClass applicationClass)
{
try
{
var workbook = WorkBookUtility.OpenWorkBook(applicationClass, TestCase.RootFilePath + ConfigurationManager.AppSettings.Get(Entities.Constants.AppSetting.VerificationFile));
dynamic worksheet = (Worksheet)workbook.Worksheets[1];
var rowsCount = worksheet.UsedRange.Rows.Count;
var cellCount = worksheet.UsedRange.Columns.Count + 1;
for (var rowIndex = 2; rowIndex <= rowsCount; rowIndex++)
{
if (string.IsNullOrEmpty(worksheet.Cells[rowIndex, 1].value))
{
break; //// reading the sheet untill the first empty row
}
var verification = new Verification();
for (var cellIndex = 1; cellIndex <= cellCount; cellIndex++)
{
string headerValue = Convert.ToString(worksheet.Cells[1, cellIndex].value);
string dataValue = Convert.ToString(worksheet.Cells[rowIndex, cellIndex].value);
if (string.IsNullOrEmpty(headerValue))
{
TestCase.Verifications.Add(verification);
break;
}
switch (headerValue.ToUpper())
{
case Entities.Constants.Verification.VerificationId:
verification.VerificationId = dataValue;
break;
case Entities.Constants.Verification.VerificationType:
verification.VerificationType = dataValue;
break;
case Entities.Constants.Verification.OperatorVerification:
verification.OperatorToUse = dataValue;
break;
default:
//// No more cell to get data from, add TestData to TestCase TestStepList
if (headerValue.Length > 0)
{
//// headerValue should be empty if we get here, something is wrong -> end all loops
throw new Exception(string.Format(Entities.Constants.Messages.VerificationError, headerValue));
}
TestCase.Verifications.Add(verification);
break;
}
}
}
WorkBookUtility.CloseWorkBook(workbook);
}
catch (Exception ex)
{
LogHelper.ErrorLog(ex, Entities.Constants.ClassName.Data, MethodBase.GetCurrentMethod().Name);
throw;
}
}