Home » C# scripts

Category Archives: C# scripts

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); 
 }

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
     }
     }

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
 }