IT工程師數位筆記本

[TestMethod]
[DataSource("System.Data.Odbc", @"Dsn=Excel Files;dbq=C:\Box Office Results.xlsx;defaultdir=C:;driverid=1046;maxbuffersize=2048;pagetimeout=5", "BoxOfficeResults$", DataAccessMethod.Sequential)]
public void MyTest()
{
    int rowIndex = this.TestContext.DataRow.Table.Rows.IndexOf(this.TestContext.DataRow);
    string s = this.TestContext.DataRow[0].ToString();
}

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </configSections>

  <connectionStrings>
    <add name="MyExcelConn" providerName="System.Data.Odbc" connectionString="Dsn=Excel Files;dbq=C:\Box Office Results.xlsx;defaultdir=C:;driverid=1046;maxbuffersize=2048;pagetimeout=5"/>
  </connectionStrings>
  <microsoft.visualstudio.testtools>
    <dataSources>
      <add name="BoxOfficeResults" connectionString="MyExcelConn" dataTableName="BoxOfficeResults$" dataAccessMethod="Sequential"/>
    </dataSources>
  </microsoft.visualstudio.testtools>
</configuration>

[DataSource("BoxOfficeResults")]

BrowserWindow.CurrentBrowser = this.TestContext.DataRow[0].ToString();

BrowserType
IE
firefox
chrome

public partial class UIMap2
{
    public void LaunchPage()
    {
        this.UIExcelInteractiveViewWindow.LaunchUrl(new Uri("http://www.cnblogs.com/jaxu/p/3635634.html"));
    }

    public void TestTableData()
    {
        HtmlTable targetTable = this.UIExcelInteractiveViewWindow.UIExcelInteractiveViewDocument.UICnblogs_post_bodyPane.UIItemTable;
        HtmlRow rowall = new HtmlRow(targetTable);
        UITestControlCollection rows = rowall.FindMatchingControls();
        Dictionary<int, Dictionary<int, string>> PageTableDataCache = new Dictionary<int, Dictionary<int, string>>();
        int rowCount = rows.Count;

        for (int i = 0; i < rowCount; i++)
        {
            HtmlCell allTD = new HtmlCell(rows[i]);
            UITestControlCollection TDs = allTD.FindMatchingControls();
            Dictionary<int, string> cellsDictionary = new Dictionary<int, string>();

            int tdCount = TDs.Count;
            for (int j = 0; j < tdCount; j++)
            {
                cellsDictionary.Add(j, ((HtmlCell)TDs[j]).InnerText);
            }

            PageTableDataCache.Add(i, cellsDictionary);
        }

        Dictionary<int, Dictionary<int, string>> ExcelDataCache = GetExcelData(ConfigurationManager.AppSettings["ExcelPath"], "BoxOfficeResults");
        // load mapping
        MappingRow[] mappingRows = GetMappingRowCollection();

        string msg = string.Empty;

        for (int i = 0; i < mappingRows.Length; i++)
        {
            Dictionary<int, string> pageRowCellsDictionary = PageTableDataCache[mappingRows[i].PageTableTrIndex];
            Dictionary<int, string> excelRowCellsDictionary = ExcelDataCache[mappingRows[i].ExcelRowNum];
            MappingColumn[] mappingColumns = mappingRows[i].MappingColumn;
            for (int j = 0; j < mappingColumns.Length; j++)
            {
                string excelValue = excelRowCellsDictionary[mappingColumns[j].ExcelColumnNum];
                string pageValue = pageRowCellsDictionary[mappingColumns[j].PageTableTdIndex];

                Assert.AreEqual(excelValue, pageValue, string.Format("Validation failed at row {0} column {1}", mappingRows[i].ExcelRowNum, mappingColumns[j].ExcelColumnNum));
            }
        }
    }

