Scripts to write excel file using c#

Namespace:
using Microsoft.Office.Interop.Excel

Workbook eMWBook;
Sheets eMWSheet;
Worksheet eMWSheet1; 
rangeUsed ranges;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible=true;
xlApp.DisplayAlerts=false;
eMWBook =xlApp.Workbooks.Open("c:\\test.xls",0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
eMWSheet = eMWBook.Worksheets;
eMWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)eMWSheet.get_Item(<sheetName>);
ranges= eMWSheet1.Usedrange;
rownum=range.Rows.Count+1; 
eMWSheet1.Cells[rownum,1]="India";
eMWSheet1.Cells[rownum,2]="Delhi";
eMWBook.SaveAs("c:\\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,Missing.Value, Missing.Value, Missing.Value, Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value);
eMWBook.Close(true, null, null);
eMWSheet1 = null;
eMWBook = null;
xlApp.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

 

Scripts to convert excel to pdf in c# using Microsoft.Office.Interop

Namespace:
using Microsoft.Office.Interop.Excel;

Application application = new Application();
Workbook wbook = application.Workbooks.Open(@"C:\test\TestReport.xls");
foreach (Worksheet wsheet in wbook.Worksheets.OfType<Worksheet>())
{
wsheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
wsheet.PageSetup.Zoom = false;
wsheet.PageSetup.FitToPagesWide = 1;
wsheet.PageSetup.FitToPagesTall = false;
}
wbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, @"C:\test\TestReport.pdf", Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, true, true, 1, 100, false, System.Reflection.Missing.Value);

Scripts to take screenshot of a page using c#

// method takes filepath as a parameter to save captured image in specified location
// file path example: filePath=@"C:\myFile\loginPage.bmp";
// Bitmap and Graphics classes present inside System.Drawing
// Screen.PrimaryScreen.Bounds.Width is used to get system width. Screen class present inside System.windows.forms

 public void screenShotCapture(string FilePath) 
 {
  Bitmap bmap= new Bitmap(Screen.PrimaryScreen.Bounds.Width, Screen.PrimaryScreen.Bounds.Height); 
  Graphics gpc= Graphics.FromImage(bmap as System.Drawing.Image); 
  gpc.CopyFromScreen(25, 25, 25, 25, bmap.Size); 
  bmap.Save(FilePath, ImageFormat.Bmp); 
 }

when you can’t identify element on webpage and if it is returning 0 elements test studio c#

There are possibilities that DOM gets changed or some elements inside DOM gets changed or get loaded dynamically after doing some operation on the webpage like selecting an item from a dropdown. So it is diffucult to identify the webelement in such cases. One simple way to handle this is.
Ex: 
ReadOnlyCollection<Element> rows = ActiveBrowser.Find.AllByXPath("//table//tbody//tr");
Log.WriteLine(rows.Count.ToString());

O/P: 0;

Solution: make use of RefreshDomTree() as below:

ActiveBrowser.RefreshDomTree(); // This will refresh dom before executing next line of code.
ReadOnlyCollection<Element> rows = ActiveBrowser.Find.AllByXPath("//table//tbody//tr");
Log.WriteLine(rows.Count.ToString());

O/P: 2;

 

 

 

Difference between const and readonly in c#

const: It is must to assign a value to that variable and you cannot change it again and again in the program. It is implicitly static and allows you to access it through class name.

ex:  public class abc
     {
     const string myName ="Kate";
     public abc()
     {
      myName ="John"; // This will give compile time error
     }
     }
 ReadOnly: It allows us to assign value during runtime. Please note that you can assign a value only through non-static constructor.

ex:  public class abc
     {
     readonly string myName ="Kate";
     public abc()
     {
      myName ="John"; // This will run successfully
     }
     }

How to handle if .Displayed and .Enabled in selenium does not work

driver.findElement(By.Id("YourIdHere")).Displayed. There are more chances that, this line of code might return null and throw NoSuchElementFound exception when it doesn't find 
particular element on the webpage. To handle it, you need to create a simple method which return boolean value and enclose above line of code by try and catch.

Read data from an excel file using c#

This method is used to read data from excel file. It accepts path, where the excel file is located in the local drive and the sheet name inside that excel.

Let us assume excel has headers like FirstName,LastName,EmailAdress,Country etc. and there are around 10 such records in rows.

