Important namespaces used:
using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Drawing;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;
using OpenQA.Selenium.Support.UI;
using System.Threading;
using System.Configuration;
using System.Globalization;
Function to Create Result:
public void createResult(string StartTime)
{
Microsoft.Office.Interop.Excel.Application TestLog = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook TestLogFile = TestLog.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Logs.Name = string.Format("Test Results");
Logs.Cells[1, 1] = "Test Execution Results – (ABC)";
Logs.Cells[1, 6] = string.Format("Start Time: {0}", StartTime.ToString());
Logs.Cells[3, 1] = "Test Module";
Logs.Cells[3, 2] = "Test Case Id";
Logs.Cells[3, 3] = "Test Case Description";
Logs.Cells[3, 4] = "Start Time";
Logs.Cells[3, 5] = "End Time";
Logs.Cells[3, 6] = "Result";
Microsoft.Office.Interop.Excel.Range R1 = Logs.get_Range("D1").EntireColumn;
R1.NumberFormat = "MM/dd/yyyy hh:mm:ss AM/PM";
Microsoft.Office.Interop.Excel.Range R2 = Logs.get_Range("E1").EntireColumn;
R2.NumberFormat = "MM/dd/yyyy hh:mm:ss AM/PM";
Logs.get_Range("A1", "E1").Interior.Color = System.Drawing.Color.SkyBlue;
Logs.get_Range("A3", "F3").Interior.Color = System.Drawing.Color.Gainsboro;
TestLogFile.SaveAs(@"C:\TestResults.xls");
TestLogFile.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Logs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLogFile);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLog);
}
Function to add summary:
public void addSummary(string StartTime, string EndTime, string description,string TestPlanId, string TestCaseId, string Outcome)
{
Microsoft.Office.Interop.Excel.Application TestLog = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook TestLogFile = TestLog.Workbooks.Open(@"C:\TestResults.xls");//.Add();
Microsoft.Office.Interop.Excel.Worksheet Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = Logs.UsedRange;
int colcount = range.Columns.Count;
int rowcount = range.Rows.Count;
Logs.Cells[rowcount + 1, 1] = TestPlanId;
Logs.Cells[rowcount + 1, 2] = TestCaseId;
Logs.Cells[rowcount + 1, 3] = description;
Logs.Cells[rowcount + 1, 4] = string.Format("{0}", StartTime.ToString());
Logs.Cells[rowcount + 1, 5] = string.Format("{0}", EndTime.ToString());
Logs.Cells[rowcount + 1, 6] = Outcome;
TestLogFile.Save();
TestLogFile.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Logs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLogFile);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLog);
}
Function for Test Case End Time:
public void endTimefunction(string EndTime)
{
Microsoft.Office.Interop.Excel.Application TestLog = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook TestLogFile = TestLog.Workbooks.Open(@"C:\TestResults.xls");//.Add();
Microsoft.Office.Interop.Excel.Worksheet Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = Logs.UsedRange;
Logs.Cells[2, 6] = string.Format("End Date Time: {0}", EndTime.ToString());
TestLogFile.Save();
TestLogFile.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Logs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLogFile);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLog);
}
Function for Test Case Start Time:
public void startTimefunction(string startTime)
{
Microsoft.Office.Interop.Excel.Application TestLog = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook TestLogFile = TestLog.Workbooks.Open(@"C:\TestResults.xls");//.Add();
Microsoft.Office.Interop.Excel.Worksheet Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Logs = (Worksheet)TestLogFile.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range range = Logs.UsedRange;
Logs.Cells[1, 6] = string.Format("Start Date Time: {0}", startTime.ToString());
TestLogFile.Save();
TestLogFile.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Logs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLogFile);
System.Runtime.InteropServices.Marshal.ReleaseComObject(TestLog);
}
Function for Execution Log:
public void Executionlog(string Time,string TestPlanId, string TestCaseId, string Message)
{
string filePath = @"C:\TestResults";
string file = string.Format(@"{0}\TP{1} - ExecutionLog {2}.txt", filePath, "Automation", DateTime.Now.ToString("MM-dd-yyyy"));
FileInfo logFile = new FileInfo(file);
StreamWriter log = logFile.AppendText();
string logMessage = string.Format(">> {0},TM: {1}, TC: {2}, {3} \n", Time,TestPlanId, TestCaseId, Message);
log.WriteLine(logMessage);
log.Flush();
log.Close();
}
Function for Error Log:
public void ErrorLog(string Time,string TestPlanId, string TestCaseId, string Message)
{
string filePath = @"C:TestResults";
string file = string.Format(@"{0}\TP{1}-TC{2} - ErrorLog {3}.txt", filePath, "Automation", TestCaseId, DateTime.Now.ToString("MM-dd-yyyy"));
FileInfo logFile = new FileInfo(file);
StreamWriter log = logFile.AppendText();
string logMessage = string.Format(">> {0},TM: {1}, TC: {2} {3}\n",Time, TestPlanId, TestCaseId, Message);
log.WriteLine(logMessage);
log.Flush();
log.Close();
}
Function to read data from data file:
public string dataFile(string input)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range xlrange;
string URL="URL";
string UserName="UserName";
string Password="Password";
string result="";
int xlRowCnt = 2;
xlApp = new Microsoft.Office.Interop.Excel.Application();
//Open Excel file
xlWorkBook = xlApp.Workbooks.Open(@"C:\Data Sheet.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlrange = xlWorkSheet.UsedRange;
//int abc = 1;
if (input.Equals(URL))
{
result = (string)(xlrange.Cells[xlRowCnt, 1] as Microsoft.Office.Interop.Excel.Range).Value2;
}
else if (input.Equals(UserName))
{
result = (string)(xlrange.Cells[xlRowCnt, 2] as Microsoft.Office.Interop.Excel.Range).Value2;
}
else if (input.Equals(Password))
{
result = (string)(xlrange.Cells[xlRowCnt, 3] as Microsoft.Office.Interop.Excel.Range).Value2;
}
xlWorkBook.Close();
return result;
}
Function for Controller:
public string module(string TestCaseId)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range xlrange;
string xlString;
string module = "";
int xlRowCnt = 0;
xlApp = new Microsoft.Office.Interop.Excel.Application();
//Open Excel file
xlWorkBook = xlApp.Workbooks.Open(@"C:\Controller.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlrange = xlWorkSheet.UsedRange;
//int abc = 1;
for (xlRowCnt = 1; xlRowCnt <= xlrange.Rows.Count; xlRowCnt++)
{
xlString = (string)(xlrange.Cells[xlRowCnt, 1] as Microsoft.Office.Interop.Excel.Range).Value2;
if (xlString.Equals(TestCaseId))
{
module = (string)(xlrange.Cells[xlRowCnt, 3] as Microsoft.Office.Interop.Excel.Range).Value2;
}
}
xlWorkBook.Close();
return module;
}
Function to read write from excel:
public string excel(string TestCaseId)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range xlrange;
string xlString;
int xlRowCnt = 0;
xlApp = new Microsoft.Office.Interop.Excel.Application();
//Open Excel file
xlWorkBook = xlApp.Workbooks.Open(@"C:\Controller.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlrange = xlWorkSheet.UsedRange;
for (xlRowCnt = 1; xlRowCnt <= xlrange.Rows.Count; xlRowCnt++)
{
xlString = (string)(xlrange.Cells[xlRowCnt, 1] as Microsoft.Office.Interop.Excel.Range).Value2;
if (xlString.Equals(TestCaseId))
{
mode = (string)(xlrange.Cells[xlRowCnt, 4] as Microsoft.Office.Interop.Excel.Range).Value2;
//Console.WriteLine(mode);
}
}
xlWorkBook.Close();
return mode;
}
Function to add description to result file:
public string description(string TestCaseId)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
Microsoft.Office.Interop.Excel.Range xlrange;
string xlString;
string desc="";
int xlRowCnt = 0;
xlApp = new Microsoft.Office.Interop.Excel.Application();
//Open Excel file
xlWorkBook = xlApp.Workbooks.Open(@"C:\QQ Catalyst Project\Controller Sheet\Controller.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlrange = xlWorkSheet.UsedRange;
for (xlRowCnt = 1; xlRowCnt <= xlrange.Rows.Count; xlRowCnt++)
{
xlString = (string)(xlrange.Cells[xlRowCnt, 1] as Microsoft.Office.Interop.Excel.Range).Value2;
if (xlString.Equals(TestCaseId))
{
desc = (string)(xlrange.Cells[xlRowCnt, 2] as Microsoft.Office.Interop.Excel.Range).Value2;
}
}
xlWorkBook.Close();
return desc;
}
end...