[HOW-TO] <implement read data from excel in easyrepro>

[meeta0311]

Question 1

We have a requirement in which data should be read from an excel file to make it data driven for a third party portal by using easyrepro framework. We need to read data from excel file and it should fill up information on a third party portal other than dynamics 365 using easyrepro framework.

[tipsey]

You'll need to leverage native C# and Nuget packages. Check out "SpreadsheetLight". I used version 3.4.11 with success:

using System.IO; using SpreadsheetLight;

Example:

[TestClass]
public class ReadExcel
{
    [TestMethod]
    public void ExcelReader()
    {
        int row = 0; //To Skip first row header in Excel
        int failed = 0;

        string exeDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) +"\\Data";
        using (SLDocument sl = new SLDocument())
        {

            //Locate ExcelLogins.xlsx and Open
            string file = System.IO.Path.Combine(exeDir, "ExcelLogins.xlsx");
            Console.WriteLine(file);
            FileStream fs = new FileStream(file, FileMode.Open);
            SLDocument sheet = new SLDocument(fs, "Sheet1"); //Name of worksheet always has to be default Sheet1

            SLWorksheetStatistics stats = sheet.GetWorksheetStatistics();

            for (int j = 1; j < stats.EndRowIndex + 1; j++)
            {

                //Disregard the Title Row this is
                row++;
                if (row == 1)
                {
                    continue;
                }
                // Get the first column of the row (SLS is a 1-based index)

                //0 j = RowCounter, 1 = Column 1, 2 = Column 2 etc

                var Email = sheet.GetCellValueAsString(j, 1);
                var Password = sheet.GetCellValueAsString(j, 2);

                //SETUP BROWSER
                Browser.InitBrowser(System.Configuration.ConfigurationManager.AppSettings["BrowserType"]);
                Browser.Driver.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(Timeout);
                var driver = Browser.Driver;

                try
                {
                    driver.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(Timeout);
                    driver.Manage().Window.Maximize();
                    driver.Navigate().GoToUrl("https://pathtoLoginPage");
                    //Login in English
                    // MainPage.Btn_English(driver).Click();
                    LoginPage.Txtbx_Username(driver).Clear();
                    LoginPage.Txtbx_Username(driver).SendKeys(Email);
                    LoginPage.Txtbx_Password(driver).Clear();
                    LoginPage.Txtbx_Password(driver).SendKeys(Password);
                    LoginPage.Btn_Login(driver).Click();

                    //TEST TO SEE IF LOGIN SUCCESSFULLY

                    try
                    {
                        //Click something on page to determine success
                        LoginPage(driver).Click();
                        Console.WriteLine("Login Successful");
                    }
                    catch (Exception)
                    {
                        //BUG Not catching last row exception: make sure last row is valid?

                        failed = failed + 1;
                        Console.WriteLine("FAILED Login: " + Email + " " + Password);
                        continue;
                    }

                    Browser.CloseAllDrivers();
                }

                catch (Exception)
                {
                    Console.WriteLine("Problem with " + Email + " " + Password);
                    if (driver != null)
                        driver.Quit();
                    //When last row get test results
                    if (j == stats.EndRowIndex + 1)
                    {
                        Console.WriteLine("Number of Emails Tested: " + j);
                        Console.WriteLine("Number of Emails Failed: " + failed);
                    }

                    continue;
                }

                //Close any Browser
                if (driver != null)
                    driver.Quit();

                //When last row get test results
                if (j >= stats.EndRowIndex - 1)
                {
                    Console.WriteLine("Number of Emails Tested: " + j);
                    //Console.WriteLine("Number of Emails Failed Not Including Last Row: " + failed);
                }
            }        

        }

        Console.WriteLine("Number of Emails Failed: " + failed);
        Browser.CloseAllDrivers();
    }  
}

}

[meeta0311]

Thankyou so much.. I will try this method.