Reading Excel file with Selenium WebDriver C# and web form with values

Question

I have a Selenium WebDriver script written in C# that reads some values from an Excel spreadsheet and then uses the values from the row to fill a web form. The challenge I have now is that it takes the first cell value from the Excel file and enters it to all the fields in the form, then takes the next value and does the same and so on.

How can I make it such that it takes the 1st value, add to the first(named field) in the form, takes 2nd value and to 2nd named field and so on.

Please see code for method below.

public void FillForm()
//Function reads entries from an Excel spreadsheet then uses values to populate and fill the form
{

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    Excel.Range xlrange;

    string xlString;
    int xlRowCnt = 0;
    int xlColCnt = 0;

    xlApp = new Excel.Application();
    //Open Excel file
    xlWorkBook = xlApp.Workbooks.Open(@"D:\Projects\Data\MSI_Data_file.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    //This gives the used cells in the sheet

    xlrange = xlWorkSheet.UsedRange;


    for (xlRowCnt = 1; xlRowCnt <= xlrange.Rows.Count; xlRowCnt++)
    {
        for (xlColCnt = 1; xlColCnt <= xlrange.Columns.Count; xlColCnt++)
        {
            xlString = (string)(xlrange.Cells[xlRowCnt, xlColCnt] as Excel.Range).Value2;
            driver.FindElement(By.XPath("//input[contains(@name, 'FirmName')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'FirstName')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'LastName')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'Email')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'FirmAddress')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//select[@id= 'ddlCountry']")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'PhoneNumber')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'FaxNumber')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[contains(@name, 'Website')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//textarea[contains(@name, 'Comments')]")).SendKeys(xlString);
            driver.FindElement(By.XPath("//input[@id='chkFirm_Service_Accounting']")).Click();
            driver.FindElement(By.XPath("//select[contains(@name, 'LeadSource')]")).SendKeys(xlString);

            //save screenshot of completed form
            SaveScreenShot("CompleteForm");

            driver.FindElement(By.XPath("//a[contains(text(), 'Submit')]")).Click();

            //Take screenshot of successful form submission
            SaveScreenShot("Submission_Success");


            driver.FindElement(By.XPath("//a[contains(text(), 'click here')]")).Click();

        }
    }
}

Answer 1

You should handle each column one at a time to do a FindElement. I have adapted your code and made it a bit more flexible.

First you notice I generalized the XPath expression you used by introducing a lamba function for each:

Func<string,string> inputName = (id) => String.Format("//input[contains(@name, '{0}')]", id);

We can then later reuse these when we are mapping the columns to the right XPath. As you see in the final code I have 5 of those.

Next I added a simple List<string> to hold for each column in your Excel which field is to be used:

var fields = new List<string> {
    inputName("FirmName"),
    inputName("FirstName"),
    // many more
 }

So the first column will match with your input element on your webform called FirmName, the second column will match with Firstname etc.

Notice that it wouldn't be too hard to achieve this mapping in your excel sheet so this code then be further generalized but I leave that as an exercise for the reader.

Once we have our fields we can simply obtain for each column which XPath to use by doing a simple call to in indexer of the List<string>:

// this will give you the correct XPath
var xpath = fields[xlColCnt -1]; 

The only caveat is that in Excel the columns start at 1 while in C# in general indexes are 0 based, hence the -1.

Putting it all together will give you this final implementation:

public void FillForm()
//Function reads entries from an Excel spreadsheet then uses values to populate and fill the form
{

    string xlString;
    int xlRowCnt = 0;
    int xlColCnt = 0;

    var xlApp = new Application();
    //Open Excel file
    var xlWorkBook = xlApp.Workbooks.Open(@"MSI_Data_file.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

    //This gives the used cells in the sheet

    var xlrange = xlWorkSheet.UsedRange;

    // XPath formatters for each 
    Func<string,string> inputName = (id) => String.Format("//input[contains(@name, '{0}')]", id);
    Func<string, string> inputId = (id) => String.Format("//input[@id='{0}']", id);
    Func <string, string> selectId = (id) => String.Format("//select[@id= '{0}']", id);
    Func<string, string> selectName = (id) => String.Format("//select[contains(@name, '{0}')]", id);
    Func<string, string> textareaName = (id) => String.Format("//textarea[contains(@name, '{0}')]", id);

    // map a fieldname to an Xpath formatter
    // Order of the fields is important!
    var fields = new List<string> {
        inputName("FirmName"),
        inputName("FirstName"),
        inputName("LastName"),
        inputName("Email"),
        inputName("FirmAddress"),
        selectId("ddlCountry"),
        inputName("PhoneNumber"),
        inputName("FaxNumber"),
        inputName("Website"),
        textareaName("Comments"),
        inputId("chkFirm_Service_Accounting"),
        selectName("LeadSource"),
    };
    // handle all rows
    for (xlRowCnt = 1; xlRowCnt <= xlrange.Rows.Count; xlRowCnt++)
    {
        // handle each column, based on the column number
        for (xlColCnt = 1; xlColCnt <= xlrange.Columns.Count; xlColCnt++)
        {
            var value = xlrange.Cells[xlRowCnt, xlColCnt].Value2;
            // if value contains a double or a date, this will still work
            xlString = (value ?? "").ToString();

            // this will give you the correct XPath
            var xpath = fields[xlColCnt -1]; 
            // find 
            driver.FindElement(By.XPath(xpath)).SendKeys(xlString);
        }

        //save screenshot of completed form
        SaveScreenShot("CompleteForm");
        driver.FindElement(By.XPath("//a[contains(text(), 'Submit')]")).Click();
        //Take screenshot of successful form submission
        SaveScreenShot("Submission_Success");
        driver.FindElement(By.XPath("//a[contains(text(), 'click here')]")).Click();
    }
}