Result in Excel at runtime when Testlists executed

[Deepak]

Hi,

It's urgent.
Can anyone gives a light on how to generate excel data file at runtime when your testlist gets executed.

Let say Testlist have 5 test cases. Once this gets executed test result.res file generate but i want an excel file which shows execution result.

Thanks in advance..

[Anthony]

Hello Deepak,

First, be aware that you can export test list results to an Excel file without code from the Results tab.

To proceed in code, use the Excel sample in the External Log File article and place it in the OnAfterTestListCompleted method in an Execution Extension. 

All the best,
Anthony
the Telerik team

[Deepak]

Hi Anthony,

Thanks for your reply.

I have applied the code you provided inside OnAfterTestResultCompleted, however the result is not in the form what i expected. I want the same format of that Excel file which is generated from test Studio after execution of testlist.

Moreover when we applied the code OnAfterTestListCompleted, we got an error message "ArtOfTest.WebAii.Design.Execution.TestResult" or "ArtOfTest.WebAii.Design.Execution.RunResult"name or assembly reference is missing.

and when I tried to incorporate OnAfterTestListCompleted method in an Execution Extension. methods gives me  attached error.


Thanks,
Deepak

[Deepak]

Hi Anthony,

I have removed all the compilation error and now i am getting below mentioned error log file generated in result:

------------------------------------------------------------
'6/21/2012 8:02:04 PM' - Using .Net Runtime version: '4.0.30319.1' for tests execution.
'6/21/2012 8:02:04 PM' - Starting execution....
'6/21/2012 8:02:06 PM' - Failure detected during execution. Details:
------------------------------------------------------------
'6/21/2012 8:02:06 PM' - System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ArtOfTest.Common.Design.Data.ExcelDataSource.GetData(DataInfo testBindingInfo)
   at ArtOfTest.WebAii.Design.Execution.DataContext.InitializeDataSource(String outDirectory)
   at ArtOfTest.WebAii.Design.Execution.ExecutionContext.SetCurrentTestAndData(Test test)
   at ArtOfTest.WebAii.Design.Execution.ExecutionEngine.InternalExecuteTest(Test test, TestResult initializationResult)
   at ArtOfTest.WebAii.Design.Execution.TestExecuteProxy.ExecuteTest(ExecuteTestCommand command)
------------------------------------------------------------
'6/21/2012 8:02:06 PM' - Test completed!

Kindly suggest.

Thanks,
Deepak

[Anthony]

Hello Deepak,

Does the Excel file exist on disk and is it in the correct format? The code shown in the External Log File article assumes that it is. The majority of that code is Microsoft-specific, so you may have luck searching that error in their forums.

If you continue to have difficulty, please paste all the code from your Execution Extension and we can assist you further.

All the best,
Anthony
the Telerik team

[Deepak]

 Hi Anthony,

I have pasted all the code from Execution Extension and created the Excel File and now I am not getting this error. Instead of this I am getting below mentioned error

------------------------------------------------------------
'6/22/2012 11:42:33 AM' - Using .Net Runtime version: '4.0.30319.1' for tests execution.
'6/22/2012 11:42:33 AM' - Starting execution....
'6/22/2012 11:42:33 AM' - Detected custom code in test. Locating test assembly: InProtech_automation.dll.
'6/22/2012 11:42:33 AM' - Assembly Found: C:\Documents and Settings\dejain\My Documents\Test Studio Projects\InProtech_Automation_Demo\bin\InProtech_automation.dll
'6/22/2012 11:42:33 AM' - Loading code class: ''.
'6/22/2012 11:42:33 AM' - Failure detected during execution. Details:
------------------------------------------------------------
'6/22/2012 11:42:33 AM' - System.ArgumentException: String cannot have zero length.
   at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly assembly, String name, Boolean throwOnError, Boolean ignoreCase, ObjectHandleOnStack type)
   at System.Reflection.RuntimeAssembly.GetType(String name, Boolean throwOnError, Boolean ignoreCase)
   at System.Reflection.Assembly.GetType(String name)
   at ArtOfTest.WebAii.Design.Execution.ExecutionUtils.EnsureTypeExists(Assembly assm, String typeName)
   at ArtOfTest.WebAii.Design.Execution.ExecutionUtils.CreateCodedTestInstance(Test test, TestResult result, String binariesFolder)
   at ArtOfTest.WebAii.Design.Execution.ExecutionEngine.InternalExecuteTest(Test test, TestResult initializationResult)
   at ArtOfTest.WebAii.Design.Execution.TestExecuteProxy.ExecuteTest(ExecuteTestCommand command)