public class GeneriMethods
{
public List<userInfo> readExcelFile(string filePath, string excelSheetName)
 {
    List<userInfo> myUserList = new List<userInfo>();
 
 using (OleDboledbConnection oledbConn = new OleDboledbConnection())
 {
    string excelFileExtension = System.IO.Path.GetExtension(filePath);
 if (excelFileExtension == ".xls")
    oledbConn.oledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
 
 if (excelFileExtension == ".xlsx")
    oledbConn.oledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (OleDboledbCommand oledbComm = new OleDboledbCommand())
 {
    oledbConn.Open();
    oledbComm.oledbCommandText = String.Format("select * from [{0}${1}]", excelSheetName,"A1:IV");;

oledbComm.oledbConnection = oledbConn;

using (OleDbDataAdapter oledbDataAdapt = new OleDbDataAdapter())
 {
    oledbDataAdapt.SelectoledbCommand = oledbComm;
    OleDbDataReader oDR = oledbComm.ExecuteReader();
 
 while(oDR.Read())
 {
    userInfo userin = new userInfo();   create object of userInfo class
    userin.FirstName=oDR["First name"].ToString();
    userin.LastName=oDR["Last name"].ToString();
    userin.Email=oDR["Email"].ToString();
    userin.Street=oDR["Street"].ToString();
    userin.City=oDR["City"].ToString();
    userin.Postalcode=oDR["Postalcode"].ToString();
    userin.Region=oDR["Region"].ToString();
    myUserList.Add(userin);
 } 
 }
 }
 }
  return myUserList;
 }
 }
 
 public class userInfo
 {
    public string FirstName{get;set;}
    public string LastName{get;set;}
    public string DOB{get;set;}
    public string Email{get;set;}
    public string Street{get;set;}
    public string City{get;set;}
  }

Now, we will see how to access in the main method.

List<userInfo> userDetails = new List<userInfo>();  // create a list of userInfo
 userDetails = readExcelFile("YourFileLocationPath","YourSheetName"); // as readExcelFile return list of userInfo

foreach( userInfo a in userDetails )
 {
    Console.Writeline(a.FirstName);
    Console.Writeline(a.LastName);
    Console.Writeline(a.Email);  // this way you can access all the data from excel
 }

Send Email from Outlook account

This method is used to send email from your microsoft office account and it takes 3 arguments i.e email subject, email body and an optional attachement.(using c#)

public void SendEMail(string messagesubject, string messagebody, string screenshot = "")
 
 {
 try
 {
 MailMessage mesg = new MailMessage(); // MailMessage is a class inside System.Net.Mail
 foreach (string receipents in GetRecipients())
 {
 mesg.To.Add(new MailAddress(receipents));
 }
 // if (!String.IsNullOrEmpty(emailReceiver))
 // {
 // mesg.To.Add(new MailAddress(emailReceiver));
 // }
 mesg.From = new MailAddress("<yourMicrosoftUserName>", "Automation Report");
 mesg.Subject = messagesubject;
 mesg.Body = messagebody;
 mesg.IsBodyHtml = false;

if (!String.IsNullOrEmpty(screenshot))
 {
 Attachment attachment;
 attachment = new Attachment(screenshot);
 mesg.Attachments.Add(attachment);
 }

SmtpClient client = new SmtpClient(); // SmtpClient is a class inside System.Net.Mail
 client.TargetName = "STARTTLS/smtp.office365.com";
 client.UseDefaultCredentials = true;
 client.Credentials = new System.Net.NetworkCredential("<yourMicrosoftUserName>", "<yourMicrosofPassword");
 client.Port = 587;
 client.Host = "smtp.office365.com";
 client.DeliveryMethod = SmtpDeliveryMethod.Network;
 client.EnableSsl = true;

client.Send(mesg);
 }
 catch (Exception ex)
 {
 Console.WriteLine("Exception caugh while Sending e-mail. Details : {0}", ex.ToString());
 }
 }


// get list of recepients

private List<string> GetRecipients()
 {
 List<string> ListOfRecipients = new List<string>();
 try
 {
 
 string value1 = File.ReadAllText("C:\\myFile\\myFile.txt");
 
 var Recipients = value1.Replace(" ", "").Split(';');
 foreach (string Recipient in Recipients)
 {
 
 if (!String.IsNullOrWhiteSpace(Recipient))
 {
 ListOfRecipients.Add(Recipient);
 
 }
 }
 }
 catch{}
 return ListOfRecipients;
 }

Take screenshot of the webpage using Selenium C#

Following script is used to take screenshot of the webpage and save in your local drive. (using C#)

ITakesScreenshot screen = driver as ITakesScreenshot;  // ITakesScreenshot is  an interface inside OpenQA.Selenium namespace
Screenshot scrnst = screen.GetScreenshot();  // Screenshot is a class inside OpenQA.Selenium namespace
string screenshot = "c:\\screens\\myScreenShot1.png";
scrnst.SaveAsFile(screenshot, ImageFormat.Png);