记录测试结果到Check list 的Excel文档中

Excel文档是一份check list. 里面有四列

ID, Name, Description, Result。

其中Name和Description主要描述要检查什么内容。

自动化测试用例运行通过的话，根据ID获取到行，然后把结果写道该行的Result列中。如果通过写入"Ok"，并将单元格背景设为Green,如果不通过，写入“NG”，并将单元格背景设为Red。

在Excel中可以很容易就统计出测试用例的通过率，以及覆盖率。

下面是一个精简的例子。

namespace Excels
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Interop.Excel;
    using System.Reflection;
    using System.Drawing;

    public class NExcel : IDisposable
    {
        private Application app;

        private Workbooks workBooks;

        private Workbook workBook;

        private Worksheet worksheet;

        private readonly Object miss = Missing.Value;

        public NExcel(string fileParh)
        {
            app = new Application();
            workBooks = app.Workbooks;
            workBook = workBooks.Open(fileParh, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            worksheet = (Worksheet)workBook.Worksheets[1];
            app.Visible = false;
        }

        public Worksheet GetWorkSheet(int index)
        {
             return (Worksheet)workBook.Worksheets.get_Item(index);
        }

        public string GetValue(int rowNum, int colNum)
        {
            Range range = GetRange(rowNum, colNum);
            return range.Value2;
        }

        public void SetValue(int rowNum, int colNum, string value)
        {
            Range range = GetRange(rowNum, colNum);
            range.Value2 = value;
        }

        public void SetValue(int searchColNum, string searchValue, int colNum, string value, Color color)
        {
            Range range = GetRange(searchColNum, searchValue, colNum);
            range.Value2 = value;
            range.Interior.Color = color;
        }

        private Range GetRange(int rowNum, int colNum)
        {
            return worksheet.Cells[rowNum, colNum];
        }

        private Range GetRange(int searchColNum, string searchValue, int colNum)
        {
            int rowNum = 0;
            int count = worksheet.UsedRange.Rows.Count;
            Range range = null;
            string strActualValue = string.Empty;
            for (int i = 1; i <= count; i++)
            {
                range = worksheet.Cells[i, searchColNum];
                if (Convert.ToString(range.Value2) == searchValue)
                {
                    rowNum = i;
                    break;
                }
            }
            return worksheet.Cells[rowNum, colNum];
        }

        public void Dispose()
        {
            Console.WriteLine("NExcel is disposing....");
            workBook.Close(true, miss, miss);
            worksheet = null;
            workBooks = null;
            workBook = null;
            app.Quit();
            app = null;
        }

    }
}

namespace GoogleTest
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Drawing;
    using Excels;

    public class TestCase : IDisposable
    {
        public List<WriteResultDelegate> WriteResultDelegateList = new List<WriteResultDelegate>();

        public List<IAsyncResult> asyncResultList = new List<IAsyncResult>();

        public string ResultFilePath = @"D:\TestCases.xls";

        public void WriteErrorResult(string testCaseId)
        {
            using (NExcel excel = new NExcel(ResultFilePath))
            {
                excel.SetValue(1, testCaseId, 4, "NG", Color.Red);
            }
        }

        public void WritePassResult(string testCaseId)
        {
            using (NExcel excel = new NExcel(ResultFilePath))
            {
                excel.SetValue(1, testCaseId, 4, "Ok", Color.DarkGreen);
            }
        }

        public void WriteErrorResultDelegate(string testCaseId)
        {
            WriteResultDelegate writeResultDelegate = WriteErrorResult;
            WriteResultDelegateList.Add(writeResultDelegate);
            IAsyncResult asyncResult = writeResultDelegate.BeginInvoke(testCaseId, null, null);
            asyncResultList.Add(asyncResult);
        }

        public void WritePassResultDelegate(string testCaseId)
        {
            WriteResultDelegate writeResultDelegate = WritePassResult;
            WriteResultDelegateList.Add(writeResultDelegate);
            IAsyncResult asyncResult = writeResultDelegate.BeginInvoke(testCaseId, null, null);
            asyncResultList.Add(asyncResult);
        }

        public delegate void WriteResultDelegate(string id);

        public virtual void Dispose()
        {
            Console.WriteLine(this.GetType().ToString() + " is disposing...");
            for (int i = 0; i < WriteResultDelegateList.Count; i++)
            {
                WriteResultDelegateList[i].EndInvoke(asyncResultList[i]);
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using WatiN.Core;
using System.Threading;
using Assert = GoogleTest.Assert;

namespace GoogleTest
{
    [TestFixture]
    public class TestCase001 : TestCase
    {
        [Test]
        [STAThread]
        public void OpenGoogle()
        {
            IE ie = new IE(true);
            WritePassResultDelegate("1");
            ie.GoTo("http://www.google.com");
            WritePassResultDelegate("2");
            Assert.IsTrue(ie.TextField(Find.ByName("q")).Exists);
            WritePassResultDelegate("3");
            Assert.IsTrue(ie.Button(Find.ByName("btnI")).Exists);
            ie.TextField(Find.ByName("q")).Value = "WatiN";
            ie.Button(Find.ByName("btnG")).Click();
        }
    }
}