------------------------------------------------------------
'6/22/2012 11:42:33 AM' - Test completed!

I searched on the google and found that this error is of because i have removed the inheritance Class "public class Report1 : BaseWebAiiTest, IExecutionExtension" to "public class Report1 : IExecutionExtension" and saw that my coded step removed
and so i changed the inheritance to "public class Report1 : BaseWebAiiTest, IExecutionExtension"

and now i tried to save and run this i am getting error message @ Code "OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);  

cmd.CommandType = CommandType.Text;" 
"error cs0104: 'CommandType' is an ambiguous reference between 'System.Data.CommandType' and 'ArtOfTest.WebAii.Design.CommandType'"

I am using below mentioned list of namespaces in order to run the code which is mentioned on Extension Execution

using Telerik.TestingFramework.Controls.KendoUI;
using Telerik.WebAii.Controls.Html;
using Telerik.WebAii.Controls.Xaml;
using System;
using System.IO;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using ArtOfTest.Common.UnitTesting;
using ArtOfTest.WebAii.Core;
using ArtOfTest.WebAii.Controls.HtmlControls;
using ArtOfTest.WebAii.Controls.HtmlControls.HtmlAsserts;
using ArtOfTest.WebAii.Design;
using ArtOfTest.WebAii.Design.Execution;
using ArtOfTest.WebAii.ObjectModel;
using ArtOfTest.WebAii.Silverlight;
using ArtOfTest.WebAii.Silverlight.UI;

Please guide me now how i can run this as there is ambiguous reference here. I am using the same code mentioned on Extension Execution.

Problem 2:
To run this code i need to place Excel file in the same format at location given in the method "System.Data.DataTable OnInitializeDataSource" ofd.Title = @"C:\OpenExcelFile.xls";

what if i need this file to create at runtime with the Excel FileName= filename+execution system date and time like filename=OpenExcelFile+Curent System Date+Current System Time.xls

Please help me out inorder to implement this.

[Anthony]

Hello Deepak,

I think we have a misunderstanding. I did not intend you to copy the code from the Execution Extension (EE) article and use it directly. I wanted you to copy the contents of your EE code and paste it here so I can inspect it for errors.

Let's start from scratch so we're on the same page:

1. Follow steps 1-5 in the EE article. You'll also need to add a reference to Microsoft.Office.Interop.Excel.
2. Insert the following code in the OnAfterTestListCompleted method. All other methods can be left alone:

 

public void OnAfterTestListCompleted(RunResult result)
{
    string passed = Convert.ToString(result.PassedCount);
    string failed = Convert.ToString(result.FailedCount);
    string overall = Convert.ToString(result.AllCount);

    string resultMessage = Convert.ToString(result.Summary);
    string[] split = resultMessage.Split(new Char[] { '\n' });

    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
    string myPath = @"C:\Temp\Excel.xlsx";
    Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(myPath);

    int idx = 1;
    foreach (string s in split)
    {
       excelApp.Cells[idx, 1] = s;
       idx++;
    }

    excelApp.Cells[idx + 1, 1] = ("Number of Passed Tests: " + passed);
    excelApp.Cells[idx + 2, 1] = ("Number of Failed Tests: " + failed);
    excelApp.Cells[idx + 3, 1] = ("Number of Total Tests: " + overall);
    excelApp.Visible = true;

    excelApp.ActiveWorkbook.Save();
    workbook.Close(false, Type.Missing, Type.Missing);
    excelApp.Workbooks.Close();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

    excelApp.Quit();
    GC.Collect();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}

 

3. Follow steps 7-9 in the EE article.

Whenever you make changes to the code in the EE, you must recompile the DLL and copy it back to the \Plugins folder (and overwrite).

