代码之家 › 专栏 › 技术社区 › G. Dan

Selenium C#从Excel读取数据进行数据驱动测试时出错

data-driven-tests c#-4.0 selenium-webdriver selenium c#

G. Dan · 技术社区 · 6 年前

我正在尝试通过从Excel文件加载客户端代码来执行数据驱动测试,以登录并执行其他操作。我正在尝试对client\u code列中的所有客户端重复测试。我只需要读取client\u code列并获取数据,以便对这些客户端重复相同的测试。当我运行下面的代码时,得到的值为空

如果您有任何帮助,我们将不胜感激。谢谢。

我正在使用ExcelDataReader v3。4.0,ExcelDataReader。数据集v3。4.0,selenium Webdriver v3。11

我的Excel表如下所示。

client_code		client_name
11111			Client1
22222			Client2
33333			Client3
44444			Client4
55555			Client5

这是我的代码:

ExcelUtil。cs公司

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.Remoting.Messaging;
using System.Text;
using System.Threading.Tasks;


namespace MyDemoAutomation
    {
    public class ExcelUtil
        {
        public DataTable ExcelToDatable(string fileName)
            {
            // open file and returns as stream
            FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
            // create openXmlReader via ExcelReaderFactory
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //Set the first row as column name
          var result1 = excelReader.AsDataSet(new ExcelDataSetConfiguration()
                {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                    {
                    UseHeaderRow = true
                    }
                });
     
            // Return as dataset
            DataSet result = excelReader.AsDataSet();
            // Get all tables
            DataTableCollection table = result.Tables;
            // Store in Database
            DataTable resultTable = table["Sheet1"];
            // return
            return resultTable;

            }

        List<DataCollection> dataCol = new List<DataCollection>();
        public void PopulateInCollection(string fileName)
            {

            DataTable table = ExcelToDatable(fileName);
            for (int row = 1; row <= table.Rows.Count; row++)
                {

                for (int col = 0; col < table.Columns.Count; col++)
                    {

                    DataCollection dtTable = new DataCollection()
                        {

                        rowNumber = row,
                        colName = table.Columns[col].ColumnName,
                        colValue = table.Rows[row - 1][col].ToString()
                        };
                    dataCol.Add(dtTable);

                    }
                }
            }

        public string ReadData(int rowNumber, string columnName)
            {
            try
                {
                // Retriving data using LINQ to reduce much of iterations
                string data = (from colData in dataCol
                               where colData.colName == columnName && colData.rowNumber == rowNumber
                               select colData.colValue).SingleOrDefault();

                return data.ToString();
                }
            catch (Exception e)
                {
                return null;
                }
            }

        internal class DataCollection
            {
            public int rowNumber { get; internal set; }
            public string colName { get; internal set; }
            public string colValue { get; internal set; }
            }
        }
    }

测试等级:

	[Test]
        public void DataDrivenTest_FromExcel()
            {

			Driver = new ChromeDriver();
			
            ExcelUtil util = new ExcelUtil();
            util.PopulateInCollection(@"C:\dan\AutomationTest\TestData\test.xlsx");

			Driver.FindElement(By.Id("contentPlaceholder_txtClientCode")).SendKeys(util.ReadData(i));
			
			Driver.FindElement(By.XPath("//*[@id='btnLogin']")).Click();
			Driver.FindElement(By.XPath("//*[@id='tabContent0']/table/tbody/tr[2]/td[1]")).Click();
			Driver.FindElement(By.Id("contentPlaceholder_txtcloseButton")).Click();
			
			Driver.Quit

            }

2 回复 | 直到 6 年前

Miguel D'Alessio 6 年前

您可以:

读取EXCEL文件:

