locked
excel datasource for coded ui test RRS feed

  • Question

  • I would like to use an excel spreadsheet as a datasource for my coded ui tests.

    However I want to have one spreadsheet per application screen, so that I don't have to put all the controls inside the same spreadsheet.

    The problem is that it seems the datasource has to come be a specific sheet in the excel file. It doesn't seem possible to specify multiple sheets. Or is it ?


    Sam

    Friday, November 16, 2012 3:56 PM

Answers

  • Can you avoid the UIMaps completely as far as the searching goes? You might add the form name or the window name into the XML or the spreadsheet, or perhaps they are fixed and known in advance. Then make the parser use these names to start searching from a higher level in the structure of elements on the screen.

    You can call SearchProperties.Add and FilterProperties.Add several times to add multiple search criteria and you can start the search at the desktop rather than at a place identified in the UI map. So you could add the form name or the window name into the search before the call to Find or FindMatchingControls.

    Regards

    Adrian

    • Marked as answer by graphicsxp Monday, November 26, 2012 8:44 AM
    Wednesday, November 21, 2012 9:42 AM

All replies

  • Assuming each of your application screens has its own test method having both the [DataSource(...)] and [TestMethod] attributes. Then, to drive each of these tests via a different sheet within a spreadsheet try the following:

    In VS2010 find the "Test View" window and then view the properties page of the test. In the properties page, the "Data Table Name" field names the sheet within the ".xls" or ".xlsx" file. Perhaps simpler, find the "[DataSource(...)]" attribute just above the test method. One of the parameters in that line names the sheet.

    My test using "sheet2" has (with line breaks added to make it readable):

    [DataSource("System.Data.Odbc",

    "Dsn=Excel Files;dbq=|DataDirectory|\\threads.xlsx;

    defaultdir=C:\\Users\\AdrianHHH\\Documents\\Files;

    driverid=1046;maxbuffersize=2048;pagetimeout=5", "Sheet2$",

    DataAccessMethod.Sequential), DeploymentItem("threads.xlsx"), TestMethod]

    If by spreadsheet you mean CSV file then you will need different files and a different DataSource(...) for each test.

    Regards

    Adrian

    Friday, November 16, 2012 5:11 PM
  • Hi Adrian,

    Thanks for the reply. Here's the thing: I have multiple screens per test, hence multiple excel sheets per test.

    Therefore, I don't know what can be done to bind my test to multiple sheets.

    Any idea ?


    Sam

    Monday, November 19, 2012 6:40 AM
  • Hi Sam,

    I think we need to know more about your application and your tests. What kind of data would you want in each sheet? Can you expand on the phrases "one spreadsheet per application screen" and "all the controls inside the same spreadsheet" from your question? Will each screen have its own Coded UI test or will one test visit many screens?

    Regards

    Adrian

    Monday, November 19, 2012 11:25 AM
  • I'd like to bind my Coded UI test to an excel workbook.

    One coded UI test can indeed visit several screens. So I'm not sure what's the best approach for binding the test to an excel file ?

    Should I have one single spreadsheet for all of the controls in the application, or multiple spreadsheets, each containing the controls for a different screen ?

    Regardless of the approach, a spreadsheet should contain the following:

    Headers should contain the id's of the controls on an application screen. Rows below the header should contain the data that goes in the controls, with each row being a different test.


    Sam

    Monday, November 19, 2012 3:27 PM
  • Helllo Sam,

    According to your description, I feel that you want to bind a data for all controls in every application UI. Am I right? If yes, I would like to know why you do like that. Generally we bind specified data for some input or edit controls so that you can run your coded UI test multiple times with different sets of data to test different conditions. As my understanding, we can’t bind data for all kinds of controls. And I suggest that you can bind data source for the required controls.

    In addition, I don’t understand why you define control ID in Excel. Do you want to find the corresponding control according to the ID and then specify the corresponding data to the control?

    I don’t think that you can set data source like that and get data from it. Generally we create a data source like that and access the data through TestContext.DataRow["Input1"].ToString();:

    Input1     Input2   ExpectedResult …

    3                 4              7

    5               6            11

    If you set data source like that, I don’t think that you can access the data normally.

    For detailed information, you can refer to this article:

    How to: Create a Data-Driven Coded UI Test

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, November 20, 2012 2:35 AM
    Moderator
  • Hello Sam,

    What about your issue now? Could you get useful information from our reply?

    Would you mind letting us know the result of the suggestion?

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, November 21, 2012 8:48 AM
    Moderator
  • Name

    Owner
           Rate
          Search
    Smyth 4800 12    
    1

    Simpson
    4801 23

    Above is an example of a spreadsheet data, with the first row being the name of my column and next rows being the values. Each row is a unique test.

    Since I don't use classic databinding as I don't know in advance the type of control I'm going to have on my web form, I've used an xml file to describe the controls:

    <UIControls>
      <UIControl  ExcelMapping="Name" ClientControlId="ctl00_ContentPlaceHolder_txtName" ControlType="TextBox" />
      <UIControl  ExcelMapping="Owner" ClientControlId="ctl00_ContentPlaceHolder_txtOwner" ControlType="HyperLink" />
      <UIControl PageName="/Propositions/Propositions.aspx" ExcelMapping="Camionnettes" ClientControlId="ctl00_ContentPlaceHolder_offre_Camionnettes" ControlType="HyperLink" />


    Sam

    Wednesday, November 21, 2012 8:56 AM


  • Name      Owner            Rate           Search
    Smyth     4800               12              1           
    Simpson     4801            23               1

    Above is an example of a spreadsheet data, with the first row being the name of my column and next rows being the values. Each row is a unique test.

    Since I don't use classic databinding as I don't know in advance the type of control I'm going to have on my web form, I've used an xml file to describe the controls:

    <UIControls>
      <UIControl  ExcelMapping="Name" ClientControlId="ctl00_ContentPlaceHolder_txtName" ControlType="TextBox" />
      <UIControl  ExcelMapping="Owner" ClientControlId="ctl00_ContentPlaceHolder_txtOwner" ControlType="TextBox" />
      <UIControl  ExcelMapping="Rate" ClientControlId="ctl00_ContentPlaceHolder_chkRate" ControlType="CheckBox" />
      <UIControl  ExcelMapping="Search" ClientControlId="ctl00_ContentPlaceHolder_btnSearch" ControlType="Button" />
    <UIControls>

    Then I've created a parser that searches for the controls on the webform and set their values according to the data on the spreadsheet.

    That works well, but if my test case spreads across several screen, then I need to change dynamically the UIMap to which my parse is bound to, otherwise the controls won't be found (the UIMap knows only about one HtmlDocument.

    Is it clearer what I'm trying to achieve ?

    Sam

    Wednesday, November 21, 2012 9:07 AM
  • Can you avoid the UIMaps completely as far as the searching goes? You might add the form name or the window name into the XML or the spreadsheet, or perhaps they are fixed and known in advance. Then make the parser use these names to start searching from a higher level in the structure of elements on the screen.

    You can call SearchProperties.Add and FilterProperties.Add several times to add multiple search criteria and you can start the search at the desktop rather than at a place identified in the UI map. So you could add the form name or the window name into the search before the call to Find or FindMatchingControls.

    Regards

    Adrian

    • Marked as answer by graphicsxp Monday, November 26, 2012 8:44 AM
    Wednesday, November 21, 2012 9:42 AM
  • So you are suggesting I move the code that is in the UIMap designer file to my parser class, so that I dynamically build the htmlDocument based on parameters in the xml ?

    that sounds good, i'll give it a try.


      


    Sam

    Wednesday, November 21, 2012 9:47 AM
  • Hello Sam,

    What about your issue now? Could you get useful information from Adrian's reply? Would you mind letting us know the result of the suggestion?

    I think that his suggestion is useful. You can try to add the window name in the XML and then let the parser to search from the specified screen when test case spreads across different screens to find corresponding controls.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, November 23, 2012 1:38 AM
    Moderator
  • Hello Sam,

    Have you resolved this issue? Could you get useful information from our reply?

    If you have resolved it, please mark the helpful reply as answer.

    If not, please let us know the latest news about this issue and the result of our suggestion.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, November 26, 2012 8:12 AM
    Moderator
  • Hello,

    Sorry for the late reply, but I needed some time to put all the pieces together.

    Yes, it is working now. I got rid of the uimap altogether.  And I've created a class in my test framework that creates a html document based on the search parameters defined from the xml file. The browser window is then found, as well as the different controls on the page.

    thanks for helping


    Sam

    Monday, November 26, 2012 8:43 AM