As far as creating a unique Excel file with a time-stamped file name and getting that Excel into a specific format: we won't be able to provide additional assistance there. That's venturing into Microsoft code which is not Test Studio related.

All the best,
Anthony
the Telerik team

[Deepak]

Hi Anthony,

Please find my code below which i am using to generate result in excel file and also find attached screenshot of excel file what i am expecting to generate from this code this file has the same as generated from Telerik Test Result --> Save as Excel format

 

using Telerik.TestingFramework.Controls.KendoUI;
using Telerik.WebAii.Controls.Html;
using Telerik.WebAii.Controls.Xaml;
using System;
using System.IO;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using ArtOfTest.Common.UnitTesting;
using ArtOfTest.WebAii.Core;
using ArtOfTest.WebAii.Controls.HtmlControls;
using ArtOfTest.WebAii.Controls.HtmlControls.HtmlAsserts;
using ArtOfTest.WebAii.Design;
using ArtOfTest.WebAii.Design.Execution;
using ArtOfTest.WebAii.ObjectModel;
using ArtOfTest.WebAii.Silverlight;
using ArtOfTest.WebAii.Silverlight.UI;

namespace InProtech_automation
{

    //
    // You can add custom execution steps by simply
    // adding a void function and decorating it with the [CodedStep]
    // attribute to the test method.
    // Those steps will automatically show up in the test steps on save.
    //
    // The BaseWebAiiTest exposes all key objects that you can use
    // to access the current testcase context. [i.e. ActiveBrowser, Find ..etc]
    //
    // Data driven tests can use the Data[columnIndex] or Data["columnName"]
    // to access data for a specific data iteration.
    //
    // Example:
    //
    // [CodedStep("MyCustom Step Description")]
    // public void MyCustomStep()
    // {
    //        // Custom code goes here
    //      ActiveBrowser.NavigateTo("http://www.google.com");
    //
    //        // Or
    //        ActiveBrowser.NavigateTo(Data["url"]);
    // }
    //
       

    public class Report1 : BaseWebAiiTest, IExecutionExtension
    {
        #region [ Dynamic Pages Reference ]

        private Pages _pages;
       
        /// <summary>
        /// Gets the Pages object that has references
        /// to all the elements, frames or regions
        /// in this project.
        /// </summary>
        public Pages Pages
        {
            get
            {
                if (_pages == null)
                {
                    _pages = new Pages(Manager.Current);
                }
                return _pages;
            }
        }

        #endregion
       
       
        // Add your test methods here...
       
        public void OnAfterTestCompleted(ExecutionContext executionContext, TestResult result)
        {
            //throw new NotImplementedException();
        }
 
 
        // After the test list is completed.
        // <param name="result">The entire RunResult object.</param>
 
        public void OnAfterTestListCompleted(RunResult result)
        {
          //throw new NotImplementedException();
        }
 
 
        // Before a test list is about to start execution.
        // <param name="list">The test list that is about to start.</param>
 
        public void OnBeforeTestListStarted(TestList list)
        {  
           //throw new NotImplementedException();
           
        }
 
 
        // Before a test is about to start.
        // <param name="executionContext">The execution context the test is running under.</param>
        // <param name="test">The test we are about to start running.</param>
       
 
        // Will be called when the test is data bound. Use it to return your own data source.
        // <param name="executionContext">The execution context.</param>
       
 
 
        // Called only on a step failure.
        // <param name="executionContext">The execution context a test is running under.</param>
        // <param name="stepResult">The step result that just failed.</param>
 