class ExcelReader
 {
 public List<TestCaseData> ReadExcelData(string excelFile, string sheetname)
 {
    string cmdText = "SELECT * FROM [" + sheetname+ "$]";

    if (!File.Exists(excelFile))
        throw new Exception(string.Format("File name: {0}", excelFile), new 
    FileNotFoundException());

    string connectionStr = 
     string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", excelFile);
    var ret = new List<TestCaseData>();
    using (var connection = new OleDbConnection(connectionStr))
    {
        connection.Open();
        var command = new OleDbCommand(cmdText, connection);
        var reader = command.ExecuteReader();
        if (reader == null)
            throw new Exception(string.Format("No data return from file, 
        file name:{0}", excelFile));
        while (reader.Read())
        {
            var row = new List<string>();
            var feildCnt = reader.FieldCount;
            for (var i = 0; i < feildCnt; i++)
                row.Add(reader.GetValue(i).ToString());
            ret.Add(new TestCaseData(row.ToArray()));
        }
    }
    return ret;
}

您的测试用例将保持如下状态:

      [TestFixture]
class TC_1
{

  public static IEnumerable<TestCaseData> BudgetData
    {
        get
        {
            List<TestCaseData> testCaseDataList = new ExcelReader().ReadExcelData(//path//document.xlsx",
                "SheetName");
            if (testCaseDataList != null)
                foreach (TestCaseData testCaseData in testCaseDataList)
                    yield return testCaseData;
        }
    }


    [Test]     
    [TestCaseSource(typeof(TC_1), "BudgetData")]
    public void TestCase1(string attribbutte1, string .....)
    {
     ........................................

注意:您需要安装参考“Acces数据库引擎”才能读取Excel文件

注2:默认情况下,第一行为标题,对于每一新行,将执行TC

更改数据时,同一代码被执行5次

G. Dan 6 年前

嗨@Miguel D'Alessio我试过运行你提供的代码。下面是我运行时出错的复制粘贴代码。非常感谢。

ExcelReader类:

namespace Automation
    {
    class ExcelReader
        {
        public List<TestCaseData> ReadExcelData(string excelFile, string sheetname)
            {
            string cmdText = "SELECT * FROM [" + sheetname + "$]";

            if (!File.Exists(excelFile))
                throw new Exception(string.Format("File name: {0}", excelFile), new
            FileNotFoundException());

            string connectionStr =
             string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", excelFile);
            var ret = new List<TestCaseData>();
            using (var connection = new OleDbConnection(connectionStr))
                {
                connection.Open();
                var command = new OleDbCommand(cmdText, connection);
                var reader = command.ExecuteReader();
                if (reader == null)
                    throw new Exception(string.Format("No data return from file, file name:{ 0 }", excelFile));
                while (reader.Read())
                    {
                    var row = new List<string>();
                    var feildCnt = reader.FieldCount;
                    for (var i = 0; i < feildCnt; i++)
                        row.Add(reader.GetValue(i).ToString());
                    ret.Add(new TestCaseData(row.ToArray()));
                    }
                }
            return ret;
            }
        }
    }

测试等级:

namespace Automation
    {
    [TestFixture]
    class Excel_TC_1 
        {
        private readonly string testCaseData;

        public static IEnumerable<TestCaseData> BudgetData
            {
            get
                {
                List<TestCaseData> testCaseDataList = new ExcelReader().ReadExcelData(@"C:\Safety_dan\AutomationTest\TestData\test.xlsx",
                    "Sheet1");
                if (testCaseDataList != null)
                    foreach (TestCaseData testCaseData in testCaseDataList)
                        yield return testCaseData;
                }
            }


        [Test]
        [TestCaseSource(typeof(Excel_TC_1), "BudgetData")]
        public void TestCase1(string attribbutte1)
            {
            // Login

            CcLoginPageObject ccPageLogin = new CcLoginPageObject();
            ClientLoginPageObject clientLoginPage = new ClientLoginPageObject();

                ccPageLogin.Login("username", "password");
          
            // Loading client code:
            Driver.FindElement(By.Id("ClientsSearch")).SendKeys(testCaseData);

            }
        }
        }

错误:

Test Name:	TestCase1
Test FullName:	Automation.Excel_TC_1.TestCase1
Test Source:	C:\Safety_dan\-localRepoVisualStudioC#\Automation\Excel_TC_1.cs : line 32
Test Outcome:	Failed
Test Duration:	0:00:00.005

Result StackTrace:	
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at Automation.ExcelReader.ReadExcelData(String excelFile, String sheetname) in C:\Safety_dan\-localRepoVisualStudioC#\Automation\Excel_TC_1.cs:line 27
   at Automation.Excel_TC_1.<get_BudgetData>d__2.MoveNext() in C:\Safety_dan\-localRepoVisualStudioC#\Automation\Excel_TC_1.cs:line 20
   at NUnit.Framework.TestCaseSourceAttribute.GetTestCasesFor(IMethodInfo method) in C:\src\nunit\nunit\src\NUnitFramework\framework\Attributes\TestCaseSourceAttribute.cs:line 177
Result Message:	System.InvalidOperationException : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.