Read data from excel

Answered

Hi all;
If anyone can please help me.I want to read some data from an excel file in selenium web driver.I can do it by using java but at the moment I want to do it in C#.

Regards.

Train Asked on September 2, 2014 in Selenium WebDriver.
Add Comment
  • 3 Answer(s)
    Best answer

    Hi Priya,

    Probably you are looking for something like this. But this cannot be the complete code you are looking for, you have to learn and build up on this

     

    
    public void WriteToExcel()
    
    {
    
    string myPath = @"C:\Excel.xls"; // this must be full path.
    
    FileInfo fi = new FileInfo(myPath);
    
    if (!fi.Exists)
    
    {
    
    Console.Out.WriteLine("file doesn't exists!");
    
    }
    
    else
    
    {
    
    var excelApp = new Microsoft.Office.Interop.Excel.Application();
    
    var workbook = excelApp.Workbooks.Open(myPath);
    
    Worksheet worksheet = workbook.ActiveSheet as Worksheet;
    
    Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[1,1] as Range;
    
    range.Value2 = "5";
    //excelApp.Visible = true;
    
    workbook.Save();
    
    workbook.Close();
    
    }
    
    }
    
    

    Thanks
    Virender 

    Professor Answered on September 2, 2014.
    Add Comment

    Hi Priya,

    You have to reference Microsoft Excel Object Library in your project to get hold of Excel object model. You can do this by clicking  Add Reference and in the pop up window by selecting COM (Component Object model). This will ist down all the libraries that are available on you system. Find Excel object library and you are good to go.

    Here is a code sample which performs some of the excel functions

    #region Using directives
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.IO;
    using System.Reflection;

    using Excel = Microsoft.Office.Interop.Excel;
    using System.Runtime.InteropServices;  #endregion

    namespace CSAutomateExcel
    {
    static class Solution1
    {
    public static void AutomateExcel()
    {
    object missing = Type.Missing;
    Excel.Application oXL = null;
    Excel.Workbooks oWBs = null;
    Excel.Workbook oWB = null;
    Excel.Worksheet oSheet = null;
    Excel.Range oCells = null;
    Excel.Range oRng1 = null;
    Excel.Range oRng2 = null;

    try
    {
    // Create an instance of Microsoft Excel and make it invisible.

    oXL = new Excel.Application();
    oXL.Visible = false;
    Console.WriteLine(“Excel.Application is started”);

    // Create a new Workbook.

    oWBs = oXL.Workbooks;
    oWB = oWBs.Add(missing);
    Console.WriteLine(“A new workbook is created”);

    // Get the active Worksheet and set its name.
    oSheet = oWB.ActiveSheet as Excel.Worksheet;
    oSheet.Name = “Report”;
    Console.WriteLine(“The active worksheet is renamed as Report”);

    // Fill data into the worksheet’s cells.

    Console.WriteLine(“Filling data into the worksheet …”);

    // Set the column header
    oCells = oSheet.Cells;
    oCells[11] = “First Name”;
    oCells[12] = “Last Name”;
    oCells[13] = “Full Name”;

    // Construct an array of user names
    string[,] saNames = new string[,] {
    {“John”“Smith”},
    {“Tom”“Brown”},
    {“Sue”“Thomas”},
    {“Jane”“Jones”},
    {“Adam”“Johnson”}};

    // Fill A2:B6 with an array of values (First and Last Names).
    oRng1 = oSheet.get_Range(“A2”“B6”);
    oRng1.Value2 = saNames;

    // Fill C2:C6 with a relative formula (=A2 & ” ” & B2).
    oRng2 = oSheet.get_Range(“C2”“C6”);
    oRng2.Formula = “=A2 & \” \” & B2″;

    // Save the workbook as a xlsx file and close it.

    Console.WriteLine(“Save and close the workbook”);

    string fileName = Path.GetDirectoryName(
    Assembly.GetExecutingAssembly().Location) + “\\Sample1.xlsx”;
    oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
    missing, missing, missing, missing,
    Excel.XlSaveAsAccessMode.xlNoChange,
    missing, missing, missing, missing, missing);
    oWB.Close(missing, missing, missing);

    // Quit the Excel application.

    Console.WriteLine(“Quit the Excel application”);

    // Excel will stick around after Quit if it is not under user 
    // control and there are outstanding references. When Excel is 
    // started or attached programmatically and 
    // Application.Visible = false, Application.UserControl is false. 
    // The UserControl property can be explicitly set to True which 
    // should force the application to terminate when Quit is called, 
    // regardless of outstanding references.
    oXL.UserControl = true;

    oXL.Quit();
    }
    catch (Exception ex)
    {
    Console.WriteLine(“Solution1.AutomateExcel throws the error: {0}”,
    ex.Message);
    }
    finally
    {
    // Clean up the unmanaged Excel COM resources by explicitly 
    // calling Marshal.FinalReleaseComObject on all accessor objects. 
    // See http://support.microsoft.com/kb/317109.

    if (oRng2 != null)
    {
    Marshal.FinalReleaseComObject(oRng2);
    oRng2 = null;
    }
    if (oRng1 != null)
    {
    Marshal.FinalReleaseComObject(oRng1);
    oRng1 = null;
    }
    if (oCells != null)
    {
    Marshal.FinalReleaseComObject(oCells);
    oCells = null;
    }
    if (oSheet != null)
    {
    Marshal.FinalReleaseComObject(oSheet);
    oSheet = null;
    }
    if (oWB != null)
    {
    Marshal.FinalReleaseComObject(oWB);
    oWB = null;
    }
    if (oWBs != null)
    {
    Marshal.FinalReleaseComObject(oWBs);
    oWBs = null;
    }
    if (oXL != null)
    {
    Marshal.FinalReleaseComObject(oXL);
    oXL = null;
    }
    }
    }
    }
    }

    Thanks
    Virender

    Professor Answered on September 2, 2014.
    Add Comment

    Thanks Virender for your quick response.
    Actually I want to automate a login page and for that I want to get username and password from an excel sheet.
    I have to automate an e-com website and it’s same as your example “Hybrid Automation Framework”.It’s little bit hard to me because I have to do it using C# with nunit. I’m familiar with selenium with java but completely new to C# .So would be much thankful to you if you can provide me a sample code for this.

    Regards.

    Train Answered on September 2, 2014.
    Add Comment
  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.