    public void OnBeforeTestStarted(ExecutionContext executionContext, ArtOfTest.WebAii.Design.ProjectModel.Test test)
        {
         //throw new NotImplementedException();
           
        }
public void OnStepFailure(ExecutionContext executionContext, ArtOfTest.WebAii.Design.AutomationStepResult stepResult)
        {
            //throw new NotImplementedException();
        }
 
       
public System.Data.DataTable OnInitializeDataSource(ExecutionContext executionContext)
{
    System.Data.DataTable table = null;
    var thread = new System.Threading.Thread(obj =>
    {
        try
        {
            System.Windows.Forms.OpenFileDialog ofd = new System.Windows.Forms.OpenFileDialog();
            ofd.Title = @"C:\OpenExcelFile.xls";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string excel = ofd.FileName;
                DataSet foo = ImportExcelXLS(excel, true);
                table = (foo.Tables[0]);
            }
        }
        catch (Exception ex)
        {
            NativeWindow a = new NativeWindow();
            a.AssignHandle(ArtOfTest.WebAii.Core.Manager.Current.ActiveBrowser.Window.Handle);
            MessageBox.Show(a, ex.Message);
        }
    });
    thread.SetApartmentState(System.Threading.ApartmentState.STA);
    thread.Start();
    thread.Join();
    return table;
}
private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn = null;
    if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
    {
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=1\"";
    }
    else
    {
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
    }
 
    DataSet output = new DataSet();
 
    using (OleDbConnection conn = new OleDbConnection(strConn))
    {
        conn.Open();
 
        DataTable schemaTable = conn.GetOleDbSchemaTable(
          OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
 
        foreach (DataRow schemaRow in schemaTable.Rows)
        {
            string sheet = schemaRow["TABLE_NAME"].ToString();

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
            cmd.CommandType = CommandType.Text;
 
            DataTable outputTable = new DataTable(sheet);
            output.Tables.Add(outputTable);
            new OleDbDataAdapter(cmd).Fill(outputTable);
        }
    }
    return output;
}
        [CodedStep (@"Wait for '250' msec.")]
        public void New_Coded_Step()
        {
            // Wait for '250' msec.
            System.Threading.Thread.Sleep(250);
        }
   
       
    }
}
   
       
   
       
   

 

 

 

 

[Anthony]

Hello Deepak,

There's still some confusion. The Execution Extension code does not go into a coded step or the code-behind directly in a test. Please re-read the Execution Extension article and the steps laid out in my previous post. You need to create a Class Library project in Visual Studio and add the sample code I provided to the class file under the correct method. The compiled DLL gets copied to the Test Studio\Bin\Plugins folder, which signals Test Studio to execute that code every time after a test list executes.

The code in the External Log File article is meant to go into the code-behind for an individual test, but it only affects that test. Since you want an Excel report for a test list, the Execution Extension is appropriate as it affects execution on a project level.

So, delete the existing code from your individual test and follow the instructions in my previous post to achieve your goal.

Greetings,
Anthony
the Telerik team

[Deepak]

Hi Anthony,

I tried with the steps you mentioned and found that after execution of test lists no result data found in excel result sheet.

Thanks,
Deepak

[Anthony]

Hello Deepak,

Please place your Test Studio project folder and your Visual Studio Class Library project folder into a zip file and attach them here. If those files are too sensitive for a public forum, create a new support ticket (which is confidential), attach it there, and reference this thread. 

I'll load your projects locally and troubleshoot them.

All the best,
Anthony
the Telerik team

[Sam]

Anthony,

I have a problem just getting the generic Export Test Results. I copied and pasted the format into a coded step, then exported it into Visual Studio and I get the following error **click here**.