    private Dictionary<int, Dictionary<int, string>> GetExcelData(string filePath, string sheetName)
    {
        Dictionary<int, Dictionary<int, string>> sheetDataDic = new Dictionary<int, Dictionary<int, string>>();
        Application excel = new Application();
        excel.Visible = false;
        excel.UserControl = true;

        try
        {
            Workbook wb = (Workbook)excel.Application.Workbooks.Open(filePath,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value,
                Missing.Value
                );

            foreach (var worksheet in wb.Worksheets)
            {
                Worksheet ws = ((Worksheet)worksheet);
                if (ws.Name.Equals(sheetName))
                {
                    int rowscount = ws.UsedRange.Cells.Rows.Count;
                    int colscount = ws.UsedRange.Cells.Columns.Count;

                    for (int i = 0; i < rowscount; i++)
                    {
                        Dictionary<int, string> cellsDictionary = new Dictionary<int, string>();
                        for (int j = 0; j < colscount; j++)
                        {
                            Range range = ws.Cells.get_Range(ConvertNumberToName(j) + (i + 1));
                            string cellText = ((object)range.Text) == null ? "" : ((object)range.Text).ToString();
                            cellsDictionary.Add(j, cellText);
                        }
                        sheetDataDic.Add(i, cellsDictionary);
                    }
                    break;
                }
            }
        }
        finally
        {
            excel.Application.Workbooks.Close();
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
            GC.Collect();
        }

        return sheetDataDic;
    }

    private string ConvertNumberToName(int index)
    {
        if (index < 0) { throw new Exception("invalid parameter"); }

        List<string> chars = new List<string>();
        do
        {
            if (chars.Count > 0) index--;
            chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
            index = (int)((index - index % 26) / 26);
        } while (index > 0);

        return String.Join(string.Empty, chars.ToArray());
    }

    private MappingRow[] GetMappingRowCollection()
    {
        Mapping mapping = null;
        MappingRow[] mappingRowCollection = null;

        XmlSerializer serializer = new XmlSerializer(typeof(Mapping));

        using (FileStream fs = new FileStream(ConfigurationManager.AppSettings["MappingsPath"], FileMode.Open))
        {
            using (XmlReader reader = XmlReader.Create(fs))
            {
                mapping = (Mapping)serializer.Deserialize(reader);
            }
        }

        if (mapping != null)
        {
            mappingRowCollection = mapping.MappingRow;
        }

        return mappingRowCollection;
    }
}

/// <remarks/>
[System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
[System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
public partial class Mapping
{

    private MappingRow[] mappingRowField;

    /// <remarks/>
    [System.Xml.Serialization.XmlElementAttribute("MappingRow")]
    public MappingRow[] MappingRow
    {
        get
        {
            return this.mappingRowField;
        }
        set
        {
            this.mappingRowField = value;
        }
    }
}

/// <remarks/>
[System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
public partial class MappingRow
{

    private MappingColumn[] mappingColumnField;

    private int excelRowNumField;

    private int pageTableTrIndexField;

    /// <remarks/>
    [System.Xml.Serialization.XmlElementAttribute("MappingColumn")]
    public MappingColumn[] MappingColumn
    {
        get
        {
            return this.mappingColumnField;
        }
        set
        {
            this.mappingColumnField = value;
        }
    }

    /// <remarks/>
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public int ExcelRowNum
    {
        get
        {
            return this.excelRowNumField;
        }
        set
        {
            this.excelRowNumField = value;
        }
    }

    /// <remarks/>
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public int PageTableTrIndex
    {
        get
        {
            return this.pageTableTrIndexField;
        }
        set
        {
            this.pageTableTrIndexField = value;
        }
    }
}

/// <remarks/>
[System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
public partial class MappingColumn
{

    private int excelColumnNumField;

    private int pageTableTdIndexField;

    /// <remarks/>
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public int ExcelColumnNum
    {
        get
        {
            return this.excelColumnNumField;
        }
        set
        {
            this.excelColumnNumField = value;
        }
    }

    /// <remarks/>
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public int PageTableTdIndex
    {
        get
        {
            return this.pageTableTdIndexField;
        }
        set
        {
            this.pageTableTdIndexField = value;
        }
    }
}