001.using Telerik.TestingFramework.Controls.KendoUI;//File access
002.using Telerik.WebAii.Controls.Html;//File access
003.using Telerik.WebAii.Controls.Xaml;//File access
004.using System;//File access
005.using System.Collections.Generic;//File access
006.using System.Text;//File access
007.using System.Linq;//File access
008.
009.using ArtOfTest.Common.UnitTesting;//File access
010.using ArtOfTest.WebAii.Core;//File access
011.using ArtOfTest.WebAii.Controls.HtmlControls;//File access
012.using ArtOfTest.WebAii.Controls.HtmlControls.HtmlAsserts;//File access
013.using ArtOfTest.WebAii.Design;//Directly Correlates Print File Access
014.using ArtOfTest.WebAii.Design.Execution;//Directly Correlates Print File Access
015.using ArtOfTest.WebAii.ObjectModel;//Directly Correlates Print File Access
016.using ArtOfTest.WebAii.Silverlight;//Unused presently...Silverlight is not installed to Test Studio
017.using ArtOfTest.WebAii.Silverlight.UI;//Unused presently...Silverlight is not installed to Test Studio
018.using ArtOfTest.WebAii.Design.AutomationStepResult; //Included to run the reporting module.
019.
020.
021.namespace Retest_Ilaris_PLEASE_DELETE
022.{
023.
024.    //
025.    // You can add custom execution steps by simply
026.    // adding a void function and decorating it with the [CodedStep]
027.    // attribute to the test method.
028.    // Those steps will automatically show up in the test steps on save.
029.    //
030.    // The BaseWebAiiTest exposes all key objects that you can use
031.    // to access the current testcase context. [i.e. ActiveBrowser, Find ..etc]
032.    //
033.    // Data driven tests can use the Data[columnIndex] or Data["columnName"]
034.    // to access data for a specific data iteration.
035.    //
036.    // Example:
037.    //
038.    // [CodedStep("MyCustom Step Description")]
039.    // public void MyCustomStep()
040.    // {
041.    //        // Custom code goes here
042.    //      ActiveBrowser.NavigateTo("http://www.google.com");
043.    //
044.    //        // Or
045.    //        ActiveBrowser.NavigateTo(Data["url"]);
046.    // }
047.    //
048.
049.
050.    public class Ilaris_ePrescription_Existing_Patient_w_primary_and_secondary_insurance__test : BaseWebAiiTest
051.    {
052.        #region [ Dynamic Pages Reference ]
053.
054.        private Pages _pages;
055.
056.        /// <summary>
057.        /// Gets the Pages object that has references
058.        /// to all the elements, frames or regions
059.        /// in this project.
060.        /// </summary>
061.        public Pages Pages
062.        {
063.            get
064.            {
065.                if (_pages == null)
066.                {
067.                    _pages = new Pages(Manager.Current);
068.                }
069.                return _pages;
070.            }
071.        }
072.
073.        #endregion
074.
075.        // Add your test methods here...
076.
077.        [CodedStep(@"New Coded Step")]
078.        public void Ilaris_ePrescription_Existing_Patient_w_primary_and_secondary_insurance_test_CodedStep()
079.        {
080.
081.            //Get/Set Extracted Variables in Code
082.            //PROBLEM
083.            //I extracted a variable in a test step. I would like to do something with that data in a coded step.
084.            //SOLUTION
085.            //Use the GetExtractedValue() function:
086.            //object myData = GetExtractedValue("varname");
087.            //------------------------------------------------------------------------------------------------------------------
088.            //PROBLEM
089.            //How can I set the value of an extraction variable so it can be used later in a standard action or verification step?
090.            //SOLUTION
091.            //Use the SetExtractedValue() function:
092.            //string area = "Atlanta";
093.            //SetExtractedValue("extractedID", area);
094.
095.
096.            var step_data = GetExtractedValue("rx_id");
097.            //var step_data
098.
099.        }
100.
101.        [CodedStep(@"New Coded Step")]
102.        public void Ilaris_ePrescription_Existing_Patient_w_primary_and_secondary_insurance_test_CodedStep1()
103.        {
104.            public IList<AutomationStepResult> stepResults { get; set; }
105.            public override void OnAfterTestCompleted(TestResult result)
106.            {
107.                stepResults = result.StepResults;
108.
109.                string passed = Convert.ToString(result.TotalPassedSteps);
110.                string notRunSteps = Convert.ToString(result.TotalNumberOfNotRunSteps);
111.                string overall = Convert.ToString(result.Result);
112.                string resultMessage = Convert.ToString(result.Message);
113.
114.                System.IO.StreamWriter file = new System.IO.StreamWriter("c:\\source.txt");   //Source can be changed to ‘Results’ or something…
115.
116.                file.WriteLine(resultMessage);
117.                file.WriteLine("Number of Passed Steps:"+ passed);
118.                file.WriteLine("Number of Not Run Steps: "+ notRunSteps);
119.                file.WriteLine("Total Test Result: "+ overall);
120.                file.Close();
121.
122.            }
123.        }
124.    }
125.}

[Boyan Boev]

Hello,

Thank you for contacting Telerik support.

Please remove the last bracket and give it a try.

Hope this helps.

Regards,
Boyan Boev
Telerik